Prev Next
SQL – PRIMARY KEY:
- PRIMARY KEY is a constraint in SQL which is used to identify each record uniquely in a table.
- By default, PRIMARY KEY is UNIQUE.
- PRIMARY KEY can’t have null values.
- A table can have only one PRIMARY KEY either on one column or multiple columns. When multiple columns are defined as PRIMARY KEY, then, it is called COMPOSITE KEY.
- If we try to insert/update duplicate values for the PRIMARY KEY column, then, the query will be aborted.
- Syntax for SQL PRIMARY KEY & COMPOSITE KEY constraint is given below.
SQL Syntax for PRIMARY KEY constraint:
| SQL Syntax for PRIMARY KEY constraint | CREATE TABLE table_name ( column_name1 data_type(size) PRIMARY KEY, column_name2 data_type(size) NOT NULL, column_name3 data_type(size), etc… );OrCREATE TABLE table_name ( column_name1 data_type(size) NOT NULL, column_name2 data_type(size) NOT NULL, column_name3 data_type(size), etc… PRIMARY KEY column_name1; ); |
| SQL Syntax for COMPOSITE KEY constraint | CREATE TABLE table_name ( column_name1 data_type(size) NOT NULL, column_name2 data_type(size) NOT NULL, column_name3 data_type(size), CONSTRAINT Constraint_name PRIMARY KEY (column_name1, column_name2) etc… ); |
Example1 for how to use SQL PRIMARY KEY constraint:
- Please execute below query in SQL to create a table with PRIMARY KEY constraint.
| CREATE TABLE student (Student_ID int PRIMARY KEY, Student_name varchar(255) NOT NULL, City varchar(255), Marks int); |
- Now, a new table called “student” is created and the fields “Student_ID” is defined as PRIMARY KEY. So, this column will be NOT NULL & UNIQUE.
Example2 for how to use SQL PRIMARY KEY constraint on multiple columns (COMPOSITE KEY):
- Please execute below query in SQL to create a table with COMPOSITE KEY i.e. PRIMARY KEY constraint on multiple columns.
| CREATE TABLE student (Student_ID int NOT NULL, Student_name varchar(255) NOT NULL, City varchar(255), Marks int CONSTRAINT CompKey_ID_NAME PRIMARY KEY (Student_ID, Student_name)); |
- Now, a new table called “student” is created and the fields “Student_ID” & “Student_name” is together defined as PRIMARY KEY.
- CompKey_ID_NAME is the Constraint name. So, the combination of Student_ID and Student_name will be NOT NULL & UNIQUE.
How to ALTER a PRIMARY KEY constraint?
- To ALTER a PRIMARY KEY constraint on an existing table, you can use below Syntax.
Syntax for PRIMARY KEY:
| ALTER table table_name ADD PRIMARY KEY column_name; |
Example for PRIMARY KEY:
| ALTER table Student ALTER PRIMARY KEY Marks; |
- To ALTER a COMPOSITE KEY constraint on an existing table, you can use below Syntax.
Syntax for COMPOSITE KEY:
| ALTER table table_name ADD CONSTRAINT Constraint_name PRIMARY KEY (column_name1, column_name2); |
Example for COMPOSITE KEY:
| ALTER table Student ADD CONSTRAINT CompKey_CityMarks PRIMARY KEY ( City, Marks ); |
How to DROP a PRIMARY KEY & COMPOSITE KEY constraint?
- To DROP a PRIMARY KEY constraint on an existing table, you can use below Syntax.
Syntax:
| ALTER table table_name DROP PRIMARY KEY; |
Example:
| ALTER table Student DROP PRIMARY KEY; |
- To DROP a COMPOSITE KEY constraint on an existing table, you can use below Syntax.
Syntax:
| ALTER table table_name DROP CONSTRAINT Constraint_name; |
Example:
| ALTER table Student DROP CONSTRAINT CompKey_CityMarks; |