Lisa, ----- Original Message ----- From: "Lisa Tong" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Tuesday, May 14, 2002 5:29 AM Subject: foreign key constraint to itself
> > Hi, > > I am just wondering can I create foreign key constraint to itself in mySQL > innodb (the mySQL 3.23.50 release)? > > Let's say we have a table: test_table_01 (id integer, pid integer) type = > innodb. > > Can I create a foreign key on pid and reference to test_table_01 (id)? yes, you must first create the table with the appropriate indexes, and after that use ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY ... to add the self-referential foreign key constraint. Note the following bug fix in 3.23.51: May 2, 2002: If you add self-referential foreign key constraints with ON DELETE CASCADE to tables and a row deletion causes InnoDB to attempt the deletion of the same row twice because of a cascading delete, then you will get an assertion failure in row0ins.c line 511. Fixed in 3.23.51. > Thanks, > > Lisa 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 --------------------------------------------------------------------- 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