Foregn Keys and InnoDB not working as expected with 3.23.53
Reposted from Newbie forum-
New to MySQL - (Oracle/SQL Server DBA/Developer for many years). I recently inherited several MySQL databases version 3.23.53 all on various Linux platforms (some are RedHat, some are Debian). The tables within the database have not been created with any referential integrity constraints, so I'm working on correcting that. InnoDB is configured on the server, and I recently added a line to the my.conf file to support create tables where type = InnoDB (mysqld param added: innodb_data_file_path=ibdata:30M).
Perhaps I'm missing something, but I created a parent table and a child table, and referenced the child table to the parent. I expected to not be able to add a row to the child table where the referenced row did not exist in the parent, but as you can see, I was able to. Am I missing something? Is there more explicit documentation someone can point me to on InnoDB and referential integrity (specifically for my version of MySQL, 3.23.53?). Or answer the question as to why this didn't work as I expected? Thanks-
mysql> create table parent
-> (parentid int not null primary key,
-> somevalue varchar(20))
-> Type = InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table parent;
+--------+----------------------------------------------
| Table | Create Table |
+--------+------------------------------------------
| parent | CREATE TABLE `parent` (
`parentid` int(11) NOT NULL default '0',
`somevalue` varchar(20) default NULL,
PRIMARY KEY (`parentid`)
) TYPE=InnoDB |
+--------+----------------------------------------------------
1 row in set (0.00 sec)
mysql> create table child(
-> parentid int not null references parent(parentid),
-> childid int not null,
-> someotherval varchar(30),
-> primary key(parentid,childid))
-> type = innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table child;
+-------+-----------------------------------------------------
| Table | Create Table |
+-------+-------------------------------------------------------
| child | CREATE TABLE `child` (
`parentid` int(11) NOT NULL default '0',
`childid` int(11) NOT NULL default '0',
`someotherval` varchar(30) default NULL,
PRIMARY KEY (`parentid`,`childid`)
) TYPE=InnoDB |
+-------+-----------------------------------------------------------
1 row in set (0.00 sec)
mysql> insert into parent values (1,'blah');
Query OK, 1 row affected (0.00 sec)
mysql> insert into child values (2,2,'yuck');
Query OK, 1 row affected (0.00 sec)
mysql> select * from parent;
+----------+-----------+
| parentid | somevalue |
+----------+-----------+
| 1 | blah |
+----------+-----------+
1 row in set (0.00 sec)
mysql> select * from child;
+----------+---------+--------------+
| parentid | childid | someotherval |
+----------+---------+--------------+
| 2 | 2 | yuck |
+----------+---------+--------------+
1 row in set (0.01 sec)