Dyego, ----- Original Message ----- From: "Dyego Souza do Carmo" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Mysql maillist" <[EMAIL PROTECTED]> Sent: Wednesday, November 27, 2002 2:44 PM Subject: constraints and InnoDB
> Hi ! :D > > I would like you help me to solve the following problem: > > Suppose the tables below: > > create table book > ( > Bk_Id integer(4) unsigned not null auto_increment primary key, > Bk_Name varchar(20) > ) type = innoDb; > > create table page > ( > Pg_Id integer(4) unsigned not null auto_increment primary key, > Pg_Notes varchar(20), > Pg_Book integer(4) unsigned not null > ) type = innoDb; > > Create index IPg_Book on page (Pg_Book); > > One book must have one or more pages and one page must be a part of > one book. It must be impossible to have one book with no pages or > one page not associated to one book. Because this, the following > constraint were created: > > alter table book Add Constraint Foreign Key (Bk_Id) > References page(Pg_Book); > > alter table page Add Constraint Foreign Key (Pg_Book) > References book(Bk_Id); > > > The problem is that creating this two constraints, I can't insert > records in these tables. See the example: > > begin; > insert into book (Bk_Id, Bk_Name) VALUES (1, 'Test'); > insert into page (Pg_Book, Pg_Notes) VALUES (1, 'Test'); > commit; > > Both insert commands returns: > Cannot add a child row: a foreign key constraint fails > > Is there a way to create constraints that implements the rules I > need? I would in this circular case recommend coding the check inside your application. InnoDB does not support deferred constraints, hence transactions do not help here. Another way is to do SET FOREIGN_KEY_CHECKS=0; then add the book, then SET FOREIGN_KEY_CHECKS=1; again. Then add the pages. > Bye, > > > sql,query > ------------------------------------------------------------------------- > ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query > ------------------------------------------------------------------------- > E S C R I B A I N F O R M A T I C A > ------------------------------------------------------------------------- > The only stupid question is the unasked one (somewhere in Linux's HowTo) > Linux registred user : #230601 > -- > $ look into "my eyes" Phone : +55 041 296-2311 r.112 > look: cannot open my eyes Fax : +55 041 296-6640 > ------------------------------------------------------------------------- > Reply: [EMAIL PROTECTED] > > --------------------------------------------------------------------- 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
