Editing Composite key

{{Short description|Candidate key with two or more attributes in a database}}
{{more sources needed|date=February 2024}}
In [[Data modeling|database design]], a '''composite key''' is a [[candidate key]] that consists of two or more attributes,<ref>{{cite book |last1=Connolly |first1=Thomas M. |last2=Begg |first2=Carolyn E. |title=Database systems: a practical approach to design, implementation, and management |date=2015 |publisher=Pearson |location=Boston Columbus Indianapolis |isbn=978-1-292-06118-4 |page=416 |edition=6., global |chapter=12.3.4 Keys}}</ref><ref>{{cite book |last1=Elmasri |first1=Ramez |last2=Navathe |first2=Sham |title=Fundamentals of database systems |date=2017 |publisher=Pearson |location=Boston Columbus Indianapolis New York San Francisco Hoboken Amsterdam Cape Town Dubai London Madrid Milan Munich Paris Montreal Toronto Delhi Mexico City São Paulo Sydney Hong Kong Seoul Singapore Taipei Tokyo |isbn=978-1-292-09761-9 |page=661 |edition=Seventh, global |chapter=17.4 Indexes on Multiple Keys}}</ref><ref>{{cite book |last1=Coronel |first1=Carlos |last2=Morris |first2=Steven |title=Database systems: design, implementation, and management |date=2015 |publisher=Cengage Learning |isbn=978-1-305-62748-2 |page=770 |edition=12e |chapter=Glossary}}</ref> (table columns) that together uniquely identify an entity occurrence (table row).  

A '''compound key''' is a composite key for which each attribute that makes up the key is a [[foreign key|foreign]] key in its own right.<ref>{{Citation |last=Duncan |first=Joyce |title=Step 340 — Enhance Required Logical Data Model |date=1995 |work=SSADM in Practice: A Version 4 Text |pages=61–70 |editor-last=Duncan |editor-first=Joyce |url=https://link.springer.com/chapter/10.1007/978-1-349-10341-6_6 |access-date=2024-10-25 |place=London |publisher=Macmillan Education UK |language=en |doi=10.1007/978-1-349-10341-6_6 |isbn=978-1-349-10341-6 |last2=Rackley |first2=Lesley |last3=Walker |first3=Alexandria |editor2-last=Rackley |editor2-first=Lesley |editor3-last=Walker |editor3-first=Alexandria|url-access=subscription }}</ref><ref>{{Citation |last=Sikora |first=Z. M. |title=Implementing a Design |date=1997 |work=Oracle Database Principles |pages=74–84 |editor-last=Sikora |editor-first=Z. M. |url=https://link.springer.com/chapter/10.1007/978-1-349-14693-2_7 |access-date=2024-10-25 |place=London |publisher=Macmillan Education UK |language=en |doi=10.1007/978-1-349-14693-2_7 |isbn=978-1-349-14693-2|url-access=subscription }}</ref>

== Advantages ==
Composite keys have advantages similar to that of a [[natural key]] as it is often composed of multiple natural key attributes.
=== Storage ===
Composite keys use less disk space as compared to defining a [[surrogate key]] column, this is because the composite key already exists as attributes in the table and does not need to be defined in the table just for the purpose of unique identification. This simplifies the table and also saves space.

=== Easier to implement and use ===
Composite keys are easy to implement in a [[database schema]] as their component parts are already named items in the database. When they are also natural keys, they are often intuitive for real world scenarios. They are often used when a non-composite key does not always uniquely identify a record. For example, a personal name may often, but not always, be unique in a given database, and some other field such as date of birth may be added to make uniqueness much more probable.

== Disadvantages ==
=== Requirement changes ===
The business requirements and rules can change which can change the format of certain real world entities. Composite keys are formed of multiple natural keys which are related to the real world and with the change of their format in the real world, their format in the database will also be changed. This is inconvenient as the number of attributes of composite key will change and all the foreign keys would need to be updated.
=== Complexity and storage ===
A composite key consists of multiple attributes and the composite key will be referenced in multiple tables as the foreign key, this uses a lot of disk space as multiple columns are being stored as the foreign key instead of just possibly one. This makes the schema complex and the queries become more CPU expensive as for every join the DBMS will need to compare three attributes instead of just possibly one in case of a single natural key.

== Example ==
An example is an entity that represents the modules each student is attending at University. The entity has a {{lang|mis|studentID}} and a {{lang|mis|moduleCode}} as its [[primary key]]. Each of the attributes that makes up the primary key is a simple key because each represents a unique reference when identifying a student in one instance and a module in the other, so this key is a compound key.

In contrast, using the same example, imagine we identified a student by their {{lang|mis|firstName}} + {{lang|mis|lastName}} (assuming that people must have different names). In a table representing students our primary key would now be {{lang|mis|firstName}} + {{lang|mis|lastName}}. Because students can have the same {{lang|mis|firstName}} or the same {{lang|mis|lastName}} these attributes are not simple keys.  The primary key {{lang|mis|firstName}} + {{lang|mis|lastName}} for students is a composite key.

== See also ==
* [[Relational database]]
* [[Alternate key]]
* [[Candidate key]]
* [[Foreign key]]
* [[Primary key]]
* [[Unique key]]
* [[Superkey]]

==References==
{{reflist}}

== External links ==
*[http://esw.w3.org/topic/CIFP Composite Inverse Functional Properties]: for an equivalent notion in the Semantic Web
*[http://rdbms.opengrass.net Relation Database terms of reference, Keys]: An overview of the different types of keys in an RDBMS
*[https://www.databasestar.com/database-keys/ Different types of keys in a database]: An overview of all the types of keys that are used in an RDBMS

[[Category:Data modeling]]

[[ru:Составной ключ]]