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
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;
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.
Visit our SQL Community to get answers to all your queries!