t_Consumer t_Consumer - 6 months ago 11x
SQL Question

SQL Primary Key Duplicate Values

I have a table with 2 primary key columns :


column is set to
isIdentity = Yes
with auto increment.

I've tested it multiple times before, but for some reason this time, when I insert a duplicate value on
, it does not throw the error but instead added it on to the database. 2 of the same values are displayed when I show the table data.

What can be the problem here?


You have a compound primary key on ID and StudentID. That means you the combination of ID and StudentID together must be unique. Since ID is an identity column that combination of ID and StudentID will always be unique (because ID is already unique on its own).

You can change the primary key to be on ID only. Then you can add a unique index on StudentID. For example:

create unique index idx_studentID on yourTable(StudentID)

That will insure that the StudentID column, in fact, contains only unique values.

It seems like you may not actually need ID column, but that's a little wider discussion than your original question.