SQL – PRIMARY KEY & COMPOSITE KEY

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;

Prev     Next

Share this website !!!
Facebook Twitter Linkedin