MySQL Server : Open Source Database : MySQL Constraints

• MySQL Constraints
• UNIQUE Constraint
• PRIMARY KEY Constraint
• Defining Auto-Increment
• Referential Integrity Constraints
• CHECK Constraint
• DEFAULT Constraint
• Examples

• MySQL also supports many types of constraints to support data integrity.
• A constraint can be part of a column definition or be given a specific name so that it can be referenced later.
• Constraint violations are generally checked after execution of each statement

NOT NULL Constraint

Defining a Column’s Nullability

• For some columns, it is acceptable to set null or unknown values.
• A null value is not the same as zero or blank, instead it indicates absence of value.
• By default, NULL is assumed, and null values are permitted in the column.

ALTER TABLE Passenger  MODIFY journey_date DATETIME NOT NULL; 

UNIQUE Constraint

• The UNIQUE constraint specifies that the combined value of given columns must either be unique across the table or have NULL values.
• If using just one column, UNIQUE constraint can be specified as part of the column definition.

Syntax:

CONSTRAINT constraintName UNIQUE(column1,column2,...)... 

or for single column ...
<column-def> UNIQUE

PRIMARY KEY Constraint: Defining a Master Key

• PRIMARY KEY constraint is similar to UNIQUE constraint as well as a PK column cannot be NULL.
• A primary key is one or more columns in a table used uniquely identify each row in that table.
• For nearly any table you create, you should define a primary key for that table.
• A table can have only one PRIMARY KEY constraint.

Syntax:

CONSTRAINT constraintName PRIMARY KEY(column1,column2....)... 

or for single column ...
<column-def> PRIMARY KEY 

Use PRIMARY KEY option in the column definition.

pnr INT NOT NULL PRIMARY KEY,... 
or define a separate constraint ...PRIMARY KEY (pnr) 

Defining Auto-Increment Columns

Here, we are making review_id a TRUE key and set it as an AUTO_INCREMENT column.

pnr INT UNSIGNED NOT NULL AUTO_INCREMENT

OR

ALTER TABLE Passenger  
MODIFY pnr MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Referential Integrity Constraints: Defining Foreign Keys

• Referential integrity or FK constraints ensure data in the referencing table has related and needed data in the foreign or referenced table.
• Specifically, each element of the column specified as a foreign key must match an element of a key column (or a set of columns comprising the key) of the specified foreign (referenced) table.
• The foreign key on the column or columns in the originating child table that references the column or columns in the referenced parent table.
• A foreign key leads to consistency between the child table the parent table.

REFERENCES referenced-table(column)... 

or as a separate constraint ...

FOREIGN KEY(referencing-table-columns) REFERENCES  referenced-table(columns)

• There are two key Foreign Key rules generally applied:
• Insert & Update Rule (Referencing Table): Inserting a non-null foreign key or updating a foreign key in the referencing table is permitted when the new or modified foreign key matches a key in the specified referenced table.
• Update & Delete Rule (Referenced Table): A key in the referenced table cannot be altered or removed if being used as a foreign key in any referencing table.

ALTER TABLE Ticket   
ADD CONSTRAINT fk_pnr FOREIGN KEY (pnr) REFERENCES film(pnr) ON DELETE CASCADE ON UPDATE CASCADE; 

CHECK Constraint

The CHECK constraint specifies a condition that must not be violated, and is used commonly for limiting values that can be stored in a column.

CONSTRAINT constraintName CHECK(condition)....
 
<column-def> CHECK(condition) 

Here, if fare must be greater than 200 and reservation_year must be 20th century or newer.

fare FLOAT CHECK(fare > 200.0),
reservation_year CHECK(reservation_year >= 2000), 

DEFAULT values can also be used for undefined values.

• For numeric columns that are not configured with the AUTO_INCREMENT option, the default value is 0.
• For columns configured with the ENUM data type, the default value is the first value specified in the column definition.
• For columns configured with a string data type other than the ENUM type, the default value is an empty string.

Syntax

column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]

Example

mysql> create table passenger
    -> (
    -> pnr INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> passenger_name varchar(30),
    -> age SMALLINT NOT NULL DEFAULT 0,
    -> journey_date timestamp DEFAULT CURRENT_TIMESTAMP
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> DESC passenger;
+----------------+-------------+------+-----+-------------------+----------------+
| Field          | Type        | Null | Key | Default           | Extra		 |
+----------------+-------------+------+-----+-------------------+----------------+
| pnr            | int(11)     | NO   | PRI | NULL              | auto_increment |
| passenger_name | varchar(30) | YES  |     | NULL              |		 |
| age            | smallint(6) | NO   |     | 0                 |		 |
| journey_date   | timestamp   | NO   |     | CURRENT_TIMESTAMP |		 |
+----------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.87 sec)

mysql>

mysql> insert into passenger(passenger_name) values('jamesBond');
Query OK, 1 row affected (0.09 sec)

mysql> insert into passenger(passenger_name) values('john');
Query OK, 1 row affected (0.02 sec)

mysql> select * from passenger;
+-----+----------------+-----+---------------------+
| pnr | passenger_name | age | journey_date        |
+-----+----------------+-----+---------------------+
|   1 | jamesBond      |   0 | 2014-05-11 17:56:56 |
|   2 | john           |   0 | 2014-05-11 17:57:04 |
+-----+----------------+-----+---------------------+
2 rows in set (0.03 sec)

mysql>

You will notice in the above example that only name is getting passed in insert statement and rest of the value taken default.

Creating Table with primary key and Auto_increment

mysql> CREATE TABLE item
    -> (
    ->   itemno int(11) NOT NULL  AUTO_INCREMENT,
    ->   iname varchar(20) DEFAULT NULL,
    ->   qty int(11) DEFAULT 0,
    ->   price int(11) DEFAULT NULL,
    ->   PRIMARY KEY (itemno)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;
Query OK, 0 rows affected (1.51 sec)

mysql>

Table Creation with PRIMARY AND FOREIGN KEY having Referential Integrity.

mysql> create table item ( itemid int primary key, name varchar(20), qty int )Engine=InnoDB default CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.16 sec)

mysql> show create table item \g
+-------+-------------------------------------------------------+
| Table | Create Table               |
+-------+-------------------------------------------------------+
| item  | CREATE TABLE `item` (
  `itemid` int(11) NOT NULL,
  `name` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin | 
+-------+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc item;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| itemid | int(11)     | NO   | PRI | 0       |       | 
| name   | varchar(20) | YES  |     | NULL    |       | 
| qty    | int(11)     | YES  |     | NULL    |       | 
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> create table item ( itemid int, name varchar(20), qty int , primary key(itemid,name) );
Query OK, 0 rows affected (0.07 sec)

mysql> desc item;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| itemid | int(11)     | NO   | PRI | 0       |       | 
| name   | varchar(20) | NO   | PRI |         |       | 
| qty    | int(11)     | YES  |     | NULL    |       | 
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table item \g
+-------+----------------------------------------+
| Table | Create Table                |
+-------+----------------------------------------+
| item  | CREATE TABLE `item` (
  `itemid` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) NOT NULL DEFAULT '',
  `qty` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemid`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 | 
+-------+----------------------------------------+
1 row in set (0.00 sec)

mysql> drop table item;
Query OK, 0 rows affected (0.00 sec)

mysql> create table item ( itemid int, name varchar(20), qty int)Engine=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> alter table item
    -> add primary key(itemid);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table item \g
+-------+-------------------------------------------+
| Table | Create Table                    |
+-------+-------------------------------------------+
| item  | CREATE TABLE `item` (
  `itemid` int(11) NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  PRIMARY KEY (`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into item values(101,'lux',70);
Query OK, 1 row affected (0.04 sec)

mysql> insert into item values(101,'ponds',50);
ERROR 1062 (23000): Duplicate entry '101' for key 'PRIMARY'
mysql> insert into item values(102,'ponds',50);
Query OK, 1 row affected (0.03 sec)

mysql> select * from item;
+--------+-------+------+
| itemid | name  | qty  |
+--------+-------+------+
|    101 | lux   |   70 | 
|    102 | ponds |   50 | 
+--------+-------+------+
2 rows in set (0.00 sec)

mysql> insert into item values(103,'parkAvenue',50);
Query OK, 1 row affected (0.04 sec)

mysql> select * from item;
+--------+------------+------+
| itemid | name       | qty  |
+--------+------------+------+
|    101 | lux        |   70 | 
|    102 | ponds      |   50 | 
|    103 | parkAvenue |   50 | 
+--------+------------+------+
3 rows in set (0.00 sec)

mysql> create table sales ( salesid int, itemid int, qty_sold int, price int, total int,foreign key(itemid) references item(itemid) )Engine=InnoDB;
Query OK, 0 rows affected (0.13 sec)

mysql> show create table sales \g                                                                            
+-------+---------------------------------------------------------------------------+
| Table | Create Table                     |
+-------+---------------------------------------------------------------------------+
| sales | CREATE TABLE `sales` (
  `salesid` int(11) DEFAULT NULL,
  `itemid` int(11) DEFAULT NULL,
  `qty_sold` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `total` int(11) DEFAULT NULL,
  KEY `itemid` (`itemid`),
  CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`itemid`) REFERENCES `item` (`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table sales drop foreign key sales_ibfk_1;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table sales \g
+-------+---------------------------------------------+
| Table | Create Table               |
+-------+---------------------------------------------+
| sales | CREATE TABLE `sales` (
  `salesid` int(11) DEFAULT NULL,
  `itemid` int(11) DEFAULT NULL,
  `qty_sold` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `total` int(11) DEFAULT NULL,
  KEY `itemid` (`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> drop tables sales;
Query OK, 0 rows affected (0.06 sec)

mysql> create table sales ( salesid int, itemid int, qty_sold int, price int, total int)Engine=InnoDB; Query OK, 0 rows affected (0.08 sec)

mysql> alter table sales
    -> add constraint myfk1 foreign key(itemid) references item(itemid);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table sales \g
+-------+-------------------------------------------------------------------+
| Table | Create Table                        |
+-------+-------------------------------------------------------------------+
| sales | CREATE TABLE `sales` (
  `salesid` int(11) DEFAULT NULL,
  `itemid` int(11) DEFAULT NULL,
  `qty_sold` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `total` int(11) DEFAULT NULL,
  KEY `myfk1` (`itemid`),
  CONSTRAINT `myfk1` FOREIGN KEY (`itemid`) REFERENCES `item` (`itemid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 | 
+-------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into sales values(1,105,2,90,180);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`taaza`.`sales`, CONSTRAINT `myfk1` FOREIGN KEY (`itemid`) REFERENCES `item` (`itemid`))
mysql> insert into sales values(1,103,2,90,180);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales values(1,102,2,90,180);
Query OK, 1 row affected (0.04 sec)

mysql> insert into sales values(3,103,5,90,180);
Query OK, 1 row affected (0.03 sec)

mysql> insert into sales values(5,103,2,90,180);
Query OK, 1 row affected (0.04 sec)

mysql> select * from sales;
+---------+--------+----------+-------+-------+
| salesid | itemid | qty_sold | price | total |
+---------+--------+----------+-------+-------+
|       1 |    103 |        2 |    90 |   180 | 
|       1 |    102 |        2 |    90 |   180 | 
|       3 |    103 |        5 |    90 |   180 | 
|       5 |    103 |        2 |    90 |   180 | 
+---------+--------+----------+-------+-------+
4 rows in set (0.00 sec)

mysql> select * from item;
+--------+------------+------+
| itemid | name       | qty  |
+--------+------------+------+
|    101 | lux        |   70 | 
|    102 | ponds      |   50 | 
|    103 | parkAvenue |   50 | 
+--------+------------+------+
3 rows in set (0.00 sec)

mysql> delete from item where itemid=103;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`taaza`.`sales`, CONSTRAINT `myfk1` FOREIGN KEY (`itemid`) REFERENCES `item` (`itemid`))
mysql> delete from item where itemid=101;
Query OK, 1 row affected (0.04 sec)

mysql> select * from item;
+--------+------------+------+
| itemid | name       | qty  |
+--------+------------+------+
|    102 | ponds      |   50 | 
|    103 | parkAvenue |   50 | 
+--------+------------+------+
2 rows in set (0.00 sec)

mysql> select * from sales;
+---------+--------+----------+-------+-------+
| salesid | itemid | qty_sold | price | total |
+---------+--------+----------+-------+-------+
|       1 |    103 |        2 |    90 |   180 | 
|       1 |    102 |        2 |    90 |   180 | 
|       3 |    103 |        5 |    90 |   180 | 
|       5 |    103 |        2 |    90 |   180 | 
+---------+--------+----------+-------+-------+
4 rows in set (0.00 sec)

mysql> alter table sales drop foreign key myfk1;
Query OK, 4 rows affected (0.33 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table sales add constraint myfk1 foreign key(itemid) references item(itemid) on delete cascade;
Query OK, 4 rows affected (0.27 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table sales drop foreign key myfk1;
Query OK, 4 rows affected (0.29 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table sales add constraint myfk1 foreign key(itemid) references item(itemid) on delete cascade on update cascade;
Query OK, 4 rows affected (0.30 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>