foreign key 1

[bobthesnake77]

Hello,

I'm trying to build a db with foreign keys, I'm using mysql 3.23.39, here's what I did:

CREATE TABLE persons (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirts (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
PRIMARY KEY (id)
);

But when I insert a shirt with an owner that doesn't exist or when I delete a person which still has shirts, I can do (I shouldn't!!!)

Can someone help me with that??? Thanks...

 

[swampBoogie]

Foreign keys are not supported in that version. They are just stored but not acted upon. Rather insidious.

http://www.mysql.com/doc/en/ANSI_diff_Foreign_Keys.html

 

[bobthesnake77]

Ok thanks, but is the syntax correct at least???

 

[swampBoogie]

SQL syntax is ok.
Mysql has some strange additional restrictions about table type and indexes

http://www.mysql.com/doc/en/SEC467.html

 

[bobthesnake77]

Thanks a lot for your help!!!