Composite key, or composite primary key, refers to cases where more than one column is used to specify the primary key of a table. In such cases, all foreign keys will also need to include all the columns in the composite key. Note that the columns that make up a composite key can be of different data types.<\/p>\n
Below is the SQL syntax for specifying a composite key:<\/p>\n
CREATE TABLE TABLE_NAME
\n(COLUMN_1 DATA_TYPE_1,
\nCOLUMN_2 DATA_TYPE_2,
\n…
\nPRIMARY KEY (COLUMN_1, COLUMN_2, …));<\/p>\n
Some database-specific examples are shown below. In all cases the composite key created consists of COL1 and COL2.<\/p>\n
CREATE TABLE SAMPLE_TABLE
\n(COL1 integer,
\nCOL2 varchar(30),
\nCOL3 varchar(50),
\nPRIMARY KEY (COL1, COL2));<\/p>\n
CREATE TABLE SAMPLE_TABLE
\n(COL1 integer,
\nCOL2 varchar(30),
\nCOL3 varchar(50),
\nPRIMARY KEY (COL1, COL2));<\/p>\n
CREATE TABLE SAMPLE_TABLE
\n(COL1 integer,
\nCOL2 nvarchar(30),
\nCOL3 nvarchar(50),
\nPRIMARY KEY (COL1, COL2));<\/p>\n
An example is when we want to record the history of student class registration and grade history. For this, we want to record which student took what class during what quarter, along with the date of registration, date of drop (if the class was dropped), and the final grade. Our table will look like the following:<\/p>\n
Table Class_History<\/b><\/i><\/p>\n In this table, the first three fields, Quarter_ID, Student_ID, Course_ID, together form a composite key.<\/b> Each of them individually cannot uniquely identify each record, but together the combination of all three does uniquely identify each record.<\/p>\n","protected":false},"excerpt":{"rendered":" Discusses the concept of composite key in SQL, and shows examples of creating a composite key in MySQL, Oracle, and SQL Server.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[247],"tags":[297,299,298],"_links":{"self":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/616"}],"collection":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/comments?post=616"}],"version-history":[{"count":8,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/616\/revisions"}],"predecessor-version":[{"id":1136,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/posts\/616\/revisions\/1136"}],"wp:attachment":[{"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/media?parent=616"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/categories?post=616"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.1keydata.com\/blog\/wp-json\/wp\/v2\/tags?post=616"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
\n Quarter_ID<\/td>\n<\/tr>\n \n Student_ID<\/td>\n<\/tr>\n \n Course_ID<\/td>\n<\/tr>\n \n Date_Registered<\/font><\/tr>\n \n Date_Dropped<\/td>\n<\/tr>\n \n Final_Grade<\/td>\n<\/tr>\n<\/table>\n