MySQL Constraints

 

1. Not null:

It doesn't allow null values.

Syntax: 

a. create table table_name ( col_name1 col_datatype not null, col_nameN...);

b. alter table tabl_name change column existing col_name new_col_name datatype not null;

2. Primary Key:

It doesn't allow null values.

It doesn't allow duplicate values

It doesn't allow more than one primary key column.

Syntax: 

a. create table table_name ( col_name1 col_datatype not null primary key, col_nameN...);

b. alter table tabl_name change column existing col_name new_col_name datatype not null primary key;

c. alter table table_name drop primary key;

3. Unique Key:

It does allow null values.

It doesn't allow duplicate values

It does allow more than one unique key columns.

Syntax: 

a. create table table_name ( col_name1 col_datatype , col_nameN..., unique(col_name1));

b. alter table tabl_name add constraint unique_name unique(col_name);

c. alter table table_name drop index unique_name ;

4. Composite/Combination Primary Key:

It doesn't allow null values.

It doesn't allow duplicate values

It does allow more than one primary key column.

Syntax: 

a. create table table_name ( col_name1 col_datatype not null, col_nameN..., primary key(col_name1,col_name2));

b. alter table tabl_name add primary key (col_name1, col_name2);

c. alter table table_name drop primary key;

5. MySQL Foreign Key: 

Foreign key is a field in a table that refers to the primary key in another table. That means foreign key apply a relationship between two tables and helps maintain data integrity.

Example we have two tables department and employee with below data.

Department table data:

Dept_id	Dept_name
1	HR
2	IT

 

Employee table data:

Emp_id	Emp_name	Dept_id
101	Ajay	1
102	Barath	2
103	Naveen	5

 

In the employee table last record dept_id column has 5 where it is not present in the department table. So it causes abnormal results. Dept_id 5 has no dept_name then without dept_name how we can allocate the dept_id=5 in the employee table? So to check data integrity between two tables foreign key helps us.

Using foreign key we cannot insert data in the child table, if the data is not available in the parent table.

In this case the department table is a parent table and employee table is a child table.

So create the foreign key in the child table for the column dept_id by taking references of the parent table column dept_id.

Syntax:

ALTER TABLE child_table_name

ADD CONSTRAINT constraint_name

FOREIGN KEY (child_column_name)

REFERENCES referenced_table_name(referenced_column_name);

Example:

ALTER TABLE employee

ADD CONSTRAINT fk_emp_dept_id

FOREIGN KEY (dept_id)

REFERENCES department(dept_id);

 

Now we will get the error while inserting last record in the employee table. So we came to know that we must define the dept_id in the parent table then only we can insert the data in child table.