pb with integrity constraint
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_ENVVARCHAR(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. Something bad in may script ? - 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
Re: pb with integrity constraint
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. And, judging by the .fr domain, you might be interested in French translations of the docs: http://dev.nexen.net/docs/mysql/chargement.html -steve 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_ENVVARCHAR(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. Something bad in may script ? -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | Restriction of free thought and free speech is the most dangerous of | | all subversions. It is the one un-American act that could most easily | | defeat us.| | - Supreme Court Justice (1939-1975) William O. Douglas | ++ - 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
Re: pb with integrity constraint
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_ENVVARCHAR(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
