| ☰ See All Chapters |
Primary key constraint in MySQL
Primary key uniquely identifies each record in a database table. If primary key constraint is applied on a column then every row in the table must have a primary key and no two rows can have the same primary key. Primary key value can never be null nor can be modified or updated.
Columns declared as PRIMARY KEY can be declared as NOT NULL & UNIQUE but PRIMARY KEY enforces both NOT NULL & UNIQUE constraints. So there is no reason to declare columns as NOT NULL & UNIQUE which are declared as PRIMARY KEY.
Primary key helps to relate with other tables using foreign key. Foreign key is studied in next chapter.
If Columns declared as PRIMARY KEY is declared as NOT NULL or UNIQUE then problem arises while creating FOREIGN KEY, so generally don’t declare column as NOT NULL or UNIQUE which are declared as PRIMARY KEY.
Syntax to add Primary Key constraint in MySQL
CREATE TABLE <table-name> ( <column-name1> <data-type(size)> PRIMARY KEY, <column-name2> <data-type(size)> <constraint> <column-name2> <data-type(size)> <constraint> <column-name2> <data-type(size)> <constraint> ); |
Example to add Primary Key constraint in MySQL
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT , PHONE NUMERIC(10) PRIMARY KEY, EMAIL VARCHAR(30) ); |
Composite primary key
Sometimes table cannot have any columns with unique values in each record, but it is necessary to define a primary key to relate it to other tables, in such situations we can define composite primary key.
Composite primary key is defined by set of columns which can uniquely identify every row in the table.
Syntax to add Composite Primary Key constraint in MySQL
------------Syntax/Approach 1--------------
CREATE TABLE <table-name> ( <column-name1> <data-type(size)> , <column-name1> <data-type(size)>, <column-name1> <data-type(size) );
ALTER TABLE <table-name>ADD ( CONSTRAINT <Name for Constraint> PRIMARY KEY (List of Column Names) );
------------Syntax/Approach 2-------------- CREATE TABLE <table-name> ( <column-name1> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, PRIMARY KEY (List of Column Names) );
------------Syntax/Approach 3--------------
CREATE TABLE <table-name> ( <column-name1> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, <column-name2> <data-type(size)> <constraint>, CONSTRAINT <Name for Constraint> PRIMARY KEY (List of Column Names) );
|
Example to add Primary Key constraint in MySQL
------------Syntax/Approach 1--------------
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT, PHONE NUMERIC(10), EMAIL VARCHAR(30) );
ALTER TABLE CUSTOMER ADD ( CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID,PHONE,EMAIL) );
------------Syntax/Approach 2--------------
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT NOT NULL, PHONE NUMERIC(10) NULL, EMAIL VARCHAR(30) UNIQUE, PRIMARY KEY (CUSTOMER_ID,PHONE) );
------------Syntax/Approach 3--------------
CREATE TABLE CUSTOMER ( CUSTOMER_ID INT NOT NULL, PHONE NUMERIC(10) NULL, EMAIL VARCHAR(30) UNIQUE, CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID,PHONE,EMAIL );
|
All Chapters
