Re: Creating foreign key

[vinita vigine Murugiah]

HI
still not working :-(

I didn't do the installation , Do we need to set any option specifically during the installation for foreign key to work??

mysql> CREATE TABLE unit (
-> unitCode CHAR(20) NOT NULL,
-> unitName CHAR(50),
-> unitYear CHAR(20) NOT NULL,
-> PRIMARY KEY (unitCode, unitYear),
-> INDEX unit_code (unitCode)
-> ) TYPE=INNODB;
Query OK, 0 rows affected (0.00 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 unit (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 **********************
mysql> INSERT INTO unit_semesters (unitCode, unitYear, semester)
-> VALUES ("433222", "2000", "1");
Query OK, 1 row affected (0.00 sec)


Silmara wrote:

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