MySQL - SPLessons

MySQL Constraints

Chapter 11

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

MySQL Constraints

MySQL Constraints

shape Description

By using constraints, different number of conditions can be applied on a column. Constraints setup the RDBMS  and execute the integrity of the database automatically, without need to create triggers and rules. MySQL Constraints are utilized to limit the type of information that can be inserted into a table.

Constraints can be classified into the following types.They are :

shape Conceptual
figure

Unique Constraints

shape Description

By defining a column with Unique Constraint, then that unique constraint column will not accept duplicate values i.e, unique constraint will accept a single character or number. It will not accept repeated values in the table. And this Unique Constraint are similar to the constraints in SQL database.

shape Syntax

create table <table_name>(<column_name1> datatype(size) constraint  type, <column_name2> datatype(size));

table_name => Any accurate table in the database.

column_name => The operation that can be perform on a column in the table.

constraint_type => constraint type will specify the type of constraint, that going to be executed in the database.

shape Examples

By viewing the below example, the concept of unique constraint can be understood easily.

mysql> create table student(stu_id int unique,stu_name varchar(255),fee int);
Query OK, 0 rows affected (0.32 sec)

mysql> insert into student values(1001,'rock',12000);
Query OK, 1 row affected (0.16 sec)

mysql> insert into student values(1002,'hairy',13000);
Query OK, 1 row affected (0.17 sec)

mysql> insert into student values(1003,'scena',13000);
Query OK, 1 row affected (0.16 sec)

mysql> insert into student values(1003,'joe',13000);
ERROR 1062 (23000): Duplicate entry '1003' for key 'stu_id'

In the above example, the unique constraint is applied to column_name emp_id in which only unique emp_id’s values will be accept, and duplicate emp_id values will not be accepted.

Not Null Constraints

shape Description

By defining a column with Not Null Constraint then that constraint column won’t acknowledge null values.And this Not Null Constraint are similar to the constraints in SQL database.

shape Syntax

create table( datatype(size)constraint_type, datatype(size);

table_name => Any accurate table.

Column_name => The operation that can be perform on a column in the table.

constraint_type => Constraint type will specify the type of constraint, that going to be executed in the database.

shape Examples

By viewing the below example,the concept of  not null constraint can be easily understood.

mysql> create table student(stu_id int not null,stu_name varchar(255)not null,fee
 int);
Query OK, 0 rows affected (0.41 sec)

mysql>insert into student values(1001,'jack',12000);
Query OK, 1 row affected (0.10 sec)

mysql> insert into student values(null,'jack',12000);
ERROR 1048 (23000): Column 'stu_id' cannot be null

In the above example, if not null constraint is applied on a column_name emp_id, then that column will accepts the values other than null values.

Primary Key Constraints

shape Description

By defining a column with Primary Key constraint then that primary key constraint won’t acknowledge invalid and copy values. And a table can contain only one primary key. And this Primary Key are similar to the constraints in SQL database.

Primary key = Unique+Not null

Unique constraints can acknowledge null values where as primary key won’t acknowledge null and copy values.

shape Syntax

create table <table_name> (<column_name1>data type(size) constraint type, <column_name2>data type(size),constraint(column_name1);

table_name => Any accurate table.

column_name => The operation that can be performed in the column of a table.

constraint_type => Constraint type will specify the type of constraint, that going to be executed in the database.

shape Examples

By viewing the below example, the concept of primary key constraint can be easily understand.

mysql> create table product(prod_id int not null auto_increment,prod_name varchar
(255)not null,primary key(prod_id));
Query OK, 0 rows affected (0.52 sec)

mysql> insert into product (prod_name) values ('mobile');
Query OK, 1 row affected (0.05 sec)

mysql> insert into product (prod_name) values ('laptop');
Query OK, 1 row affected (0.09 sec)

mysql> insert into product (prod_name) values ('system');
Query OK, 1 row affected (0.09 sec)

mysql> select * from product;
+---------+-----------+
| prod_id | prod_name |
+---------+-----------+
|       1 | mobile    |
|       2 | laptop    |
|       3 | system    |
+---------+-----------+
3 rows in set (0.00 sec)

In the above example, the column prod_id is given not null auto increment constraint and at the same time in another column prod_id is given primary key constraint. In such case we no need to insert each an every value, just writing the prod_name it will automatically display the prod_id values.

Foreign key constraints

shape Description

When a column is defined with Foreign Key constraint, then that foreign key constraint column will accept only the values of given primary key constraint column.And this Foreign Key are similar to the constraints in SQL database.

  • A table can have stand out primary key constraint columns.
  • A table can have any number of foreign key constraints columns.
  • A table in which primary key constraint is defined is called parent key.
  • A table in which foreign key constraint column is defined is called child table.
  • A parent table can have any number of child tables. But a child table can have only one parent table.
  • Without inserting parent record in parent table child record cannot be inserted in child table.
  • Without deleting child record from child table, parent record cannot be inserted from parent table.

shape Syntax

Create table <table_name1>(<column_name1> datatype(size), <column_name2>datatype(size), primary key constraint(column_name1));

create table <table_name2>(<column_name1( datatype(size), <column_name2>datatype(size), datatype(size), FOREIGN KEY constraint(column_name1) REFERENCES(column_name1); );

table_name => Any accurate table.

column_name =>The operation that can be perform on a column in the table.

constraint_type => Constraint type will specify the type of constraint, that going to be executed in the database.

shape Conceptual
figure

shape Examples

By viewing the below example, the concept of foreign key constraint can be easily understand.

mysql> CREATE TABLE Student(stud_id int AUTO_INCREMENT,name varchar(30)NOT NULL,age int NOT NULL,PRIMARY KEY(stud_id));
Query OK, 0 rows affected (0.37 sec)
mysql> insert into Student (name,age) values ('mike',25);
Query OK, 1 row affected (0.11 sec)

mysql> insert into Student (name,age) values ('kate',35);
Query OK, 1 row affected (0.05 sec)

mysql> select * from Student;
+---------+------+-----+
| stud_id | name | age |
+---------+------+-----+
|       1 | mike |  25 |
|       2 | kate |  35 |
+---------+------+-----+
2 rows in set (0.00 sec)

mysql> CREATE TABLE enroll(rol_no int NOT NULL AUTO_INCREMENT,stud_id int NOT NULL,PRIMARY KEY(rol_no),FOREIGN KEY(stud_id)REFERENCES Student(stud_id));
Query OK, 0 rows affected (0.34 sec)
mysql> INSERT INTO enroll(stud_id) values (1);
Query OK, 1 row affected (0.38 sec)

mysql> INSERT INTO enroll (stud_id) values (2);
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO enroll (stud_id) values (3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`employee`.`enroll`, CONSTRAINT `enroll_ibfk_1` FOREIGN KEY (`stud_id`)
REFERENCES `student` (`stud_id`))
mysql> select * from enroll;
+--------+---------+
| rol_no | stud_id |
+--------+---------+
|      1 |       1 |
|      2 |       2 |
+--------+---------+
2 rows in set (0.06 sec)

In the above example, 2 tables have been has created i.e, student and enroll. In student table primary key constraint is assigned to column stud_id, so no need to assign stud_id values just write name and age values, stud_id values will be taken automatically. In the table enroll foreign key is assign to stud_id with reference to primary key. In such case in enroll table assigning stud_id values has to satisfy the stud_id of student values, if not it will terminate the values.

Summary

shape Key Points

  • Constraints – Different number of conditions can be applied on a column with constraints.
  • Unique Constraints – Unique constraint will not accept duplicate values.
  • Not Null Constraint – Not Null constraint will not accept Null values.
  • Primary Key Constraint – Primary key constraint won’t accept null and copied values.
  • Foreign Key Constraint – Foreign key values will accept only the primary key values.