Compound key
Encyclopedia
In database design
, a compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. Each attribute that makes up the compound key is a simple key in its own right.
This is often confused with a composite key whereby even though this is also a key that consists of 2 or more attributes that uniquely identify an entity occurrence, at least one attribute that makes up the composite key is not a simple key in its own right.
An example might be an entity that represents the modules each student is attending at University. The entity has a studentId and a moduleCode as its primary key. Each of the attributes that make up the primary key are simple keys because each represents a unique reference when identifying a student in one instance and a module in the other.
In contrast, using the same example, imagine we identified a student by their firstName + lastName. In our table representing students on modules our primary key would now be firstName + lastName + moduleCode. Because firstName + lastName represent a unique reference to a student, it is not a simple key, it is a combination of attributes used to uniquely identify a student. Therefore the primary key for this entity is a composite key.
No restriction is applied to the attributes regarding their (initial) ownership within the data model
. This means that any one, none, or all, of the multiple attributes within the compound key can be foreign key
s. Indeed, a foreign key may itself be a compound key.
Compound keys almost always originate from attributive or associative entities (tables) within the model, but this is not an absolute.
Data modeling
Data modeling in software engineering is the process of creating a data model for an information system by applying formal data modeling techniques.- Overview :...
, a compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. Each attribute that makes up the compound key is a simple key in its own right.
This is often confused with a composite key whereby even though this is also a key that consists of 2 or more attributes that uniquely identify an entity occurrence, at least one attribute that makes up the composite key is not a simple key in its own right.
An example might be an entity that represents the modules each student is attending at University. The entity has a studentId and a moduleCode as its primary key. Each of the attributes that make up the primary key are simple keys because each represents a unique reference when identifying a student in one instance and a module in the other.
In contrast, using the same example, imagine we identified a student by their firstName + lastName. In our table representing students on modules our primary key would now be firstName + lastName + moduleCode. Because firstName + lastName represent a unique reference to a student, it is not a simple key, it is a combination of attributes used to uniquely identify a student. Therefore the primary key for this entity is a composite key.
No restriction is applied to the attributes regarding their (initial) ownership within the data model
Data modeling
Data modeling in software engineering is the process of creating a data model for an information system by applying formal data modeling techniques.- Overview :...
. This means that any one, none, or all, of the multiple attributes within the compound key can be foreign key
Foreign key
In the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
s. Indeed, a foreign key may itself be a compound key.
Compound keys almost always originate from attributive or associative entities (tables) within the model, but this is not an absolute.
See also
- relational databaseRelational databaseA relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
- candidate keyCandidate keyIn the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that# the relation does not have two distinct tuples In the relational model of databases, a candidate key of a relation is a minimal superkey for that...
- primary key
- alternate key
- foreign keyForeign keyIn the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
- unique keyUnique keyIn relational database design, a unique key can uniquely identify each row in a table, and is closely related to the Superkey concept. A unique key comprises a single column or a set of columns. No two distinct rows in a table can have the same value in those columns if NULL values are not used...
- surrogate keySurrogate keyA surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.- Definition :There are at least two definitions of a surrogate:...
- SuperkeySuperkeyA superkey is defined in the relational model of database organization as a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples that have the same values for the attributes in this set...
External links
- Composite Inverse Functional Properties: for an equivalent notion in the Semantic Web
- Relation Database terms of reference, Keys: An overview of the different types of keys in an RDBMS