Why use multiple columns as primary keys composite primary key

Question

This example is taken from w3schools    CREATE TABLE Persons       P Id int NOT NULL      LastName varchar 255  NOT NULL      FirstName varchar 255       Address varchar 255       City varchar 255       CONSTRAINT pk PersonID PRIMARY KEY  P Id LastName      My understanding is that both columns together  P Id and LastName  represent a primary key for the table Persons  Is this correct    Why would someone want to use multiple columns as primary keys instead of a single column   How many columns can be used together as a primary key in a given table

User · Answer

Multiple columns in a key are going to  in general  perform more poorly than a surrogate key  I prefer to have a surrogate key and then a unique index on a multicolumn key  That way you can have better performance and the uniqueness needed is maintained  And even better  when one of the values in that key changes  you don t also have to update a million child entries in 215 child tables

User · Answer

The W3Schools example isn t saying when you should use compound primary keys  and is only giving example syntax using the same example table as for other keys   Their choice of example is perhaps  misleading you by combining a meaningless key  P Id  and a natural key  LastName   This odd choice of primary key says that the following rows are valid according to the schema and are necessary to uniquely identify a student  Intuitively this doesn t make sense   1234     Jobs 1234     Gates   Further Reading  The great primary-key debate or just Google meaningless primary keys or even peruse this SO question  FWIW - My 2 cents is to avoid multi-column primary keys and use a single generated id field  surrogate key  as the primary key and add additional  unique  constraints where necessary

User · Answer

Using a primary key on multiple tables comes in handy when you re using an intermediate table in a relational database   I ll use a database I once made for an example and specifically three tables within that table   I cre auml ted a database for a webcomic some years ago   One table was called  comics  mdash a listing of all comics  their titles  image file name  etc   The primary key was  comicnum    The second table was  characters  mdash their names and a brief description  The primary key was on  charname    Since each comic mdash with some exceptions mdash had multiple characters and each character appeared within multiple comics  it was impractical to put a column in either  characters  or  comics  to reflect that   Instead  I cre auml ted a third table was called  comicchars   and that was a listing of which characters appeared in which comics   Since this table essentially joined the two tables  it needed but two columns   charname and comicnum  and the primary key was on both

User · Answer

Your second question     How many columns can be used together as a primary key in a given table    is implementation specific  it s defined in the actual DBMS being used  1   2   3  You have to inspect the technical specification of the database system you use  Some are very detailed  some are not  Searching the web about such limitations can be hard because the terminology varies  The term composite primary key should be mandatory     If you cannot find explicit information  try creating a test database to ensure you can expect stable  and specific  handling of the limit violations  which are to be expected   Be careful to get the right information about this  sometimes the limits are accumulated  and you ll see different results with different database layouts       1  sql - Composite primary key limit  - Stack Overflow  2  SQL Server - Max columns per primary key  3  How many maximum number of columns can be part of Primary Key in a table in Oracle 9i and 10g

User · Answer

We create composite primary keys to guarantee the uniqueness column values which compose a single record  It is a constraint which helps prevent insertion of data which should not be duplicated    i e   If all student Ids and birth certificate numbers are uniquely assigned to a single person  Then it would be a good idea to make the primary key for a person a composition of student id and birth certificate number because it would prevent you from accidentally inserting two people who have different student ids and the same birth certificate

User · Answer

You use a compound key  a key with more than one attribute  whenever you want to ensure the uniqueness of a combination of several attributes  A single attribute key would not achieve the same thing

User · Answer

Another example of compound primary keys are the usage of Association tables   Suppose you have a person table that contains a set of people and a group table that contains a set of groups   Now you want to create a many to many relationship on person and group  Meaning each person can belong to many groups  Here is what the table structure would look like using a compound primary key   Create Table Person  PersonID int Not Null  FirstName varchar 50   LastName varchar 50   Constraint PK Person PRIMARY KEY  PersonID    Create Table Group   GroupId int Not Null  GroupName varchar 50   Constraint PK Group PRIMARY KEY  GroupId    Create Table GroupMember   GroupId int Not Null  PersonId int Not Null  CONSTRAINT FK GroupMember Group FOREIGN KEY  GroupId  References Group GroupId   CONSTRAINT FK GroupMember Person FOREIGN KEY  PersonId  References Person PersonId   CONSTRAINT PK GroupMember PRIMARY KEY  GroupId  PersonID

User · Answer

Yes  they both form the primary key  Especially in tables where you don t have a surrogate key  it may be necessary to specify multiple attributes as the unique identifier for each record  bad example  a table with both a first name and last name might require the combination of them to be unique

User · Answer

Your understanding is correct   You would do this in many cases   One example is in a relationship like OrderHeader and OrderDetail   The PK in OrderHeader might be OrderNumber  The PK in OrderDetail might be OrderNumber AND LineNumber   If it was either of those two  it would not be unique  but the combination of the two is guaranteed unique   The alternative is to use a generated  non-intelligent  primary key  for example in this case OrderDetailId   But then you would not always see the relationship as easily   Some folks prefer one way  some prefer the other way

[database-design] Why use multiple columns as primary keys (composite primary key)

Examples related to database-design

Examples related to relational-database

Examples related to primary-key

Examples related to ddl

Examples related to database-table