Menu
I have this case :
UserSettings is not really a junction table since it only has one FK, which is gonna be unique, one UserSettings for one User. Should UserSettings have UserId marked as Primary Key even if UserId is a unique FK or is it unnecessary ?
327
The IDENTITY column should not necessarily be a PRIMARY KEY, but most often it's used to fill the surrogate PRIMARY KEY s It may or may not be useful in any particular case. Therefore, the answer to your question: The question: should every table in a database have an IDENTITY field that's used as the PK? No.
A table can have only one primary key. The primary key column has a unique value and doesn’t store repeating values. A Primary key can never take NULL values. For example, in the case of a student when identification needs to be done in the class, the roll number of the student plays the role of Primary key.
Every table should have a key, to eliminate the data corruption of inadvertently entering multiple rows that represent the same 'entity'. If the only key a table has is a meaningless surrogate key, then this function is effectively missing.
When you specify a primary key constraint for a table, the Database Engine enforces data uniqueness by automatically creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. If a primary key constraint is defined on more than one column,...
If you want to ensure this "which is gonna be unique" requirement then you'll need to define UserID either as UNIQUE or as Primary Key constraint.
159
UserSettings should ideally not exist. Logically, all of this is one table.
If you wish to keep a separate table (which might be useful for performance or architecture) you should probably use the same primary key. In other words, UserSettings should use the FK as the PK. This is advantageous for performance, storage space and simplicity.
2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
