The following pair of concepts are related in some way and different in another. Explain how the concepts are similar and how they are different. Composite Primary Key/Surrogate Primary Key
The following pair of concepts are related in some way and different in another. Explain how the concepts are similar and how they are different.
Composite Primary Key/Surrogate Primary Key
When combinations of two or more columns that uniquely identify rows in a table called Composite key and uniquely identify each record is called surrogate key. When a primary key is generated at runtime it’s called a surrogate key. Both are similar when two or more columns each record uniquely identify that time in table both condition satisfied then we say that both key s are using in single table.
How they are different
If a database has attributes A1, A2, A3...An and A1, A2 & A3 can form composite key together, is it better to use a surrogate key instead of a composite key
Using a surrogate key will improve the Insertion execution speed of records (this supports surrogate over composite key) But the SELECT, UPDATE and DELETE queries based on the attributes A1, A2 & A3 will be tremendously slowed down if we use surrogate key.
A surrogate key is one which is not naturally unique to the data but is added or imposed onto the data for a good reason. Examples can include IDENTITY columns in SQL Server - known as auto numbers sometimes in other products.
You are storing information about people, but you can't be sure that they can be uniquely identified by a combination of their name, date of birth etc.. If you don't have some external information about them that ascribes uniqueness (e.g. Social Security Number), you can use a surrogate key to uniquely identify the rows
Step by step
Solved in 2 steps