Change room to unit
FOREIGN KEY (unitCode) REFERENCES room (unitCode) ON DELETE CASCADE
FOREIGN KEY (unitCode) REFERENCES unit(unitCode) ON DELETE CASCADE
Hope this helps,
Sil
----- Original Message -----
From: "vinita vigine Murugiah" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, November 17, 2002 9:15 PM
Subject: Creating foreign key
> 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
>
---------------------------------------------------------------------
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
