Loading

MySQL PRIMARY KEY CONSTRAINT

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;

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w