0

Assume a table that may look like this:

userId INT (foreign key to a users table)
profileId INT (foreign key to a profiles table)
value INT

Say that in this table preferences for users are saved. The preference should be loaded according to the current user and the profile that the current user has selected. That means that the combination of userId and profileId is unique and can be used as a composite primary key.

But then I want to add the ability to also save a default value that should be used if no value for a specific profileId is save in the database. My first idea would be to set the profileId column to nullable and say that the row that has null as profileId contains the default value. But then I can't use a composite primary key that involves this table, because nullable columns can't be part of a primary key.

So what's the "best" way to work around this? Just drop the primary key completely and go without primary key? Generate an identity column as primary key that I never need? Create a dummy profile to link to in the profile table? Create a separate table for default values (which is the only option that guarantees that no userId has multiple default values??)?

Update: I thought about Dmitry's answer but after all it has the drawback that I can't even create a unique constraint on the two columns userId and profileId (MySQL will allow duplicate values if profileId is null and DB2 will refuse to even create a unique constraint on a nullable column). So with Dmitry's solution I will have to live without this consistency check of the DB. Is that acceptable? Or is that not acceptable (after all consistency checks are a major feature of relational DBs). What is your reasoning?

Community
  • 1
  • 1
yankee
  • 38,872
  • 15
  • 103
  • 162
  • 1
    Since I've never liked composite PK's (they're just clunky and clumsy, and referencing their table is even more clumsy) - I would definitely add a surrogate `INT IDENTITY` column as your PK and just be done with it. – marc_s Aug 07 '11 at 16:13
  • 1
    @yankee, If you create a default user profile, as described in my answer, then you won't have a null profile FK value and you can go ahead and create the composite primary key. While I wholeheartedly endorse surrogate keys for general use, when you are talking about an intersection table (like yours) a surrogate key is considered overkill by many people. It certainly doesn't buy you anything unless you plan on having other children pointing at the intersections. – Joel Brown Jan 25 '12 at 00:27

4 Answers4

2
  1. Create ID autoincrement field for your primary key.

AND

  1. Create unique index for (userId, profileId) pair. If necessary create dummy profile instead of null.
  • Could you explain a little more? Why the autoincrement key? Does it help the db engine somehow? – yankee Aug 07 '11 at 13:17
  • Shortly: Having the key independent of all other columns insulates the database relationships from changes in data values or database design (making the database more agile) and guarantees uniqueness. Link: http://en.wikipedia.org/wiki/Surrogate_key –  Aug 07 '11 at 13:21
  • actually I can't create the unique index that you mentioned. Won't work. See update in my question above. – yankee Jan 18 '12 at 10:20
2

Dmitry's answer is a good one, but since your case involves what is essentially an intersection table, there is another good way to solve this. For your situation I also like the idea of creating a default user profile that you can use in your code to establish default settings. This is good because it keeps your data model clean without introducing extra candidate keys. You would need to be clear in this dummy/default profile that this is what it is. You can give it a clear name like "Default User" and make sure that nobody but the administrator has access to the user credentials.

One other advantage of this solution is that you can sign on as the default user and use your system's GUI to modify the defaults rather than having to fiddle with the data through DB access tools. Depending on the policies in your shop, direct access to the data tables by programmers may be hard or impossible. Using the tested/approved GUIs for modifying defaults removes a lot of red tape and prevents some kinds of accidental damage to the data.

Bottom Line: Primary keys are important. In a transactional system every table should have a at least one unique index one of which should be the primary key. You can always enforce this by adding a surrogate (auto increment) key to every table. Even if you do, you still generally want a natural unique index whenever possible. This is how you will generally find what you're looking for in a table.

Creating a Default User entry in your user table isn't a cheat or a hack, it's using your table structure the way it's meant to be used and it allows you to put a usable unique contraint on the combination of user ID and profile ID, regardless of whether you invent an additional, arbitrary unique constraint with a surrogate key.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64
1

This is the normal behaviour of UNIQUE constrain on a NULL column. It allows one row of data with NULL values. However, that is not the behaviour we want for this column. We want the column to accept unique values and also accept multiple NULL values.

This can be achieved using a computed column and adding a contraint to the computed column instead default null value.

Refer below article will help you more in this matter:

UNIQUE Column with multiple NULL values

  • The computed column solution proposed in the article relies on the fact that there is some other candidate column that can be used in place of a null. That situation doesn't apply to OP's scenario. What OP needs is a default user record in order to give his default profile values a place to be persisted that is consistent with all of the user-specific profile information. – Joel Brown Jan 25 '12 at 16:37
0

I always always always use a primary auto_increment key on a table, even if its redundant; it just gives me a fantastically simple way to identify a record I want to access later or refer to elsewhere. I know it doesn't directly answer your question, but it does make the primary key situation simpler.

create table UserProfile ( int UserProfileID auto_increment primary key etc., 
    UserID int not null, ProfileID int );

Then create a secondary index UserProfileIDX(UserID, ProfileID) that's unique, but not the primary key.

mikebabcock
  • 791
  • 1
  • 7
  • 20