tuxfu...@gmail.com
Here we will try to understand the foreign key constraint with CASCADING IN ACTION.
Created a table p_t.
mysql> create table p_t (eid int auto_increment unique,name varchar(10)) engine=Innodb;
Query OK, 0 rows affected (0.08 sec)
mysql> desc p_t;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
Now , created a table s_t with p_t table.
mysql> create table s_t ( id int auto_increment unique,empid int ,shift enum('morning','evening'), FOREIGN KEY (empid) REFERENCES p_t (eid) ON DELETE CASCADE ON UPDATE CASCADE) engine=Innodb;
Query OK, 0 rows affected (0.07 sec)
mysql> desc s_t;
+-------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| empid | int(11) | YES | MUL | NULL | |
| shift | enum('morning','evening') | YES | | NULL | |
+-------+---------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
In mysql for working with mysql constraints, we need to follow few :
1) The primary table should have a primary key.
2) Keys for which we are creating constaints should be of same datatypes.
3) FOREIGN KEYS support is only for the Innodb engines.
Now lets try to insert few values into both the tables:
Inserting values into table p_t
mysql> insert into p_t values (1,'santosh');
Query OK, 1 row affected (0.03 sec)
mysql> insert into p_t values (2,'dinesh');
Query OK, 1 row affected (0.04 sec)
mysql> insert into p_t values (3,'hari');
Query OK, 1 row affected (0.03 sec)
Inserting values into table s_t
mysql> insert into s_t values (1,1,'morning');
Query OK, 1 row affected (0.04 sec)
mysql> insert into s_t values (2,2,'evening');
Query OK, 1 row affected (0.04 sec)
mysql> insert into s_t values (3,4,'evening');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`new`.`s_t`, CONSTRAINT `s_t_ibfk_1` FOREIGN KEY (`empid`) REFERENCES `p_t` (`eid`) ON DELETE CASCADE ON UPDATE CASCADE)
OPPS ERROR !!! , no this is not an error. If you read it it says (empid) is looking for the similar values as p_t(eid). So its constaints in work.
Now lets insert the other values.
mysql> insert into s_t values (3,3,'evening');
Query OK, 1 row affected (0.03 sec)
CASE I:
Now lets see how cascading works for update.
mysql> select * from p_t;
+-----+---------+
| eid | name |
+-----+---------+
| 1 | santosh |
| 2 | dinesh |
| 3 | hari |
+-----+---------+
mysql> select * from s_t;
+----+-------+---------+
| id | empid | shift |
+----+-------+---------+
| 1 | 1 | morning |
| 2 | 2 | evening |
| 3 | 3 | evening |
+----+-------+---------+
3 rows in set (0.00 sec)
'Hari' wants his eid to change to '33' , lets oblige to his request.
mysql> update p_t set eid='33' where name='hari';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Now lets see both the tables,
mysql> select * from p_t;
+-----+---------+
| eid | name |
+-----+---------+
| 1 | santosh |
| 2 | dinesh |
| 33 | hari |
+-----+---------+
3 rows in set (0.00 sec)
mysql> select * from s_t;
+----+-------+---------+
| id | empid | shift |
+----+-------+---------+
| 1 | 1 | morning |
| 2 | 2 | evening |
| 3 | 33 | evening |
+----+-------+---------+
3 rows in set (0.00 sec)
If you notice both the eid and empid have modified.
CASE II:
Now lets see how cascading works for delete.
Hari wants to move to another company 'GOOGLE'. So i have to delete his record.
mysql> delete from p_t where eid='33';
Query OK, 1 row affected (0.04 sec)
mysql> select * from p_t;
+-----+---------+
| eid | name |
+-----+---------+
| 1 | santosh |
| 2 | dinesh |
+-----+---------+
2 rows in set (0.00 sec)
mysql> select * from s_t;
+----+-------+---------+
| id | empid | shift |
+----+-------+---------+
| 1 | 1 | morning |
| 2 | 2 | evening |
+----+-------+---------+
2 rows in set (0.00 sec)
If
you see now the record has been removed from both the tables.
MYsql Help:
http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html
mysql> help 'alter table';
mysql> help 'create table';
I will sending couple of more example on foreign key constraints in later posts. Let me know in case of any queries.
tuxfu...@gmail.com
mysql> create table s_t ( id int auto_increment unique,empid int ,shift enum('morning','evening'), constraint fk_new FOREIGN KEY (empid) REFERENCES p_t (eid) ON DELETE CASCADE ON UPDATE CASCADE) engine=Innodb;
Query OK, 0 rows affected (0.08 sec)
mysql> show create table s_t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| s_t | CREATE TABLE `s_t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`empid` int(11) DEFAULT NULL,
`shift` enum('morning','evening') DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `fk_new` (`empid`),
CONSTRAINT `fk_new` FOREIGN KEY (`empid`) REFERENCES `p_t` (`eid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
That said if you dont add the constraint it will be created automatically.
mysql> show create table s_t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| s_t | CREATE TABLE `s_t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`empid` int(11) DEFAULT NULL,
`shift` enum('morning','evening') DEFAULT NULL,
UNIQUE KEY `id` (`id`),
KEY `empid` (`empid`),
CONSTRAINT `s_t_ibfk_1` FOREIGN KEY (`empid`) REFERENCES `p_t` (`eid`) ON DELETE CASCADE ON UPDATE CASCADE
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Check the content in red in create statements.
Thanks,
santosh