• Articles
  • Tutorials
  • Interview Questions

MySQL Constraints

Table of content

Show More

Some of the common MySQL constraints are:

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT
  • AUTO_INCREMENT

Watch this video on MySQL Interview Questions and Answers

Video Thumbnail

NOT NULL

  • NOT NULL Constraints on CREATE TABLE

Below is an example of NOT NULL MySQL constraints “UserID”, “FirstName”, and “LastName” columns will NOT accept NULL values when the “Mytable” table is created.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
JobPosition varchar(255),
);
  • NOT NULL Constraints on ALTER TABLE

Below is an example to create a NOT NULL ALTER TABLE MySQL constraints on the “JobPosition” column when the “Mytable” table is created.

Example

ALTER TABLE Mytable
MODIFY JobPosition varchar(255) NOT NULL;

Get familiar with the top SQL Interview Questions and answers to get a head start in your career!

UNIQUE

Unique constraints are used to ensure that all the values which are in a column must be different.

  • UNIQUE Constraints on CREATE TABLE

Below is an example to create a UNIQUE constraint on the “UserID” column in the table “Mytable”.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
UNIQUE (UserID)
);

Use the following SQL syntax to name a UNIQUE constraint and define a UNIQUE constraint on several columns.

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255),
CONSTRAINT UC_Mytab UNIQUE (UserID, JobPosition)
);
  • UNIQUE Constraints on ALTER TABLE

Below is an example to create a UNIQUE constraint on the “UserID” column in the existing table.

Example

ALTER TABLE Mytable
ADD UNIQUE (UserID);

Use the following SQL syntax to name a UNIQUE constraint and define a UNIQUE constraint on several columns.

ALTER TABLE Mytable
ADD CONSTRAINT UC_Mytab UNIQUE (UserID, JobPosition);
  • UNIQUE Constraints on DROP TABLE

Below is an example to DROP a UNIQUE Constraint.

ALTER TABLE Mytable
DROP INDEX UC_Mytab;
Preparing for the SQL interviews? Check out these SQL Server Interview Questions.

PRIMARY KEY

In a table, the PRIMARY KEY constraint identifies each record in a unique manner.

  • PRIMARY KEY ON CREATE TABLE

Below is an example to create a PRIMARY KEY on the “UserID” column in a table called “Mytable”.

Example

CREATE TABLE Mytable (
	UserID int NOT NULL,
	FirstName varchar(255) NOT NULL,
	LastName varchar(255),
	JobPosition varchar(255),
    PRIMARY KEY (ID)
);

Use the following SQL syntax to enable the naming of a PRIMARY KEY constraint and to define a PRIMARY KEY constraint on multiple columns.

CREATE TABLE Mytable (
	UserID int NOT NULL,
	FirstName varchar(255) NOT NULL,
	LastName varchar(255),
	JobPosition varchar(255),
    CONSTRAINT PK_Mytab PRIMARY KEY (UserID, JobPosition)
);
  • PRIMARY KEY ON ALTER TABLE

Below is an example to create a PRIMARY KEY on the “UserID” column in the existing table.

Example

ALTER TABLE Mytable
ADD PRIMARY KEY (ID);

Use the following SQL syntax to name a PRIMARY KEY constraint and define a PRIMARY KEY constraint on multiple columns.

ALTER TABLE Mytable
ADD CONSTRAINT PK_Mytab PRIMARY KEY (UserID, JobPosition);
  • PRIMARY KEY ON DROP TABLE

Below is an example to DROP a PRIMARY KEY constraint.

ALTER TABLE Mytable
DROP PRIMARY KEY;

Want to learn more about SQL? Here is the Online SQL training provided by Intellipaat.

FOREIGN KEY

  • FOREIGN KEY ON CREATE TABLE

A field or column that joins two tables together is known as a foreign key.

Below is an example of a Foreign Key Constraint.

There are two tables named “Customer” & “Orders”.

Example

Customer Table
CREATE TABLE Customer (
CustomerID int NOT NULL PRIMARY KEY,
Name varchar(45) NOT NULL,
Age int,
City varchar(25)
);

Orders Table

CREATE TABLE Orders (
Order_ID int NOT NULL PRIMARY KEY,
Order_Num int NOT NULL,
CustomerID int,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Here, you can see that the “CustomerID” field in the “Orders” table points to the “CustomerID” field in the “Customer” table. The “CustomerID” is the PRIMARY KEY in the “Customer” table, while the “CustomerID” column of the “Orders” table is a FOREIGN KEY.

CHECK

A check constraint limits the value in a particular column and is used to consider whether the value for that column is valid or not under the provided conditions.

  • CHECK Constraints on CREATE TABLEBelow is an example of CHECK constraints on the “VoterAge” column in a table named “Mytable” which will ensure that a user’s age must be 18 or more.

Example

CREATE TABLE Mytable (
	UserID int NOT NULL,
	FirstName varchar(255) NOT NULL,
	LastName varchar(255),
	VoterAge int,
    CHECK (VoterAge>=18)
);
  • CHECK Constraints on ALTER TABLE

Below is an example of creating CHECK constraints on the “VoterAge” column in an existing table.

Example

ALTER TABLE Mytable
ADD CHECK (VoterAge>=18);
  • CHECK Constraints on DROP TABLE

Below is an example to DROP a CHECK constraint from the table.

Example

ALTER TABLE Mytable
DROP CHECK CHK_VoterAge;

DEFAULT

To specify a default value for a column, the DEFAULT Constraint is used.

  • DEFAULT Constraints on CREATE TABLE

Below is an example that sets a DEFAULT value in the column “JobPosition” in a table named “Mytable”.

Example

CREATE TABLE Mytable (
UserID int NOT NULL,
FirstName varchar(255) NOT NULL,
LastName varchar(255),
JobPosition varchar(255) DEFAULT 'Technical'
);
  • DEFAULT Constraints on ALTER TABLE

Below is an example to set a DEFAULT value in the column “JobPosition” in an existing table.

Example

ALTER TABLE Mytable
ALTER JobPosition SET DEFAULT 'Technical’;
  • DEFAULT Constraints on DROP TABLE

Below is an example to DROP a DEFAULT Constraint from the table.

ALTER TABLE Mytable
ALTER JobPosition DROP DEFAULT;

AUTO_INCREMENT

A function called Auto Increment works with numeric data types. Each time a record is added to a table, it instantly generates a series of numerical numbers.

Below is an example where the UserID column will be auto-incremented in the table named “Mytable”.

Example

mysql> CREATE TABLE Mytable(  
UserID int NOT NULL AUTO_INCREMENT,  
name CHAR(30) NOT NULL,   
PRIMARY KEY (UserID)
);

Next, we need to insert the values into the table “Mytable”.

mysql> INSERT INTO Mytable (name) VALUES   
('Himanshu'),('Yash'),('Ashish'),   
('Anil'),('Mukul'),('Ravi');  

And automatically MySQL will generate a series of numerical numbers.

 

Course Schedule

Name Date Details
SQL Training 23 Nov 2024(Sat-Sun) Weekend Batch View Details
30 Nov 2024(Sat-Sun) Weekend Batch
07 Dec 2024(Sat-Sun) Weekend Batch

About the Author

Data Engineer

As a skilled Data Engineer, Sahil excels in SQL, NoSQL databases, Business Intelligence, and database management. He has contributed immensely to projects at companies like Bajaj and Tata. With a strong expertise in data engineering, he has architected numerous solutions for data pipelines, analytics, and software integration, driving insights and innovation.