Help understanding foreign keys in MYSQL

[clement]

I'm trying to teach myself SQL and I've gotten stuck up on Foreign keys. My understanding is if you designate something as a foreign key the key that it references has to be in that table for it to allow an INSERT INTO to work. But when I try to mess around with that myself it doesn't seem to reject invalid entries like I would think they should.

 

I did a normal windows install of mysql 5.1 on my machine and from the beginning I did the following:

 

mysql> CREATE DATABASE fake;
Query OK, 1 row affected (0.01 sec)

mysql> use fake;
Database changed

mysql> create table customers(
    ->   cnum INTEGER PRIMARY KEY NOT NULL,
    ->   name CHAR(20));
Query OK, 0 rows affected (0.02 sec)

mysql> create table orders(
    ->   onum INTEGER PRIMARY KEY NOT NULL,
    ->   part INTEGER,
    ->   cnum INTEGER REFERENCES customers);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO customers VALUES(1,'Someguy');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO orders VALUES(1,2,3);
Query OK, 1 row affected (0.00 sec)[/color]

Now. I would think that last INSERT INTO should get rejected seeing as the only customer (cnum) in that table is 1 and 3 doesn't match 1 obviously.

If I look at the table orders I notice that it doesn't show that foreign key in there like I would expect it to.
[color=red]
mysql> SHOW CREATE TABLE orders;
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+
| Table  | Create Table

                          |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+
| orders | CREATE TABLE `orders` (
  `onum` int(11) NOT NULL,
  `part` int(11) DEFAULT NULL,
  `cnum` int(11) DEFAULT NULL,
  PRIMARY KEY (`onum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------+
1 row in set (0.00 sec)

 

Am I doing something wrong in creating the table, is there something somewhere that I need to change.....or am I not understanding foreign keys correctly?

[fenway]

Did you read up on the correct syntax?

[clement]

OK..I'm able to get it to work using either the ALTER TABLE method or defining the foreign key later on inside the table.

 

But the book I have here (a generic SQL book) describes a 'FOREIGN KEY As a Column Constraint' where it a foreign key can be declared inline

 

...

 

ok.it looks like I'm not the only one who has had this problem...

 

http://bugs.mysql.com/bug.php?id=11049

 

It's a confirmed bug

[fenway]

oh, i never even knew you could do it that way....