Creating foreign key

[vinita vigine Murugiah]

HI
I'm not sure what I'm doing wrong in the following example, the foreign key is NOT working. I'm using ver3.23.53.

-------------------------------------------------------------------------------------------------------------
mysql> CREATE TABLE unit (
-> unitCode CHAR(20) NOT NULL,
-> unitName CHAR(50),
-> unitYear CHAR(20) NOT NULL,
-> PRIMARY KEY (unitCode, unitYear)
-> ) TYPE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE unit_semesters (
-> unitCode CHAR(20) NOT NULL,
-> unitYear CHAR(20) NOT NULL,
-> semester ENUM("S", "1", "2", "Y", "R", "N") NOT NULL,
-> PRIMARY KEY (unitCode, unitYear, semester),
-> INDEX unit_code (unitCode),
-> FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE
-> ) TYPE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO unit (unitCode, unitName, unitYear)
-> VALUES ("433121", "testing1", "2000");
Query OK, 1 row affected (0.00 sec)

**************** ERROR*******************
I expect the following INSERT to fail since the unitCode(433222) doesn't exist in the table unit BUT

mysql> INSERT INTO unit_semesters (unitCode, unitYear, semester)
-> VALUES ("433222", "2000", "1");
Query OK, 1 row affected (0.00 sec)

mysql> select * from unit;
+----------+----------+----------+
| unitCode | unitName | unitYear |
+----------+----------+----------+
| 433121 | testing1 | 2000 |
+----------+----------+----------+
1 row in set (0.00 sec)

mysql> select * from unit_semesters;
+----------+----------+----------+
| unitCode | unitYear | semester |
+----------+----------+----------+
| 433222 | 2000 | 1 |
+----------+----------+----------+
1 row in set (0.00 sec)

mysql>

Thank you very much
vinita



---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php