SQL PRIMARY KEY Constraint

SQL PRIMARY KEY CONSTRAINT example – Learn how to accept unique values and deny null values for columns in MySQL database with example and important points.

POINTS TO NOTICE

1. SQL Primary key identifies unique values for a column or a set of columns.
2. Primary key not allows to store null values.
3. For a table only one primary key is allowed which can have single or multiple columns.

Note:   primary key= not null + unique 

SQL syntax to create primary key – Using create table statement

Syntax:

CREATE TABLE [table_name]
(
column1  column1_definition,
column2  column2_definition,
………………………………..,
constraint constraint_name
primary key  [using  BTREE | HASH](column1,column2………….)
);

you can also specify primary key in the columns definition like below

CREATE TABLE [table_name]
(
column1  column1_definition  primary key,
column2  column2_definition,
………………………………..,

);

Example SQL query to create primary key using create table statement for single column

Query example:

CREATE TABLE ADDRESSBOOK
(
ADDRBOOK_ID BIGINT  primary key,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254)
);

or

CREATE TABLE ADDRESSBOOK
(
ADDRBOOK_ID BIGINT  ,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254) ,
primary key(ADDRBOOK_ID)
);

in this example created primary key for ADDRBOOK_ID on ADDRESSBOOK table.

Example SQL Query to create primary key using create table statement for multiple columns

CREATE TABLE ADDRESSBOOK
(
ADDRBOOK_ID BIGINT  ,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254) ,
primary key(ADDRBOOK_ID,MEMBER_ID)
);

  OR

CREATE TABLE ADDRESSBOOK
(
ADDRBOOK_ID BIGINT  ,
MEMBER_ID BIGINT NOT NULL,
DSIPLAYNAME VARCHAR(254) ,
constraint   ADDRESSBOOK_pk primary key(ADDRBOOK_ID,MEMBER_ID)
);

In this example primary key constraint ( ADDRESSBOOK_pk) defined for two columns( ADDRBOOK_ID,MEMBER_ID)  on  ADDRESSBOOK table.

These two columns must be unique in the ADDRESSBOOK table.

SQL syntax to create primary key using alter table statement

ALTER TABLE [table_name]
ADD CONSTRAINT [CONSTRAINT_NAME]
PRIMARY KEY  [USING BTREE| HASH](Column1,column2….column_n);

Creation of primary key using alter table statement can be applied for single or multiple columns.

Here is the example query to create primary key using alter table statement for single column.

ALTER TABLE ADDRESSBOOK
ADD CONSTRAINT
ADDRESSBOOK_pk primary key(ADDRBOOK_ID);

Here is the example query to create primary key using alter table statement for multiple columns.

ALTER TABLE ADDRESSBOOK
ADD CONSTRAINT
ADDRESSBOOK_pk primary key(ADDRBOOK_ID,MEMBER_ID);

DROP PRIMARY KEY

SQL syntax to drop primary key using ALTER TABLE  statement.

ALTER TABLE [table_name]
drop primary key;

here is the example query to drop the primary key

ALTER TABLE ADDRESSBOOK

DROP PRIMARY KEY.