PRIMARY KEY:- A PRIMARY KEY constrains uniquely identify each row of the table. A PRIMARY KEY can not be a NULL value. Each table has only one PRIMARY KEY and each table column can have a single or multiple PRIMARY KEY. In the case of multiple column PRIMARY KEY, the Combination of values in that column must be unique.
Example :-
For Single column:-
CREATE TABLE employee(
ID int NOT NULL,
Name varchar(255),
username varchar(255).
email varchar(255) NOT NULL,
PRIMARY KEY(ID)
);
Same code we can write this way too :-
CREATE TABLE employee(
ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name varchar(255),
username varchar(255).
email varchar(255) NOT NULL
);
For multiple column PRIMARY KEY:-
CREATE TABLE employee(
ID int NOT NULL,
Name varchar(255),
username varchar(255).
email varchar(255) NOT NULL,
CONSTRAINT emp_PK PRIMARY KEY(ID, email)
);
Above example, only one PRIMARY KEY emp_PK but it is made up of two columns ID and email.
another example of multiple-column PRIMARY KEY:-
CREATE TABLE department(
dep_ID int NOT NULL,
emp_ID int NOT NULL,
PRIMARY KEY (dep_ID, emp_ID),
REFERENCES employee(ID),
FOREIGN KEY(dep_ID),
REFERENCES department(dep_ID)
);
We can add PRIMARY KEY after the creation of the table. For this, we will use ALTER table syntax. For example, Suppose we created a table ’employee’ and we want employee ID to become a PRIMARY KEY. Then we will use the following syntax:-
ALTER TABLE employee ADD PRIMARY KEY(ID);
If we want multiple columns should have a PRIMARY KEY. Then syntax will be:-
ALTER TABLE employee ADD CONSTRAINT emp_PK PRIMARY KEY(ID,emailid);
IF we want to drop a PRIMARY KEY of employee table, Then syntax will be:-
ALTER TABLE employee DROP PRIMARY KEY;