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>
Recent Comments