Loading

MySQL PRIMARY KEY CONSTRAINT

PRIMARY KEY:- A PRIMARY KEY constraint uniquely identifies 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 a 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 the 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 the employee table, Then the syntax will be:-

ALTER TABLE employee DROP PRIMARY KEY;

Read also:-

FOREIGN KEY CONSTRAINT
UNIQUE CONSTRAINT
CHECK CONSTRAINT
NOT NULL CONSTRAINT
DEFAULT CONSTRAINT
CREATE INDEX CONSTRAINT
AUTO_INCREMENT CONSTRAINT
INDEX CONSTRAINT
ENUM CONSTRAINT

For more detail about MySQL constraints please follow here

Share with:


One thought on “MySQL PRIMARY KEY CONSTRAINT

Leave a Reply

Connect with:

z35W7z4v9z8w
Verified by ExactMetrics