- 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>
You may also like...
Recent Comments