MySQL Forums
Forum List  »  InnoDB

Foregn Keys and InnoDB not working as expected with 3.23.53
Posted by: Kelly Penhall-Wilson
Date: February 24, 2005 12:24PM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Foregn Keys and InnoDB not working as expected with 3.23.53
3370
February 24, 2005 12:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.