Dominique, At 05:45 AM 1/15/2002 -0800, Steve Edberg wrote: >See doc sections: > > http://www.mysql.com/doc/A/N/ANSI_diff_Foreign_Keys.html >and > http://www.mysql.com/doc/C/R/CREATE_TABLE.html > > From the CREATE TABLE docs: > > "The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do > anything. The syntax for them is provided only for compatibility, to make > it easier to port code from other SQL servers and to run applications > that create tables with references. See section 1.7.4 MySQL Differences > Compared to ANSI SQL92."
.... >At 11:46 AM +0100 1/15/02, TOMASSONI Dominique wrote: >>Hi the list, >> >>I try to create tables with integrity constraint but it seems no running. >> >>I've got the next script : >> >>create table USERS >>( >> USER_CODE INT(8) not null, >> USER_NAME VARCHAR(30) not null, >> primary key (USER_CODE) >>) >>; >>create table ENV >>( >> USER_CODE INT(8) not null, >> CODE_ENV VARCHAR(6) not null, >> primary key (USER_CODE, CODE_ENV), >> constraint FK_USER foreign key (USER_CODE) references USERS(USER_CODE) >>) >>; >> >>The tables creation are ok, but when I insert values in the table ENV >>without any data in USERS it works but normally it wouldn't. The first section that Steve cites makes it clear that foreign key constraints are enforced for InnoDB-type tables: "In MySQL Server 3.23.44 and up, InnoDB tables supports checking of foreign key constraints.... For other table types, MySQL Server does parse the FOREIGN KEY syntax in CREATE TABLE commands, but without further action being taken." Since you do not specify TYPE = InnoDB, that would explain your difficulty, as Steve says. But it is also important not to expect too much of a foreign key constraint, even when it is being enforced. FOREIGN KEY does _not_ imply NOT NULL. The FOREIGN KEY constraint by itself does not protect against inserting orphaned child records. If you want to make sure that each child record has a corresponding parent record, then you must specify NOT NULL in addition to FOREIGN KEY. I realize that this does not help you with today's problem, but perhaps it may help avert tomorrow's problem. (For me, an Oracle user, it was last month's problem, and the memory is still painfully fresh.) Hope this helps. --Erv --------------------------------------------------------------------- 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
