Following constraints are commonly used in MYSQL.
- PRIMARY KEY
- FOREIGN KEY
- NOT NULL
- UNIQUE
- DEFAULT
- CHECK
Primary Key
The primary key makes column values unique and can not be null.
Example
CREATE TABLE IF NOT EXISTS users(
id INT(10) AUTO_INCREMENT,
first_name VARCHAR(120),
PRIMARY KEY(id)
);
Foreign Key
Foreign key field refers to Primary key in another table, it is used to create links between two tables, a table that has a primary key is called a parent table and a table that has a foreign key is called a child table.
Example
CREATE TABLE IF NOT EXISTS subjects(
id INT(10) AUTO_INCREMENT,
user_id INT(10),
subject_name VARCHAR(120),
PRIMARY KEY(id),
FOREIGN KEY(user_id) REFERENCES users(id)
);
NOT NULL
MYSQL NOT NULL constraints do not allow fields to be null.
Example
CREATE TABLE IF NOT EXISTS subjects(
id INT(10) AUTO_INCREMENT,
user_id INT(10),
subject_name VARCHAR(120) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(id) REFERENCES users(id)
);
UNIQUE
UNIQUE constraints ensure that the field values are different or unique.
Example
CREATE TABLE IF NOT EXISTS users(
id INT(10),
first_name VARCHAR(120),
last_name VARCHAR(120),
UNIQUE(id)
);
DEFAULT
DEFAULT constraints allow to autofill values when the user does not enter a value for the specified field.
Example
CREATE TABLE IF NOT EXISTS users(
id INT(10),
first_name VARCHAR(120),
last_name VARCHAR(120),
country VARCHAR(120) DEFAULT 'India'
);
CHECK
CHECK constraints check the value before insert into table, ensure that value satisfied given condition with check
Example
CREATE TABLE IF NOT EXISTS users(
id INT(10),
first_name VARCHAR(120),
last_name VARCHAR(120),
age INT(10),
CHECK (age>=18)
);