How Can I Define A Composite Primary Key In SQL?
Summary: You will learn in this article, how can i define a composite primary key In SQL? let’s also understand example of Composite Primary Key In SQL and their importance.
Primary Key:
A column that is used to determine the records uniquely is referred to as Primary Key.
1. Primary key will not enable Null values.
2. If a Primary key is created, then it will automatically create the clustered index on the table. Furthermore, in turn, the data will be sorted based on this column.
3. Only one(1) Primary key can be created for a table.
Composite Primary Key:
1. Here, The multiple columns can participate in the Primary Key that is referred to as a composite primary key in SQL.
Example of Composite Primary Key In SQL
Let’s, first of all, create the table “STUDENTS_DETAILS_TABLE” with Composite Primary key on the column STUDENT_ID and STUDENT_NAME
SQL Code:
|
1 2 3 4 5 |
CREATE TABLE STUDENTS_DETAILS_TABLE ( STUDENT_ID INT, STUDENT_NAME VARCHAR(100), STUDENT_BIRTHDATE DATETIME, PRIMARY KEY(STUDENT_ID,STUDENT_NAME)) |
Furthermore, To get the data concerning your Primary key on the columns. you’ll use the below system view.
|
1 |
SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE |
How To Achieve Your Designer Window or SQL Table Design?
Therefore, I have seen a query in popular forums that How to achieve your designer window. let’s see and understand in better way
First of all, Right-click on the table & you’ll get the columns. after that, Press Control or Shift key and select the columns -> Right-click and you can set the primary key.
What Is SQL Composite Key?
A composite key is a composition of two or more columns in a table that can be used to uniquely recognize each row in the table when the columns are combined uniqueness is guaranteed, but when it is taken individually it doesn’t guarantee uniqueness.
Sometimes more than one attribute is required to uniquely determine an entity. Therefore, a primary key that’s made by the combination of more than one attribute is called a composite key.
In alternative words, we can say that:
Therefore, a composite key in SQL is a key that is the combination of more than one field or column of a given table. it should be a candidate key or a primary key.
Columns that create up the composite key can be of different data types.
SQL Syntax To Specify Composite Key:
|
1 2 3 4 5 6 |
CREATE TABLE TABLE_NAME (COLUMN_1, DATA_TYPE_1, COLUMN_2, DATA_TYPE_2, COLUMN_3, DATA_TYPE_3, ??? PRIMARY KEY (COLUMN_1, COLUMN_2, COLUMN_3, ...)); |
In all cases, the SQL composite key created consists of COLUMN1 and COLUMN2.
MySQL:
|
1 2 3 4 5 |
CREATE TABLE SAMPLE_TABLE (COL1 integer, COL2 varchar(30), COL3 varchar(40), PRIMARY KEY (COL1, COL2)); |
Oracle:
|
1 2 3 4 5 |
CREATE TABLE SAMPLE_TABLE (COL1 integer, COL2 varchar(30), COL3 varchar(40), PRIMARY KEY (COL1, COL2)); |
SQL Server
|
1 2 3 4 5 |
CREATE TABLE SAMPLE_TABLE (COL1 integer, COL2 nvarchar(30), COL3 nvarchar(40), PRIMARY KEY (COL1, COL2)); |
Question & Answer: FAQs
1. How can I define a composite primary key consisting of two fields in SQL?
Answer: Let’s take an example, I will create tables and table name “Order” with fields OrderID, ProductID. Thus, the Composite primary key in SQL consists of the fields OrderID and ProductID.
Furthermore, a table can have at most one primary key. Thus, a primary key consists of one or more columns (from that table). If a primary key consists of two or more columns it’s known as a composite primary key.
It is defined as given below syntax:
|
1 2 3 4 5 |
CREATE TABLE Order( OrderID NUMERIC, ProductID NUMERIC, PRIMARY KEY (OrderID, ProductID) ); |
About Author
