PK and Unique Constraint

Question

Hello,

I have a table to hold Users Claims as follows:

    CREATE TABLE [dbo].[UserClaims] (
      [UserAccountID] [int] NOT NULL,
      [Type] [nvarchar](150) NOT NULL,
      [Value] [nvarchar](150) NOT NULL,
      CONSTRAINT [PK_dbo.UserClaims] PRIMARY KEY ([UserAccountID], [Type], [Value])
    )

And I have a second option for the same table:

    CREATE TABLE [dbo].[UserClaims] (
      [UserAccountID] [int] NOT NULL,
        CONSTRAINT PK_UserClaims_UserAccountId PRIMARY KEY CLUSTERED (UserAccountId),
      [Type] [nvarchar](150) NOT NULL,
      [Value] [nvarchar](150) NOT NULL,
        CONSTRAINT UQ_UserClaims_Value_Type_UserAccountID unique (Value, [Type], UserAccountID)
    )

On the first table I have a PK from 3 columns.

On the second table I have a PK from one column and a unique restriction on the 3 columns.

When should I use one or the other?

Thank You,
Miguel

Answer 1

Well, the only difference between a primary key and a unique constraint is that the latter can contain (one!) null value. Of course, if you need to have this null value you may have other design "issues".

IMV this is really a design / readability question. If UserAccountID is adequate by itself to identify a "UserClaim", then it should be the primary key.

If you need to enforce the one claim type and value per UserAccountID, then a unique constraint is an appropriate way to do this.

---

(Twitter | Blog)

Answer 2

The first one seems to be better option and even valid one.

Because the Userclaims will have multiple claims done by the same useraccountid. In such a case it cannot qualify for Primary Key.

Also, the UNIQUE constraint defined in the 2nd table design says there can be multiple entries of useraccountid (from the columns list defined in the UNIQUE constraint). This also favors Design1. 

Hope this provides some points. 

--few more points

Forgot to mention one more point, the table is having only three columns and one option is to choose combination of all a Primary Key.

Instead a Surrogate key can be added and marked as primary key. 

More than one claim can be done by same useraccountid for a same type with same value, this could be a rare scenario , why take chances.

---

Thanks
Sarat

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

hi Miguel.

as I can see that table it's purpose is to have parameters for a user

lets start with the first table.

there is uniqueness is three fiels but I guess that

user id ,type and the value

userid=1 ,type='phone',value='zzzz'

userid=1 ,type='phone',value='xxx'

if you want to find all the the phones for that user the constraint is not good because it includes the value too.

as for the second table.....

primery key is unique!

you can add this row

userid=1 ,type='phone',value='zzzz'

but you'll get an error when tring to add the second row

userid=1 ,type='phone',value='xxx'

there is already userid = 1 in the table.

 

Answer 4

To me, these are two different cases.

1. First design enforces the primary constraint for the combination of ID,Type and value whereas the second enforces only on ID.

2. First will create only clustered whereas second will create clustered and non-clustered indexes

3. First design, the queries that uses Value and type as the predicate will not be supported where as the value, type as predicate will be supported by the non clustered created by unique constrint in second design.

4. Second design, there might be an improvement in performance in high OLTP if the load is spiltted across two indexes with the all there columns are as predicate. (Note that,the order is different, hence it depends.)

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 5

I got a little bit confused with the different answers. I have the table:

    CREATE TABLE [dbo].[UsersAccounts] (
      [UserAccountID] [int] IDENTITY NOT NULL,
        CONSTRAINT PK_UsersAccounts_UserAccountId PRIMARY KEY CLUSTERED (UserAccountId)
    )

When I get a User I will always need to get all its Claims from UserClaims.

The UserAccountID is enough to identify all User Claims ... And I will not have any other queries.

I need to be able to add the following rows to UserClaims:

  UserAccountID = 2, Type = Role, Value = Editor

  UserAccountID = 1, Type = Role, Value = Editor

  UserAccountID = 1, Type = Role, Value = Member

  UserAccountID = 1, Type = Name, Value = John

But I can't add  the following Claims:

  UserAccountID = 1, Type = Role, Value = Editor

  UserAccountID = 1, Type = Role, Value = Editor

I cannot have two rows completly equal. All the other combinations are possible.

This is why I am considering the following table:

   CREATE TABLE [dbo].[UserClaims] (
      [UserAccountID] [int] NOT NULL,
        CONSTRAINT PK_UserClaims_UserAccountId PRIMARY KEY CLUSTERED (UserAccountId),
      [Type] [nvarchar](150) NOT NULL,
      [Value] [nvarchar](150) NOT NULL,
        CONSTRAINT UQ_UserClaims_Value_Type_UserAccountID unique (Value, [Type], UserAccountID)
    )

This way I will have a PK and a Constraint for the 3 columns as a group.

Does this make sense? Sorry, but I got confused about the different answers.

Thank You,

Miguel

Answer 6

I got a little bit confused with the different answers. I have the table:

    CREATE TABLE [dbo].[UsersAccounts] (
      [UserAccountID] [int] IDENTITY NOT NULL,
        CONSTRAINT PK_UsersAccounts_UserAccountId PRIMARY KEY CLUSTERED (UserAccountId)
    )

When I get a User I will always need to get all its Claims from UserClaims.

The UserAccountID is enough to identify all User Claims ... And I will not have any other queries.

I need to be able to add the following rows to UserClaims:

  UserAccountID = 2, Type = Role, Value = Editor

  UserAccountID = 1, Type = Role, Value = Editor

  UserAccountID = 1, Type = Role, Value = Member

  UserAccountID = 1, Type = Name, Value = John

But I can't add  the following Claims:

  UserAccountID = 1, Type = Role, Value = Editor

  UserAccountID = 1, Type = Role, Value = Editor

I cannot have two rows completly equal. All the other combinations are possible.

This is why I am considering the following table:

   CREATE TABLE [dbo].[UserClaims] (
      [UserAccountID] [int] NOT NULL,
        CONSTRAINT PK_UserClaims_UserAccountId PRIMARY KEY CLUSTERED (UserAccountId),
      [Type] [nvarchar](150) NOT NULL,
      [Value] [nvarchar](150) NOT NULL,
        CONSTRAINT UQ_UserClaims_Value_Type_UserAccountID unique (Value, [Type], UserAccountID)
    )

This way I will have a PK and a Constraint for the 3 columns as a group.

Does this make sense? Sorry, but I got confused about the different answers.

Thank You,

Miguel

Now with the sample data , it favours Design1, because the primary key column cannot accept duplicate values( and NULL).

---

Thanks
Sarat

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 7

From your sample data, the UserAccountID isn't enough by itself to identify a row in this table, since you have more than one row with the same UserAccountID.

So, the choices are between creating a three column primary key, or adding a new column (called something like UserClaimID), making the new column the primary key, and creating a unique constraint on UserAccountID, Type, and Value.

Which of these you choose depends on how often you will be joining on this key, and on how big the tables are.

---

(Twitter | Blog)

Answer 8

Your sample code

   CREATE TABLE [dbo].[UserClaims] (
      [UserAccountID] [int] NOT NULL,
        CONSTRAINT PK_UserClaims_UserAccountId PRIMARY KEY CLUSTERED (UserAccountId),
      [Type] [nvarchar](150) NOT NULL,
      [Value] [nvarchar](150) NOT NULL,
        CONSTRAINT UQ_UserClaims_Value_Type_UserAccountID unique (Value, [Type], UserAccountID)

Will not work for you because that would say UserClaims cannot have more than one row with the same UserAccountID.  But you want to be able to have more than one row with the same UserAccountID.  On the other hand, I am guessing you do not want to allow a UserAccountID value unless that value for UserAccountID also exist in UsersAccounts.  So the table definition would be

   CREATE TABLE [dbo].[UserClaims] (
      [UserAccountID] [int] NOT NULL,
        CONSTRAINT FK_UserClaims_UserAccountId FOREIGN KEY(UserAccountID) References UsersAccounts,
      [Type] [nvarchar](150) NOT NULL,
      [Value] [nvarchar](150) NOT NULL,
        CONSTRAINT PK_UserClaims_Value_Type_UserAccountID unique (Value, [Type], UserAccountID)

Tom