SQL Primary Key Duplicate Values

Question

I have a table with 2 primary key columns : ID and StudentID.

ID 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 StudentID, 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?

Answer 1

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.