डिप्लोमा इन ऑफिस मैनेजमेंट एण्ड अकाउटिंग

डिप्लोमा इन ऑफिस मैनेजमेंट एण्ड अकाउटिंग

Full Stack Web Development with Laravel

Full Stack Web Development with Laravel

Affiliate Program

Affiliate Program

MYSQL table constraints

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)
);

© 2016 - 2023, All Rights are Reserved.