Re: Can not add foreign key constraints
On 4/24/2017 9:18, David Mehler wrote: Hello, I'm trying to add a table to an existing database. I'm wanting it to get one of it's fields from an already existing table. I've done this before in this database. This works: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(128) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; This does not: CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Can anyone spot the situation? Let's see the CREATE TABLE statement for the referenced table, and the error message. PB - Thanks. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Can not add foreign key constraints
Am 24.04.2017 um 16:18 schrieb David Mehler: I'm trying to add a table to an existing database. I'm wanting it to get one of it's fields from an already existing table. I've done this before in this database. This works: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(128) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; This does not: CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Can anyone spot the situation? wouldn't it be cool if you post the errors you get from the start? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Can not add foreign key constraints
Hello, I'm trying to add a table to an existing database. I'm wanting it to get one of it's fields from an already existing table. I've done this before in this database. This works: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(128) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY `user` (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; This does not: CREATE TABLE IF NOT EXISTS `lastauth` ( `user` varchar(40) NOT NULL, `remote_ip` varchar(18) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user`), FOREIGN KEY (user) REFERENCES virtual_users(user) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Can anyone spot the situation? Thanks. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
MySQL ignores foreign key constraints
Hi An ideas why MySQL silently ignores any foreign key constraints I define for the following tables? mysql desc book; +--+---+--+-+-+- --+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- --+ | pkisbn | varchar(20) | NO | PRI | NULL| | | fkpublisher_id | tinyint(3) unsigned | NO | MUL | NULL| | | title| varchar(50) | NO | | NULL| | | subtitle | varchar(50) | NO | | NULL| | 13 rows in set (0.01 sec) mysql desc book_author; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | fkauthor_id | mediumint(10) unsigned | NO | MUL | NULL| | | fkisbn | varchar(20)| NO | MUL | NULL| | +-++--+-+-+---+ 2 rows in set (0.00 sec) mysql desc author; +-++--+-+-+- ---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+- ---+ | pkauthor_id | mediumint(10) unsigned | NO | PRI | NULL| auto_increment | | fname | varchar(20)| NO | | NULL| | | initial | varchar(5) | YES | | NULL| | | lname | varchar(20)| NO | | NULL| | +-++--+-+-+- ---+ 4 rows in set (0.00 sec) Mimi
Re: MySQL ignores foreign key constraints
Try show create table ... ; A On Fri, May 20, 2011 at 12:07 PM, Mimi Cafe mimic...@googlemail.com wrote: Hi An ideas why MySQL silently ignores any foreign key constraints I define for the following tables? mysql desc book; +--+---+--+-+-+- --+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- --+ | pkisbn | varchar(20) | NO | PRI | NULL| | | fkpublisher_id | tinyint(3) unsigned | NO | MUL | NULL| | | title| varchar(50) | NO | | NULL| | | subtitle | varchar(50) | NO | | NULL| | 13 rows in set (0.01 sec) mysql desc book_author; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | fkauthor_id | mediumint(10) unsigned | NO | MUL | NULL| | | fkisbn | varchar(20)| NO | MUL | NULL| | +-++--+-+-+---+ 2 rows in set (0.00 sec) mysql desc author; +-++--+-+-+- ---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+- ---+ | pkauthor_id | mediumint(10) unsigned | NO | PRI | NULL| auto_increment | | fname | varchar(20)| NO | | NULL| | | initial | varchar(5) | YES | | NULL| | | lname | varchar(20)| NO | | NULL| | +-++--+-+-+- ---+ 4 rows in set (0.00 sec) Mimi
Re: MySQL ignores foreign key constraints
WHat are the table engine types ? On Fri, May 20, 2011 at 4:37 PM, Mimi Cafe mimic...@googlemail.com wrote: Hi An ideas why MySQL silently ignores any foreign key constraints I define for the following tables? mysql desc book; +--+---+--+-+-+- --+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- --+ | pkisbn | varchar(20) | NO | PRI | NULL| | | fkpublisher_id | tinyint(3) unsigned | NO | MUL | NULL| | | title| varchar(50) | NO | | NULL| | | subtitle | varchar(50) | NO | | NULL| | 13 rows in set (0.01 sec) mysql desc book_author; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | fkauthor_id | mediumint(10) unsigned | NO | MUL | NULL| | | fkisbn | varchar(20)| NO | MUL | NULL| | +-++--+-+-+---+ 2 rows in set (0.00 sec) mysql desc author; +-++--+-+-+- ---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+- ---+ | pkauthor_id | mediumint(10) unsigned | NO | PRI | NULL| auto_increment | | fname | varchar(20)| NO | | NULL| | | initial | varchar(5) | YES | | NULL| | | lname | varchar(20)| NO | | NULL| | +-++--+-+-+- ---+ 4 rows in set (0.00 sec) Mimi -- Thanks Suresh Kuna MySQL DBA
RE: MySQL ignores foreign key constraints
Aha, got the offender. Unlike all other ones, tables book_author was MyISAM instead of Innodb. Now everything works alter table book_author add foreign key (fkauthor_id) references author (pkauthor_id); Query OK, 12 rows affected (0.39 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql alter table book_author add foreign key (fkisbn) references book (pkisbn); Query OK, 12 rows affected (0.42 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql show create table book_author; +-+- ---+ | Table | Create Table | +-+- ---+ | book_author | CREATE TABLE `book_author` ( `fkauthor_id` mediumint(10) unsigned NOT NULL, `fkisbn` varchar(20) NOT NULL, KEY `fkisbn` (`fkisbn`), KEY `fkauthor_id` (`fkauthor_id`), CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`fkisbn`) REFERENCES `book` (`pkisbn`), CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`fkauthor_id`) REFERENCES `author` (`pkauthor_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-+-- Thanks From: Suresh Kuna [mailto:sureshkumar...@gmail.com] Sent: 20 May 2011 12:15 To: Mimi Cafe Cc: mysql@lists.mysql.com Subject: Re: MySQL ignores foreign key constraints WHat are the table engine types ? On Fri, May 20, 2011 at 4:37 PM, Mimi Cafe mimic...@googlemail.com wrote: Hi An ideas why MySQL silently ignores any foreign key constraints I define for the following tables? mysql desc book; +--+---+--+-+-+- --+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+- --+ | pkisbn | varchar(20) | NO | PRI | NULL| | | fkpublisher_id | tinyint(3) unsigned | NO | MUL | NULL| | | title| varchar(50) | NO | | NULL| | | subtitle | varchar(50) | NO | | NULL| | 13 rows in set (0.01 sec) mysql desc book_author; +-++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+---+ | fkauthor_id | mediumint(10) unsigned | NO | MUL | NULL| | | fkisbn | varchar(20)| NO | MUL | NULL| | +-++--+-+-+---+ 2 rows in set (0.00 sec) mysql desc author; +-++--+-+-+- ---+ | Field | Type | Null | Key | Default | Extra | +-++--+-+-+- ---+ | pkauthor_id | mediumint(10) unsigned | NO | PRI | NULL| auto_increment | | fname | varchar(20)| NO | | NULL| | | initial | varchar(5) | YES | | NULL| | | lname | varchar(20)| NO | | NULL| | +-++--+-+-+- ---+ 4 rows in set (0.00 sec) Mimi -- Thanks Suresh Kuna MySQL DBA
Falcon and Foreign Key Constraints
Hi, I thought that Falcon supported FKs, but in my test, it doesn't? Can someone confirm this? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Falcon and Foreign Key Constraints
On Thu, Sep 20, 2007 at 11:20:19AM +0200, Martijn Tonies wrote: I thought that Falcon supported FKs, but in my test, it doesn't? Can someone confirm this? The Falcon storage engine does not currently support foreign keys. (The underlying engine does have support for them, but this functionality is not exposed to the MySQL server.) Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Falcon and Foreign Key Constraints
On Thu, Sep 20, 2007 at 11:20:19AM +0200, Martijn Tonies wrote: I thought that Falcon supported FKs, but in my test, it doesn't? Can someone confirm this? The Falcon storage engine does not currently support foreign keys. (The underlying engine does have support for them, but this functionality is not exposed to the MySQL server.) Right, thanks. Any idea if this is gonna happen soon? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign key constraints - Known issues ?
Hello All, I just wanted to know whether there are any known issues in defining and using Foreign key constraints in MySQL 4 and MySQL 5. To be specific, are there any issues on using ON DELETE CASCADE and ON UPDATE CASCADE? Would there be any performance issues when we define Foreign key constraints? We dont define Foreign Key constraints here. But at the conceptual level we know that there are foreign keys. But we do not create foreign keys at the physical level. Is this right? Thanks Regards, Ratheesh
Re: Foreign key constraints - Known issues ?
I just wanted to know whether there are any known issues in defining and using Foreign key constraints in MySQL 4 and MySQL 5. To be specific, are there any issues on using ON DELETE CASCADE and ON UPDATE CASCADE? Would there be any performance issues when we define Foreign key constraints? We dont define Foreign Key constraints here. But at the conceptual level we know that there are foreign keys. But we do not create foreign keys at the physical level. Is this right? No, that is not right, cause it means anyone (or any mistake in your software) can still bugger up your data. Make sure to install your constraints, it is able to save you countless hours in the future. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Copy some datasets including the foreign key constraints
I am not an expert in this, but it looks as though you are trying to define foreign keys on your parent table. I thought you had to define the parent table without foreign keys, and then define a child table with foreign key constraints. I'm also not sure if you are trying to redefine the key columns, or the key values. If you are changing the key values in the copies of your tables, I think you have to copy both the parent and the child and then update the key value in the parent. That should cascade down into the child. Perhaps I do not understand what you are doing, since you said it works when you use your PHP code. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Eidner, Fabian [mailto:[EMAIL PROTECTED] Sent: Thursday, November 23, 2006 6:47 AM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: AW: Copy some datasets including the foreign key constraints Hello Jerry, I can give you the shema of my parent table: CREATE TABLE `demo_employee` (\n `idemployee` int(10) unsigned NOT NULL auto_increment,\n `demo_mandt_idmandt` int(10) unsigned NOT NULL default '0',\n `demo_adress_idadress` int(10) unsigned NOT NULL default '0',\n `demo_adress_demo_region_idregion` int(10) unsigned NOT NULL default '0',\n `demo_adress_demo_country_idcountry` int(10) unsigned NOT NULL default '0',\n `demo_biz_role_idbiz_role` int(10) unsigned NOT NULL default '0',\n `demo_job_idjob` int(10) unsigned NOT NULL default '0',\n `demo_user_iduser` int(10) unsigned NOT NULL default '0',\n `I_EE_EMPLOYEE_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_GIVEN_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_MIDDLE_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_FAMILY_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_INITIALS_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_BANK_ACCOUNT_HOLD` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_BANK_ACCOUNT_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_PO_AD_FLO_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_PO_AD_ROOM_ID` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_START_DATE_HIRING` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_END_DATE_HIRING` varchar(25) collate utf8_unicode_ci default NULL,\n `I_PEH_EVENT_TYPE_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_TYPE_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_ADMIN_CATEGORY_CODE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_COMPANY_ID` int(10) unsigned default NULL,\n `I_PEH_POS_ASS_BEG_DATE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_POSITION_MAIN_INDICATOR` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_POS_ASSIGNMENT_PERCENT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_FIRST_VALUE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_FIRST_UNIT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_SEC_VALUE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_AWT_RATE_SEV_UNIT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_WA_NOTICE_PERIOD` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_GENDER_CODE` int(10) unsigned default NULL,\n `I_EE_CO_MATERIAL_STATUS_CODE` int(10) unsigned default NULL,\n `I_EE_CO_FROM_OF_ADRESS_CODE` int(10) unsigned default NULL,\n `I_EE_CO_ACADEMIC_TITLE_CODE` int(10) unsigned default NULL,\n `I_EE_CO_BIRTH_PLACE_NAME` varchar(45) collate utf8_unicode_ci default NULL,\n `I_EE_CO_BIRTH_NAME` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_BIRTH_DATE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_CO_ADDITIONAL_ACA_TITLE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_HO_ADD_USAGE_DEFAULT` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_HO_ADD_USAGE` varchar(25) collate utf8_unicode_ci default NULL,\n `I_EE_WK_ADD_USAGE` varchar(25) collate utf8_unicode_ci default NULL,\n `demo_user` int(10) unsigned default NULL,\n PRIMARY KEY (`idemployee`,`demo_mandt_idmandt`,`demo_adress_idadress`,`dem o_adress_demo_region_idregion`,`demo_adress_demo_country_idcountry`, `demo_biz_role_idbiz_role`,`demo_job_idjob`,`demo_user_iduser` ),\n KEY `demo_employee_FKIndex1` (`demo_mandt_idmandt`),\n KEY `demo_employee_FKIndex2` (`demo_adress_idadress`,`demo_adress_demo_region_idregion`,`de mo_adress_demo_country_idcountry`),\n KEY `demo_employee_FKIndex3` (`demo_biz_role_idbiz_role`),\n KEY `demo_employee_FKIndex4` (`demo_job_idjob`),\n KEY `demo_employee_FKIndex5` (`demo_user_iduser`),\n CONSTRAINT `demo_employee_ibfk_1` FOREIGN KEY (`demo_mandt_idmandt`) REFERENCES `demo_mandt` (`idmandt`) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT `demo_employee_ibfk_2` FOREIGN KEY
AW: Copy some datasets including the foreign key constraints
[mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 22. November 2006 15:54
An: Eidner, Fabian; mysql@lists.mysql.com
Betreff: RE: Copy some datasets including the foreign key constraints
Do you know what your query looks like after variable substitution? That
always helps me a lot. If you can't dump it to the screen because it breaks
too much of your system, look into syslog(). That's what I use when
debugging the heart of our online system, and it has helped me find missing
single quotes and the like.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Eidner, Fabian [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 22, 2006 7:46 AM
To: mysql@lists.mysql.com
Subject: Copy some datasets including the foreign key constraints
Hello list,
I'm pretty new here.
But i already got an problem. I'm working a while with mysql currently
i'm trying to duplicate some entries in my tables.
The problem is, i'm using foreign key constraints between those tables
and i also would like to duplicate the childs.
So i got one big parent, these parent table has some child tables. All
are contatenatet via the FK constraints.
Currently i'm extracting the references by foot. So i do a SHOW TABLE
STATUS FROM akron LIKE '$tablename'.
After that i explode the information an so on. Very dirty stuff, a lot
of code.
And now after all the hard work mysql throws me this when i try to
insert a copy of a parent: Cannot add or update a child row: a foreign
key constraint fails
The childs do exist, but i think my sql syntax is wrong i use: INSERT
INTO $non_atomar[$i] VALUES ('',$values)
The key values are right, but i think sql need another syntax to be
happy.
The other thing is, i can solve my problem in this dirty way,
but i hope
that there is an easyer way to do this.
Did anyone know an easy way to copy a parent table and all
his childs ?
The only thing that changes are the keys.
Kind regards
Fabian Eidner
E [EMAIL PROTECTED]
B-O-S website http://www.brotherhood-of-steel.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Copy some datasets including the foreign key constraints
Hello list,
I'm pretty new here.
But i already got an problem. I'm working a while with mysql currently
i'm trying to duplicate some entries in my tables.
The problem is, i'm using foreign key constraints between those tables
and i also would like to duplicate the childs.
So i got one big parent, these parent table has some child tables. All
are contatenatet via the FK constraints.
Currently i'm extracting the references by foot. So i do a SHOW TABLE
STATUS FROM akron LIKE '$tablename'.
After that i explode the information an so on. Very dirty stuff, a lot
of code.
And now after all the hard work mysql throws me this when i try to
insert a copy of a parent: Cannot add or update a child row: a foreign
key constraint fails
The childs do exist, but i think my sql syntax is wrong i use: INSERT
INTO $non_atomar[$i] VALUES ('',$values)
The key values are right, but i think sql need another syntax to be
happy.
The other thing is, i can solve my problem in this dirty way, but i hope
that there is an easyer way to do this.
Did anyone know an easy way to copy a parent table and all his childs ?
The only thing that changes are the keys.
Kind regards
Fabian Eidner
E [EMAIL PROTECTED]
B-O-S website http://www.brotherhood-of-steel.org
RE: Copy some datasets including the foreign key constraints
Do you know what your query looks like after variable substitution? That
always helps me a lot. If you can't dump it to the screen because it breaks
too much of your system, look into syslog(). That's what I use when
debugging the heart of our online system, and it has helped me find missing
single quotes and the like.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Eidner, Fabian [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 22, 2006 7:46 AM
To: mysql@lists.mysql.com
Subject: Copy some datasets including the foreign key constraints
Hello list,
I'm pretty new here.
But i already got an problem. I'm working a while with mysql currently
i'm trying to duplicate some entries in my tables.
The problem is, i'm using foreign key constraints between those tables
and i also would like to duplicate the childs.
So i got one big parent, these parent table has some child tables. All
are contatenatet via the FK constraints.
Currently i'm extracting the references by foot. So i do a SHOW TABLE
STATUS FROM akron LIKE '$tablename'.
After that i explode the information an so on. Very dirty stuff, a lot
of code.
And now after all the hard work mysql throws me this when i try to
insert a copy of a parent: Cannot add or update a child row: a foreign
key constraint fails
The childs do exist, but i think my sql syntax is wrong i use: INSERT
INTO $non_atomar[$i] VALUES ('',$values)
The key values are right, but i think sql need another syntax to be
happy.
The other thing is, i can solve my problem in this dirty way,
but i hope
that there is an easyer way to do this.
Did anyone know an easy way to copy a parent table and all
his childs ?
The only thing that changes are the keys.
Kind regards
Fabian Eidner
E [EMAIL PROTECTED]
B-O-S website http://www.brotherhood-of-steel.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't Create Foreign Key Constraints
I'm trying to create foreign key constraints and keep getting an error message 1005 (I did look it up, but didn't see an obvious solution to fixing this for my database). The version I'm using is 5.0.17-max. I used Mysql WorkBench to create the database schema and had it generate the sql script. I created a table as such: CREATE TABLE `ows`.`FilmsRatings` ( `RatingID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `Rating` VARCHAR(50) NULL, PRIMARY KEY (`RatingID`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; Then, I created another table as such: CREATE TABLE `ows`.`Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL, `PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) NULL, `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`) REFERENCES `ows`.`FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; And got this following error message: ERROR 1072 (42000): Key column '(not null)' doesn't exist in table I tried again, this time deleting the CONSTRAINTs details. Then I tried to alter the table as such: mysql alter table films - add constraint fk_films_ratings - foreign key (RatingID) references FilmsRatings (RatingID); Which produced this error message: ERROR 1005 (HY000): Can't create table './ows/#sql-a8_11.frm' (errno: 150) mysql drop table films; This database is being run with InnoDB engine, so I should be able to create the foreign key constraints. So why is this happening? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Create Foreign Key Constraints
Lola J. Lee Beno wrote: I'm trying to create foreign key constraints and keep getting an error message 1005 (I did look it up, but didn't see an obvious solution to fixing this for my database). The version I'm using is 5.0.17-max. I used Mysql WorkBench to create the database schema and had it generate the sql script. I created a table as such: CREATE TABLE `ows`.`FilmsRatings` ( `RatingID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `Rating` VARCHAR(50) NULL, PRIMARY KEY (`RatingID`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; Then, I created another table as such: CREATE TABLE `ows`.`Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL, `PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) NULL, `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`) REFERENCES `ows`.`FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; And got this following error message: ERROR 1072 (42000): Key column '(not null)' doesn't exist in table I tried again, this time deleting the CONSTRAINTs details. Then I tried to alter the table as such: mysql alter table films - add constraint fk_films_ratings - foreign key (RatingID) references FilmsRatings (RatingID); Which produced this error message: ERROR 1005 (HY000): Can't create table './ows/#sql-a8_11.frm' (errno: 150) mysql drop table films; This database is being run with InnoDB engine, so I should be able to create the foreign key constraints. So why is this happening? 1) I'm not sure what you are intending with (`(not null)`) in the middle of your foreign key definition, but that isn't valid mysql syntax. See the manual for the correct syntax http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html. 2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films it is an INT. The manual says Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. 3) Again quoting the manual, You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Create Foreign Key Constraints
Michael Stassen wrote: 1) I'm not sure what you are intending with (`(not null)`) in the middle of your foreign key definition, but that isn't valid mysql syntax. See the manual for the correct syntax http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html. This is from the script that was generated using Mysql Workbench, 1.0.3-alpha. I tried it with (null) and (not null); neither worked. 2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films it is an INT. The manual says I then modified the query as such: CREATE TABLE `ows`.`Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL, `PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) UNSIGNED NULL, `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`) REFERENCES `ows`.`FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; No dice. 3) Again quoting the manual, You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server. Which gives me: LATEST FOREIGN KEY ERROR 060207 8:33:49 Error in foreign key constraint of table ows/#sql-a8_11: foreign key (RatingID) references FilmsRatings (RatingID): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html for correct foreign key definition. Which leads me back to the same URL that you gave me. so, it looks like I should create an index for FilmsRatings first, and then create the table Films - is that correct? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Create Foreign Key Constraints
Lola, And got this following error message: ERROR 1072 (42000): Key column '(not null)' doesn't exist in table Yes, it's telling you what;s wrong: to define a constraint on a key, the table def must first define the key. PB - Lola J. Lee Beno wrote: I'm trying to create foreign key constraints and keep getting an error message 1005 (I did look it up, but didn't see an obvious solution to fixing this for my database). The version I'm using is 5.0.17-max. I used Mysql WorkBench to create the database schema and had it generate the sql script. I created a table as such: CREATE TABLE `ows`.`FilmsRatings` ( `RatingID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `Rating` VARCHAR(50) NULL, PRIMARY KEY (`RatingID`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; Then, I created another table as such: CREATE TABLE `ows`.`Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL, `PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) NULL, `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`) REFERENCES `ows`.`FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; And got this following error message: ERROR 1072 (42000): Key column '(not null)' doesn't exist in table I tried again, this time deleting the CONSTRAINTs details. Then I tried to alter the table as such: mysql alter table films - add constraint fk_films_ratings - foreign key (RatingID) references FilmsRatings (RatingID); Which produced this error message: ERROR 1005 (HY000): Can't create table './ows/#sql-a8_11.frm' (errno: 150) mysql drop table films; This database is being run with InnoDB engine, so I should be able to create the foreign key constraints. So why is this happening? No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.2/251 - Release Date: 2/4/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Create Foreign Key Constraints
Peter Brawley wrote: Lola, /And got this following error message: ERROR 1072 (42000): Key column '(not null)' doesn't exist in table / Yes, it's telling you what;s wrong: to define a constraint on a key, the table def must first define the key. Looking over the script again, RatingID is being defined, first in FilmsRatings which I ran to create this table and then in Films, the create table script which didn't work. How do I make the table def first define the key explicitly? Or am I missing something obvious? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Create Foreign Key Constraints
Hello. The query which is works is: CREATE TABLE `Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL ,`PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) unsigned , `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (RatingID) REFERENCES `FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; See: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Lola J. Lee Beno wrote: Michael Stassen wrote: 1) I'm not sure what you are intending with (`(not null)`) in the middle of your foreign key definition, but that isn't valid mysql syntax. See the manual for the correct syntax http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html. This is from the script that was generated using Mysql Workbench, 1.0.3-alpha. I tried it with (null) and (not null); neither worked. 2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films it is an INT. The manual says I then modified the query as such: CREATE TABLE `ows`.`Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL, `PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) UNSIGNED NULL, `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`) REFERENCES `ows`.`FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; No dice. 3) Again quoting the manual, You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server. Which gives me: LATEST FOREIGN KEY ERROR 060207 8:33:49 Error in foreign key constraint of table ows/#sql-a8_11: foreign key (RatingID) references FilmsRatings (RatingID): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html for correct foreign key definition. Which leads me back to the same URL that you gave me. so, it looks like I should create an index for FilmsRatings first, and then create the table Films - is that correct? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Create Foreign Key Constraints
Gleb Paharenko wrote: Hello. The query which is works is: Thanks - just what I needed. Looks like I'll need to be extra careful with sql scripts generated from Mysql Workbench, which is still alpha right now. -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't Create Foreign Key Constraints
Hello. You can post a bug: http://dev.mysql.com/doc/refman/5.0/en/bug-reports.html Lola J. Lee Beno wrote: Gleb Paharenko wrote: Hello. The query which is works is: Thanks - just what I needed. Looks like I'll need to be extra careful with sql scripts generated from Mysql Workbench, which is still alpha right now. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM and foreign key constraints?
Hi, MySQL docs claim at: http://dev.mysql.com/doc/mysql/en/ansi-diff-foreign-keys.html that At a later stage, foreign key constraints will be implemented for MyISAM tables as well. Does anyone know what the timescale is? Thanks, Jacek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
change a column type and innodb foreign key constraints
Hi, I have a column 'id' within a table : CREATE TABLE `reference` ( *`*id*`* smallint(5) unsigned NOT NULL auto_increment, `study_name` text, `author` text NOT NULL, `date` date NOT NULL default '-00-00', `reference` varchar(250) NOT NULL default '', `title` varchar(250) NOT NULL default '', `pmid` int(15) default NULL, `project` varchar(35) default NULL, `abstract` text, `datasource` smallint(5) unsigned default NULL, PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB that i need to change from smallint to int There are a number of foreign key constraints from other columns in other tables on this column: eg CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `num_peds` int(7) unsigned default NULL, `affected` int(7) unsigned default NULL, `unaffected` int(7) unsigned default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_178` FOREIGN KEY (`gene_ID`) REFERENCES `gene` (`id`), CONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE ) TYPE=InnoDB Upon trying to modify the id columns in the reference table, i'm getting the following mysql alter table reference modify id int; ERROR 1025: Error on rename of './nugenob/#sql-2ed1_e001' to './nugenob/reference' (errno: 150) mysql It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? cheers Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a column type and innodb foreign key constraints
It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? That is the way ! :) You need to drop the constrains... Alter `reference` and make `id` INT You will need to alter the `monogenic` table as well... making it's `id` INT also... Recreate constrains... Though... I do not know if you need to drop ALL constrains or only the one that ties the tables: thisCONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE/this Gabriel PREDA www.amr.ro www.lgassociations.info www.falr.ro dev.falr.ro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: change a column type and innodb foreign key constraints
Gabriel PREDA [EMAIL PROTECTED] wrote on 03/16/2005 06:12:14 AM: It looks from googling as though I need to drop all foreign key constraints on this column, perform the change and then reestablish the foreign keys. Could anyone confirm or advise of a better solution? That is the way ! :) You need to drop the constrains... Alter `reference` and make `id` INT You will need to alter the `monogenic` table as well... making it's `id` INT also... Recreate constrains... Though... I do not know if you need to drop ALL constrains or only the one that ties the tables: thisCONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE/this Gabriel PREDA www.amr.ro www.lgassociations.info www.falr.ro dev.falr.ro Yes, Gabriel is right. You do not have to remove ALL of your foreign constraints, only those that include the column you need to change. Imagine the situation that would occur if you had been able to successfully change the definition of the ID column and you didn't un-define your foreign keys or change any of your other referencing columns. You would have had a relationship that was trying to enforce equality between an int on one side and a smallint on the other. Clearly that would be an illegal FK constraint, right? That's why you were not permitted to change the column as it would have made your existing foreign keys illegal. Follow Gabriel's advice, drop the FKs that reference the column you want to change, modify the fields on BOTH ends of your FKs to have matching datatypes, then re-establish your FKs. It may be a little work but that's just part of the job. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: INSERT IGNORE like feature for rows failing foreign key constraints?
I never got a reply for this, and I'm still trying to figure out the best way to handle it. Anyone? John A. McCaskey -Original Message- From: John McCaskey [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 25, 2004 2:17 PM To: [EMAIL PROTECTED] Subject: INSERT IGNORE like feature for rows failing foreign key constraints? I have a logging table where I insert a large number of rows every 5 minutes. For performance reasons this occurs in bulk inserts of about 5000 rows at a time. (ie. INSERT INTO table VALUES(...), (...), (...)) One of the fields in the table is an id that connects it to another table. It is possible that by the time the insert occurs (they queue up in memory briefly before I create the bulk insert) a separate process has deleted the entry in the parent table and the id is invalid. When this happens right now the entire insert of 5000 rows fails because one single row is bad. I want the behavior to be that the one fails silently and the other 4999 insert successfully. Any ideas how I can do this? It seems like INSERT IGNORE would make sense but that appears to only ignore duplicates not foreign key failures. John A. McCaskey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data loading and foreign key constraints - help please
Thank you very much. I really appreciate your analogy to the waterfall. This helped me out tremendously. I was able to sort out the problem and all is now well! It appears that this wonderful little GUI tool the lets you create ER diagrams that auto-generate CREATE scripts assumes that you won't be including foreign keys in your entities. It expects you to build the relationship graphically and point out the primary keys, but it takes care of creating the foreign keys for you. My mistake. Since I put in foreign keys with the same name as the primary key in the related table, the GUI tool had no choice but to create new foreign keys with the same name appended with the number 1. The end result... total chaos. Fixed it though. I really appreciate your help. Todd On Aug 30, 2004, at 6:20 AM, [EMAIL PROTECTED] wrote: Foreign keys are used to enforce foreign relationships. Translated: Certain data values must exist in one table before another table can contain those values in columns that participate in foreign keys. Because data must first exist in one table before it can be used as data in another, you are required to fill in your FK-related structures from the top down. Start with your top-most table(s) in your structure (these are the ones that the foreign keys are referencing but have no foreign keys of their own). I think you said that you called them joblevel and jobtitile. Fill those tables with data. With those values in place you can create rows in the jobcode table that re-use certain values. You will not be able to assign a value to any row in jobcode that does not exist in either joblevel or jobtitle (for the columns that reference those tables as foreign keys). Keep filling in values in each layer of your structure until you get to the bottommost table(s). (These are the tables that FK reference other tables but have no tables that reference them.) It's kind of like a waterfall, you can't get data into some tables until it exists in other tables so it's like the data sort of trickles down the structure. (This analogy could also help to visualize how the use of the word cascade describes the auto-propagation of a delete or update to the dependent tables) HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Todd Cranston-Cuebas [EMAIL PROTECTED] wrote on 08/29/2004 04:09:15 AM: I'm a total newbie to mySQL, but was hoping someone could answer a question regarding adding a record into a database that has foreign key constraints. Remember, I'm a total newbie so I'm hoping I'm using the right words to express this. I'm taking a class that required us to use an ER diagramming tool. This tool generates SQL table create scripts in mySQL. After a little tweaking I got the scripts to work. An example is as follows: # Create Table: 'Jobdesc' Job Description for Requisition # desccode: # jobdescription : # levelcode : (references JobCode.levelcode) # jobcode1: (references JobCode.jobcode) # jobcode : # titlecode : (references JobCode.titlecode) # CREATE TABLE Jobdesc ( desccode CHAR(8) NOT NULL UNIQUE, jobdescription MEDIUMTEXT NOT NULL, levelcode CHAR(2) NOT NULL, jobcode1 CHAR(8) NOT NULL, jobcodeCHAR(8) NOT NULL, titlecode CHAR(7) NOT NULL, PRIMARY KEY (desccode,jobcode), INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode), CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode) REFERENCES JobCode (titlecode,jobcode,levelcode) ON DELETE CASCADE ON UPDATE CASCADE) TYPE=INNODB; This is a create script for a job description table. Job descriptions are related to a jobcode table. That table in turn is related to joblevel and jobtitle tables (i.e., the job title and job level determine the job code). The jobcode is needed for each job description. One problem I have is that the create scripts generated from the ER tool makes all fields in the job description entity NOT NULL. If I try to insert the description code (desccode), the job description (jobdescription) and the associated job code (jobcode) I get the following error: #1216 - Cannot add or update a child row: a foreign key constraint fails This happens if I just try to insert the desccode, the jobdescription, and jobcode data. I think this is happening because jobcode1, levelcode, and titlecode are NOT NULL so when I update the record it attempts to enter data (NULL) into these fields which are child rows. Can someone explain what I should do? Should I just change these fields of data into NULL? I'm literally just trying to populate the tables with enough data to run some test queries. Any suggestions? Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
Data loading and foreign key constraints - help please
I'm a total newbie to mySQL, but was hoping someone could answer a question regarding adding a record into a database that has foreign key constraints. Remember, I'm a total newbie so I'm hoping I'm using the right words to express this. I'm taking a class that required us to use an ER diagramming tool. This tool generates SQL table create scripts in mySQL. After a little tweaking I got the scripts to work. An example is as follows: # Create Table: 'Jobdesc' Job Description for Requisition # desccode: # jobdescription : # levelcode : (references JobCode.levelcode) # jobcode1: (references JobCode.jobcode) # jobcode : # titlecode : (references JobCode.titlecode) # CREATE TABLE Jobdesc ( desccode CHAR(8) NOT NULL UNIQUE, jobdescription MEDIUMTEXT NOT NULL, levelcode CHAR(2) NOT NULL, jobcode1 CHAR(8) NOT NULL, jobcodeCHAR(8) NOT NULL, titlecode CHAR(7) NOT NULL, PRIMARY KEY (desccode,jobcode), INDEX idx_fk_Jobdesc (titlecode,jobcode1,levelcode), CONSTRAINT fk_Jobdesc FOREIGN KEY (titlecode,jobcode1,levelcode) REFERENCES JobCode (titlecode,jobcode,levelcode) ON DELETE CASCADE ON UPDATE CASCADE) TYPE=INNODB; This is a create script for a job description table. Job descriptions are related to a jobcode table. That table in turn is related to joblevel and jobtitle tables (i.e., the job title and job level determine the job code). The jobcode is needed for each job description. One problem I have is that the create scripts generated from the ER tool makes all fields in the job description entity NOT NULL. If I try to insert the description code (desccode), the job description (jobdescription) and the associated job code (jobcode) I get the following error: #1216 - Cannot add or update a child row: a foreign key constraint fails This happens if I just try to insert the desccode, the jobdescription, and jobcode data. I think this is happening because jobcode1, levelcode, and titlecode are NOT NULL so when I update the record it attempts to enter data (NULL) into these fields which are child rows. Can someone explain what I should do? Should I just change these fields of data into NULL? I'm literally just trying to populate the tables with enough data to run some test queries. Any suggestions? Todd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT IGNORE like feature for rows failing foreign key constraints?
I have a logging table where I insert a large number of rows every 5 minutes. For performance reasons this occurs in bulk inserts of about 5000 rows at a time. (ie. INSERT INTO table VALUES(...), (...), (...)) One of the fields in the table is an id that connects it to another table. It is possible that by the time the insert occurs (they queue up in memory briefly before I create the bulk insert) a separate process has deleted the entry in the parent table and the id is invalid. When this happens right now the entire insert of 5000 rows fails because one single row is bad. I want the behavior to be that the one fails silently and the other 4999 insert successfully. Any ideas how I can do this? It seems like INSERT IGNORE would make sense but that appears to only ignore duplicates not foreign key failures. John A. McCaskey Software Development Engineer IP Sciences, Inc. [EMAIL PROTECTED] 206.902.2027
foreign key constraints
Hi everyone, I'd like to learn more about foreign key constraints... I read this section of the manual... http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html Where can I find a more detailed description with some examples? Thanks, Sergei
Re: foreign key constraints
Sergei, Check out Paul DuBois' book MySQL - The Definitive Guide (2nd edition). It has a few good chapters that discuss foreign key constraints. --bmansell Brian E. Mansell MySQL Professional On Thu, 22 Jul 2004 13:06:07 -0700, Sergei Skarupo [EMAIL PROTECTED] wrote: Hi everyone, I'd like to learn more about foreign key constraints... I read this section of the manual... http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html Where can I find a more detailed description with some examples? Thanks, Sergei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB foreign key constraints
Hello, Are there any plans to implement foreign keys with deferred integrity constraint checking in the InnoDB storage engine in a future release of the MySQL Server? Many thanks, - glenn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, ok - I've checked. Why not? What's wrong with this: BORROWER BorrowerID BOOKS BookID BorrowerID (nullable) FK from Books.BorrowerID to Borrower.BorrowerID I haven't checked, but this _should_ be possible. With regards, Its a foreign key, you can not null foreign keys.. Thats the problem. That's not true. Here's what I got: CREATE TABLE inno1 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY ( PK_Col ) ) TYPE=InnoDB ROW_FORMAT=fixed DEFAULT; CREATE INDEX I_Inno1_ChildCol ON inno1(Child_Col); ALTER TABLE inno1 ADD FOREIGN KEY (Child_Col) REFERENCES inno1 (PK_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; After that, I inserted data: INSERT INTO inno1(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (3, 1); Now, can someone explain what the problem with NULLable FKs is? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 28 May 2004 02:57 am, Martijn Tonies wrote: Hi, ok - I've checked. Why not? What's wrong with this: BORROWER BorrowerID BOOKS BookID BorrowerID (nullable) FK from Books.BorrowerID to Borrower.BorrowerID I haven't checked, but this _should_ be possible. With regards, Its a foreign key, you can not null foreign keys.. Thats the problem. That's not true. Here's what I got: CREATE TABLE inno1 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY ( PK_Col ) ) TYPE=InnoDB ROW_FORMAT=fixed DEFAULT; CREATE INDEX I_Inno1_ChildCol ON inno1(Child_Col); ALTER TABLE inno1 ADD FOREIGN KEY (Child_Col) REFERENCES inno1 (PK_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; After that, I inserted data: INSERT INTO inno1(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (3, 1); Now, can someone explain what the problem with NULLable FKs is? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com CREATE TABLE inno2 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno2_ChildCol ON inno2(Child_Col); CREATE TABLE inno3 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col) REFERENCES inno2 (Child_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); select * from inno2; The actual way he was doing it was above.. I am going to have look into this more since as you can see, this worked and considering I do not have a id 2 or 3.. it should have failed.. so something isn't right.. The entire point behind foreign keys is for constraints.. Its been awhile since I have done foreign keys on mysql... - -- Enough research will tend to support your theory. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAt1Ruld4MRA3gEwYRAvWEAKCcCqIsKLIPZk3od7Vn8z3rA9zAbACfZYhL 4VQLUYacl2HR9rmaBZC/pvw= =yiUm -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi Jeff, ok - I've checked. Why not? What's wrong with this: BORROWER BorrowerID BOOKS BookID BorrowerID (nullable) FK from Books.BorrowerID to Borrower.BorrowerID I haven't checked, but this _should_ be possible. With regards, Its a foreign key, you can not null foreign keys.. Thats the problem. That's not true. Here's what I got: CREATE TABLE inno1 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY ( PK_Col ) ) TYPE=InnoDB ROW_FORMAT=fixed DEFAULT; CREATE INDEX I_Inno1_ChildCol ON inno1(Child_Col); ALTER TABLE inno1 ADD FOREIGN KEY (Child_Col) REFERENCES inno1 (PK_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; After that, I inserted data: INSERT INTO inno1(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno1(PK_Col, Child_Col) VALUES (3, 1); Now, can someone explain what the problem with NULLable FKs is? CREATE TABLE inno2 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno2_ChildCol ON inno2(Child_Col); CREATE TABLE inno3 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col) REFERENCES inno2 (Child_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); select * from inno2; The actual way he was doing it was above.. I am going to have look into this more since as you can see, this worked and considering I do not have a id 2 or 3.. it should have failed.. so something isn't right.. The entire point behind foreign keys is for constraints.. Its been awhile since I have done foreign keys on mysql... Indeed, inserting (2, NULL) fails - while it shouldn't. A FK should only be enforced if there's a value. Not when it's NULL (for the FK columns). This is true for all other database engines that I know. How else can you create tables with either a relationship to another table or no relationship? IMO, all INSERTs you wrote should succeed. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Martijn Tonies wrote: Hi Jeff, snip CREATE TABLE inno2 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno2_ChildCol ON inno2(Child_Col); CREATE TABLE inno3 ( PK_Col Integer NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; ALTER TABLE inno3 ADD FOREIGN KEY (Pk_Col) REFERENCES inno2 (Child_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); select * from inno2; The actual way he was doing it was above.. I am going to have look into this more since as you can see, this worked and considering I do not have a id 2 or 3.. it should have failed.. so something isn't right.. The entire point behind foreign keys is for constraints.. Its been awhile since I have done foreign keys on mysql... Indeed, inserting (2, NULL) fails - while it shouldn't. A FK should only be enforced if there's a value. Not when it's NULL (for the FK columns). This is true for all other database engines that I know. How else can you create tables with either a relationship to another table or no relationship? IMO, all INSERTs you wrote should succeed. With regards, Martijn Tonies In this example, inno3.PK_Col references inno2.Child_Col, so the 2nd and 3rd statements are failing because they try to set inno3.PK_Col to values not present in inno2.Child_Col. The NULLs are irrelevant. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Michael Stassen wrote: Martijn Tonies wrote: Hi Jeff, snip In this example, inno3.PK_Col references inno2.Child_Col, so the 2nd and 3rd statements are failing because they try to set inno3.PK_Col to values not present in inno2.Child_Col. The NULLs are irrelevant. Michael Perhaps this is what you meant? CREATE TABLE inno2 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno2_ChildCol ON inno2(Child_Col); CREATE TABLE inno3 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno3_ChildCol ON inno3(Child_Col); ALTER TABLE inno3 ADD FOREIGN KEY (Child_Col) REFERENCES inno2 (Child_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); mysql SELECT * FROM inno3; ++---+ | PK_Col | Child_Col | ++---+ | 1 | NULL | | 2 | NULL | | 3 | NULL | ++---+ 3 rows in set (0.05 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, Martijn Tonies wrote: Hi Jeff, snip In this example, inno3.PK_Col references inno2.Child_Col, so the 2nd and 3rd statements are failing because they try to set inno3.PK_Col to values not present in inno2.Child_Col. The NULLs are irrelevant. Woops, right Michael - got that one wrong. Michael Perhaps this is what you meant? CREATE TABLE inno2 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno2_ChildCol ON inno2(Child_Col); CREATE TABLE inno3 ( PK_ColInteger NOT NULL DEFAULT 0, Child_Col Integer, PRIMARY KEY (PK_Col) ) TYPE=InnoDB ; CREATE INDEX I_Inno3_ChildCol ON inno3(Child_Col); ALTER TABLE inno3 ADD FOREIGN KEY (Child_Col) REFERENCES inno2 (Child_Col) ON DELETE NO ACTION ON UPDATE NO ACTION; INSERT INTO inno2(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno2(PK_Col, Child_Col) VALUES (3, 1); INSERT INTO inno3(PK_Col, Child_Col) VALUES (1, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (2, NULL); INSERT INTO inno3(PK_Col, Child_Col) VALUES (3, NULL); mysql SELECT * FROM inno3; ++---+ | PK_Col | Child_Col | ++---+ | 1 | NULL | | 2 | NULL | | 3 | NULL | ++---+ 3 rows in set (0.05 sec) This makes perfectly sense. So, once again I dare to ask: what's the problem with NULLable Foreign Keys? It works fine :-) (now, who was it that said that FKs should be entered/exist always?) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 28 May 2004 11:50 am, Martijn Tonies wrote: This makes perfectly sense. So, once again I dare to ask: what's the problem with NULLable Foreign Keys? It works fine :-) (now, who was it that said that FKs should be entered/exist always?) With regards, Here is the issue... If you go back to what he was doing this insert doesn't work.. INSERT INTO inno3(PK_Col, Child_Col) VALUES (NULL,1) He was trying to use a Primary key as his foreign key to inno2, (in this example of what he was doing). You can't have a null PK as we all know. IT wasn't the foreign key part that was hanging him up.. I think we were on two different tracks and that could have been my fault. Jeff - -- Give me Liberty or give me... well, whatever you think is best for society. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAt28Ald4MRA3gEwYRAk5/AKCId5Bbpgdh6brzxC8H8E70k8tLrgCfUR8N 44Oq/W0lvwdt798hyi5F/6Q= =/8Iu -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 28 May 2004 11:50 am, Martijn Tonies wrote: This makes perfectly sense. So, once again I dare to ask: what's the problem with NULLable Foreign Keys? It works fine :-) (now, who was it that said that FKs should be entered/exist always?) He needs to send his create table commands and so forth.. He must of defined books.borrowid as some sort of PK or some kind of not nullable INDex/column... Thats my guess. - -- DM Advice: If they split up, giggle insanely. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAt2++ld4MRA3gEwYRAhUyAKDbS1i26zUwk0p6ifWvanGLO49TDQCg1cJl 7JYbD3T1c6Rp16Cvew2JEk4= =GJmF -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
This makes perfectly sense. So, once again I dare to ask: what's the problem with NULLable Foreign Keys? It works fine :-) (now, who was it that said that FKs should be entered/exist always?) With regards, Here is the issue... If you go back to what he was doing this insert doesn't work.. INSERT INTO inno3(PK_Col, Child_Col) VALUES (NULL,1) He was trying to use a Primary key as his foreign key to inno2, (in this example of what he was doing). You can't have a null PK as we all know. IT wasn't the foreign key part that was hanging him up.. aha, the PK could be a problem yes. PKs cannot be NULL. That must be it. I think we were on two different tracks and that could have been my fault. :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
[EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. If you have a foreign key constraint that requires the foreign key field to be populated then you effectively have a MANY TO ONE with Min of 1 and Max of 1 relationship between the book table and the borrower table -- thus you cannot have a book without a borrower. That is a logical problem, and the one you are describing. Separate the BorrowerID from table `book`. Make a table called, oh, `book_borrower` and put BookID BorrowerID DateOut DateDue DateReturned ... Then you can query for borrowed books using a join like: SELECT a.BookID, b.BorrowerID, c.FullName from ( book a inner join book_borrower b on a.BookID = b.BookID ) inner join borrower c on b.BorrowerID = c.BorrowerID WHERE b.DateReturned is null Available books could be found: SELECT a.BookID from book a left join book_borrower b on a.BookID=b.BookID WHERE b.BookID is null and b.DateReturned is null (Which says show me all the book.BookID that fail to have a non-returned book in the book_borrower table. Usually I put the and b.DateReturned is null with the join statement, instead of the WHERE clause.) Now you can have a book without a borrower and can easily track borrowing history for books and borrowers. Be sure to index the fields you'll be using as selection criteria! HTH, Robert J Taylor [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
[EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. I think you have missed the point of a foreign key constraint. It is to ensure referential integrity. A constraint stops you doing things, ie entering a value that does not exist in another table. Or create a borrower called NO-ONE and always change the borrower to this when a book is returned. Or just use an outer join on an indexed field when doing reports is easiest and no constraint. Colin Bull -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, On Wednesday 26 May 2004 11:22 pm, [EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. I am not to sure how its possible to fix it.. Thinking about this.. I would have a bookid which is never null. Considering no matter if the book is out or not, you have that book. I then would have a borrow table, lets say, as the foreign key would be bookid.. I date borrowed, and date returned would be how I would know if its out or not.. Just thinking off the top of my head as there probably is a better way to do it. I am sure there are other ways to do this.. Foreign keys can't be null. Why not? What's wrong with this: BORROWER BorrowerID BOOKS BookID BorrowerID (nullable) FK from Books.BorrowerID to Borrower.BorrowerID I haven't checked, but this _should_ be possible. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
You could create a special borrower account to signify that it is not loaned out and assign that to the book. If this is for a library system (multiple branches) you could create one account for each branch. That way you would know where the book is at all times, borrowed or not ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] m To: [EMAIL PROTECTED] cc: 05/27/2004 12:22 Fax to: AM Subject: Foreign Key Constraints Please respond to kyuubi Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. Msg sent via Spymac Mail - http://www.spymac.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 27 May 2004 03:00 am, Martijn Tonies wrote: Why not? What's wrong with this: BORROWER BorrowerID BOOKS BookID BorrowerID (nullable) FK from Books.BorrowerID to Borrower.BorrowerID I haven't checked, but this _should_ be possible. With regards, Its a foreign key, you can not null foreign keys.. Thats the problem. - -- Practice safe sin. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAtfoTld4MRA3gEwYRApVcAJ9RNWF9qZrresicBlct3TP2fpEL1wCg3xIx VER2P5MGzLgUqLRc7JZLFys= =qVNw -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
Hi, Why not? What's wrong with this: BORROWER BorrowerID BOOKS BookID BorrowerID (nullable) FK from Books.BorrowerID to Borrower.BorrowerID I haven't checked, but this _should_ be possible. With regards, Its a foreign key, you can not null foreign keys.. Thats the problem. If this really is the case with MySQL, then this is the only database engine that I know that doesn't allow this. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Constraints
Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. Msg sent via Spymac Mail - http://www.spymac.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Constraints
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wednesday 26 May 2004 11:22 pm, [EMAIL PROTECTED] wrote: Hi, I am trying to use the foreign key constraints from InnoDB and creating indexes is a requirement for foreign key. The problem is that by creating index for my foreign key, it does not allow my foreign key to have null or blank values which my records will have. For eg. a BorrowerID is a foreign key on a Book table, but when the book is not borrowed, the BorrowerID will be null and I can't seem to import the data containing null values for the foreign key. Is there a way to solve this? Thanks. I am not to sure how its possible to fix it.. Thinking about this.. I would have a bookid which is never null. Considering no matter if the book is out or not, you have that book. I then would have a borrow table, lets say, as the foreign key would be bookid.. I date borrowed, and date returned would be how I would know if its out or not.. Just thinking off the top of my head as there probably is a better way to do it. I am sure there are other ways to do this.. Foreign keys can't be null. Jeff - -- Calm down--It's only ones and zeroes. -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQFAtW9lld4MRA3gEwYRAkaAAKCtA0+GlFLRifkWDdH661LIF7gaVwCdHM8V zxbQk96yn0v3dTTBD9F4gWM= =JLfz -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
foreign key constraints are driving me crazy!
So I have made a table called 'uid' where on uid is the only field in the table. Then I make another table called 'users' where there are two fields, 'uid1' and 'uid2' and both uid values have foreign key constraints referencing the 'uid' field in the uid table. No problem, tables are created and are all empty. Then I try inserting a uid varchar value of 'test' into the uid table... ok works, Then I try inserting a uid1 and uid2 varchar value of 'test' into the users table... I get a foreign key constraint error or 1216: #MysqlError: Cannot add or update a child row: a foreign key constraint fails 1216 Does anyone know what I am doing wrong? Thanks, -Cere -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key constraints are driving me crazy!
Some key in the table is null when it shouldnt be, or the type of the join keys isnt the same. i have had issues when importing data from a dump so i've had to do a FORIEGN_KEY_CHECKS=0 So I have made a table called 'uid' where on uid is the only field in the table. Then I make another table called 'users' where there are two fields, 'uid1' and 'uid2' and both uid values have foreign key constraints referencing the 'uid' field in the uid table. No problem, tables are created and are all empty. Then I try inserting a uid varchar value of 'test' into the uid table... ok works, Then I try inserting a uid1 and uid2 varchar value of 'test' into the users table... I get a foreign key constraint error or 1216: #MysqlError: Cannot add or update a child row: a foreign key constraint fails 1216 Does anyone know what I am doing wrong? Thanks, -Cere -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key constraints are driving me crazy!
In fact it seems to be just to opposite. I toggled the default values to be null and allowed for the null option and poof it worked! I don't really understand this very well. Wish I did. Some key in the table is null when it shouldnt be, or the type of the join keys isnt the same. i have had issues when importing data from a dump so i've had to do a FORIEGN_KEY_CHECKS=0 So I have made a table called 'uid' where on uid is the only field in the table. Then I make another table called 'users' where there are two fields, 'uid1' and 'uid2' and both uid values have foreign key constraints referencing the 'uid' field in the uid table. No problem, tables are created and are all empty. Then I try inserting a uid varchar value of 'test' into the uid table... ok works, Then I try inserting a uid1 and uid2 varchar value of 'test' into the users table... I get a foreign key constraint error or 1216: #MysqlError: Cannot add or update a child row: a foreign key constraint fails 1216 Does anyone know what I am doing wrong? Thanks, -Cere -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Cere Davis Unix Systems Administrator - CSDE [EMAIL PROTECTED] ph: 206.685.5346 https://staff.washington.edu/cere GnuPG Key http://staff.washington.edu/cere/gpgkey.txt Key fingerprint = B63C 2361 3B9B 8599 ECC9 D061 3E48 A832 F455 9E7FA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
foreign key constraints
Here is the script that creates the database.
create table object_attrib (obj_record_id INTEGER(7) AUTO_INCREMENT NOT
NULL,
PRIMARY KEY (obj_record_id),
INDEX (rel_obj_int_id),
FOREIGN KEY (rel_obj_int_id)
REFERENCES obj_id_internals (obj_int_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_os_id),
FOREIGN KEY (rel_os_id)
REFERENCES os_product (os_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_db_id),
FOREIGN KEY (rel_db_id)
REFERENCES db_product (db_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
rel_obj_int_id INTEGER(7) NOT NULL, rel_os_id INTEGER(7) NOT NULL,
rel_db_id INTEGER(7) NOT NULL, obj_path VARCHAR(255) NOT NULL,
obj_name VARCHAR(255) NOT NULL, obj_4n6_purpose BLOB NULL,
obj_info_source BLOB NULL, obj_comments BLOB NULL,
obj_md5_value VARCHAR(43) NOT NULL, obj_stat_dyn VARCHAR(8) NULL,
obj_byte_size VARCHAR(16) NOT NULL, obj_category VARCHAR(255) NULL,
obj_rec_last_update DATE NOT NULL, obj_rec_orgin DATE NOT NULL) TYPE =
INNODB;
Here are the lines of perl code that I am using.
open(INPUT,$filename) || die Check to see if $filename is a valid file.\n;
$sth = $dbh-prepare(q{INSERT INTO
object_attrib(obj_path,obj_name,obj_byte_size,obj_md5_value) VALUES
(?,?,?,?)}) || die $dbh-errstr;
while (INPUT)
{
chomp;
($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) = split /,/;
$sth-execute($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) || die
$dbh-errstr;
}
close INPUT;
$dbh-disconnect;
Can someone tell me why I get the following error:
Cannot add or update a child row: a foreign key constraint fails.
Thanks,
Brian Croniser
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key constraints
At 18:22 + 10/8/03, Croniser Brian Contr AFRL/IFGB wrote:
Here is the script that creates the database.
create table object_attrib (obj_record_id INTEGER(7) AUTO_INCREMENT NOT
NULL,
PRIMARY KEY (obj_record_id),
INDEX (rel_obj_int_id),
FOREIGN KEY (rel_obj_int_id)
REFERENCES obj_id_internals (obj_int_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_os_id),
FOREIGN KEY (rel_os_id)
REFERENCES os_product (os_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_db_id),
FOREIGN KEY (rel_db_id)
REFERENCES db_product (db_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
rel_obj_int_id INTEGER(7) NOT NULL, rel_os_id INTEGER(7) NOT NULL,
rel_db_id INTEGER(7) NOT NULL, obj_path VARCHAR(255) NOT NULL,
obj_name VARCHAR(255) NOT NULL, obj_4n6_purpose BLOB NULL,
obj_info_source BLOB NULL, obj_comments BLOB NULL,
obj_md5_value VARCHAR(43) NOT NULL, obj_stat_dyn VARCHAR(8) NULL,
obj_byte_size VARCHAR(16) NOT NULL, obj_category VARCHAR(255) NULL,
obj_rec_last_update DATE NOT NULL, obj_rec_orgin DATE NOT NULL) TYPE =
INNODB;
Here are the lines of perl code that I am using.
open(INPUT,$filename) || die Check to see if $filename is a valid file.\n;
$sth = $dbh-prepare(q{INSERT INTO
object_attrib(obj_path,obj_name,obj_byte_size,obj_md5_value) VALUES
(?,?,?,?)}) || die $dbh-errstr;
while (INPUT)
{
chomp;
($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) = split /,/;
$sth-execute($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) || die
$dbh-errstr;
}
close INPUT;
$dbh-disconnect;
Can someone tell me why I get the following error:
Cannot add or update a child row: a foreign key constraint fails.
Thanks,
Brian Croniser
You're not assigning an explicit value to any of your foreign
keys. Apparently the default value (0) is not present in one or
the other of the referenced keys in the parent table?
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key Constraints
We are trying to load a set of data using Hibernate (O/R mapping tool). Hibernate creates foreign key constraints when it is creating our DB (mysql 4.0.14). The process for loading data requires us to break these constraints at certain points, however they will all be satisfied at the end of the transaction. This is the perfect case for deferred FK constraints, however since mysql does not support deferred FK. I read where we could use Set foreign_key_checks=0 Do the data load Set foreign_key_checks=1 However when we tried to run this we still got a FK error message. Caused by: java.sql.SQLException: General error, message from server: Cannot delete or update a parent row: a foreign key constraint fails at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1628) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:886) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:945) at com.mysql.jdbc.Connection.execSQL(Connection.java:1809) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:16 02) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:14 88) at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(W rappedPreparedStatement.java:308) at net.sf.hibernate.persister.NormalizedEntityPersister.delete(NormalizedEn tityPersister.java:606) at net.sf.hibernate.impl.ScheduledDeletion.execute(ScheduledDeletion.java:2 2) at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2100) at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2066) ... 78 more Anybody know why the Set foreign_key_checks=0 might not be working? -John
Re: primary key/foreign key constraints with InnoDB
Thanks,
but I think the lik you provided won't help. I know how to create pk/fk
contraints, and do in our schema, when the foreign key is completely
specified. for example, if my original table was instead:
create table Example (
id int not null auto_increment primary key,
fk_id int not null
) type = InnoDB;
then I create an index in fk_id, and issue the alter table statement:
alter table Example add constraint foreign key (fk_id) references
Fk(id);
for an InnoDB table called Fk.
What I need to do is somehow put an if statement in there. If
table_name = 'TabA', then verify that TabA.id exists. If table_name =
'TabB', then verify that TabB.id exists. TabA and TabB, for the present
purposes, could simply be
create table TabA {
id int not null auto_increment primary key
) type = InnoDB;
create table TabB {
id int not null auto_increment primary key
) type = InnoDB;
Its as though I could do the following:
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
alter table Example add index (table_id);
alter table Example add constraint foreign key (table_id) references
(if table_name = 'TabA' then TabA(id) else TabB(id);
but I don't think this works.
jeff
Stefan Hinz wrote:
Jeff,
I'm wondering if its somehow possible to create a pk/fk constraint for
the table below
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
if table_name is 'TabA', then I want to make sure the row exists in
TabA. Likewise if table_name is 'TabB'
You can find the syntax for MySQL / InnoDB and a good example here:
http://www.mysql.com/doc/en/SEC463.html
To avoid trouble, consider this sentence from that page:
Both tables have to be InnoDB type and there must be an index where
the foreign key and the referenced key are listed as the FIRST
columns. InnoDB does not auto-create indexes on foreign keys or
referenced keys: you have to create them explicitly.
The example on that page, however, shows exactly how you'd do that.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
[filter fodder: sql, mysql, query]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key/foreign key constraints with InnoDB
Jeff,
We faced a similar challenge in an application: Each child record must have
a parent in one of two tables, TabA or TabB, but not both. We solved it
by adding a foreign-key field for each possible parent in the child
table. Each column can have the FK constraint. We were using Sybase, but
I translate the DDL to MySQL below.
create table Example (
id int not null auto_increment primary key,
tableA_id int not null,
tableB_id int not null
) type = InnoDB;
alter table Example add index (tableA_id);
alter table Example add index (tableB_id);
alter table Example add constraint foreign key (tableA_id) references TabA(id);
alter table Example add constraint foreign key (tableB_id) references TabB(id);
However, you will notice that each child record now must have a parent
record in BOTH parent tables. We used our front end to enforce a rule that
the one of the two foreign key fields is always -1 (or some other default
value). Then we insert a record into each parent with a key value that
matches our default (-1).
This method is not as easily extensible as your model, but perhaps that's
OK. In SQL to join the parent and child you must decide which parent to
join based on which FK column has the non-default value.
You might be able to come up with a DB rule to ensure that exactly one of
the FK values is non-default.
Stephe
At 09:26 AM 4/2/2003 -0700, Jeff Mathis wrote:
Thanks,
but I think the lik you provided won't help. I know how to create pk/fk
contraints, and do in our schema, when the foreign key is completely
specified. for example, if my original table was instead:
create table Example (
id int not null auto_increment primary key,
fk_id int not null
) type = InnoDB;
then I create an index in fk_id, and issue the alter table statement:
alter table Example add constraint foreign key (fk_id) references
Fk(id);
for an InnoDB table called Fk.
What I need to do is somehow put an if statement in there. If
table_name = 'TabA', then verify that TabA.id exists. If table_name =
'TabB', then verify that TabB.id exists. TabA and TabB, for the present
purposes, could simply be
create table TabA {
id int not null auto_increment primary key
) type = InnoDB;
create table TabB {
id int not null auto_increment primary key
) type = InnoDB;
Its as though I could do the following:
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
alter table Example add index (table_id);
alter table Example add constraint foreign key (table_id) references
(if table_name = 'TabA' then TabA(id) else TabB(id);
but I don't think this works.
jeff
Stefan Hinz wrote:
Jeff,
I'm wondering if its somehow possible to create a pk/fk constraint for
the table below
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
if table_name is 'TabA', then I want to make sure the row exists in
TabA. Likewise if table_name is 'TabB'
You can find the syntax for MySQL / InnoDB and a good example here:
http://www.mysql.com/doc/en/SEC463.html
To avoid trouble, consider this sentence from that page:
Both tables have to be InnoDB type and there must be an index where
the foreign key and the referenced key are listed as the FIRST
columns. InnoDB does not auto-create indexes on foreign keys or
referenced keys: you have to create them explicitly.
The example on that page, however, shows exactly how you'd do that.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
[filter fodder: sql, mysql, query]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key/foreign key constraints with InnoDB
thanks for the advice Stephen. I'll admit though I am somewhat loathe to
adding an artifical row in the other tables, but it may not be a bad way
to go. In the past, I've written triggers to do this kind of check, but
mysql doesn't yet support triggers.
what I ended up doing is carefully rethinking the schema. It turns out
we came up with a better design that does not require the table_name,
table_id linking mechanism. We just link into one table, which of course
presents no problems in creating a foreign key constraint.
jeff
Stephen Giese wrote:
Jeff,
We faced a similar challenge in an application: Each child record must have
a parent in one of two tables, TabA or TabB, but not both. We solved it
by adding a foreign-key field for each possible parent in the child
table. Each column can have the FK constraint. We were using Sybase, but
I translate the DDL to MySQL below.
create table Example (
id int not null auto_increment primary key,
tableA_id int not null,
tableB_id int not null
) type = InnoDB;
alter table Example add index (tableA_id);
alter table Example add index (tableB_id);
alter table Example add constraint foreign key (tableA_id) references TabA(id);
alter table Example add constraint foreign key (tableB_id) references TabB(id);
However, you will notice that each child record now must have a parent
record in BOTH parent tables. We used our front end to enforce a rule that
the one of the two foreign key fields is always -1 (or some other default
value). Then we insert a record into each parent with a key value that
matches our default (-1).
This method is not as easily extensible as your model, but perhaps that's
OK. In SQL to join the parent and child you must decide which parent to
join based on which FK column has the non-default value.
You might be able to come up with a DB rule to ensure that exactly one of
the FK values is non-default.
Stephe
At 09:26 AM 4/2/2003 -0700, Jeff Mathis wrote:
Thanks,
but I think the lik you provided won't help. I know how to create pk/fk
contraints, and do in our schema, when the foreign key is completely
specified. for example, if my original table was instead:
create table Example (
id int not null auto_increment primary key,
fk_id int not null
) type = InnoDB;
then I create an index in fk_id, and issue the alter table statement:
alter table Example add constraint foreign key (fk_id) references
Fk(id);
for an InnoDB table called Fk.
What I need to do is somehow put an if statement in there. If
table_name = 'TabA', then verify that TabA.id exists. If table_name =
'TabB', then verify that TabB.id exists. TabA and TabB, for the present
purposes, could simply be
create table TabA {
id int not null auto_increment primary key
) type = InnoDB;
create table TabB {
id int not null auto_increment primary key
) type = InnoDB;
Its as though I could do the following:
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
alter table Example add index (table_id);
alter table Example add constraint foreign key (table_id) references
(if table_name = 'TabA' then TabA(id) else TabB(id);
but I don't think this works.
jeff
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: primary key/foreign key constraints with InnoDB
Jeff,
I'm wondering if its somehow possible to create a pk/fk constraint for
the table below
create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
if table_name is 'TabA', then I want to make sure the row exists in
TabA. Likewise if table_name is 'TabB'
You can find the syntax for MySQL / InnoDB and a good example here:
http://www.mysql.com/doc/en/SEC463.html
To avoid trouble, consider this sentence from that page:
Both tables have to be InnoDB type and there must be an index where
the foreign key and the referenced key are listed as the FIRST
columns. InnoDB does not auto-create indexes on foreign keys or
referenced keys: you have to create them explicitly.
The example on that page, however, shows exactly how you'd do that.
Regards,
--
Stefan Hinz [EMAIL PROTECTED]
iConnect GmbH http://iConnect.de
Heesestr. 6, 12169 Berlin (Germany)
Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3
[filter fodder: sql, mysql, query]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Incorrect handling of foreign key constraints in InnoDB tables in MySQL 3.23.55-max run on Windows ME
Hi,
If a parent table has multicolumn primary key
AND child table have a constraint to one column of this parent
primary key (let's say column id_a)
AND there are more records in parent table having the same value
of id_a (let's say id_a==1)
AND child table has record with id_a==1
THANit is not possible to UPDATE or DELETE any rows of parent
table, which has id_a==1
I hope you can use the following example to repeat the problem and fix
this bug
Thank you
Jan Maznek
[EMAIL PROTECTED]
#
===
# Incorrect handling of foreign key constraints in InnoDB tables in
MySQL 3.23.55-max
# run on Windows ME
#
===
# EXAMPLE
#
# Table: zbozi_skupina # parent table
#
create table zbozi_skupina
(
id INTEGERdefault '1' not
null AUTO_INCREMENT,
valid_to INTEGERdefault
'2147166000' not null,
valid_from INTEGERdefault '1' not
null,
akce VARCHAR(64)default
'zbozi_vypis' not null,
constraint pk_zbozi_skupina primary key (id, valid_to)
)TYPE=InnoDB;
#
# Table: zbozi_skupina_strom# child table
#
create table zbozi_skupina_strom
(
id_parentINTEGERdefault '1' not
null,
id_child INTEGERdefault '1' not
null,
constraint pk_zbozi_skupina_strom primary key (id_parent, id_child)
)TYPE=InnoDB;
#
# Index: fk_skupina_zbozi_nadrizena
#
create index fk_skupina_zbozi_nadrizena on zbozi_skupina_strom
(id_parent asc);
#
# Index: fk_skupina_zbozi_podrizena
#
create index fk_skupina_zbozi_podrizena on zbozi_skupina_strom (id_child
asc);
alter table zbozi_skupina_strom
add constraint fk_zbozi_sk_sz_rodic_zbozi_sk foreign key (id_child)
references zbozi_skupina (id);
alter table zbozi_skupina_strom
add constraint fk_zbozi_sk_sz_dite_zbozi_sk foreign key (id_parent)
references zbozi_skupina (id);
INSERT INTO `zbozi_skupina` ( `id`, `valid_to`, `valid_from`,
`akce`)
VALUES ( '1', '2147166000', '1',
'zbozi_vypis');
INSERT INTO `zbozi_skupina` ( `id`, `valid_to`, `valid_from`,
`akce`)
VALUES ( '1', '1','1',
'zbozi_vypis');
# Correct:
# At this point it is possible to:
# DELETE FROM zbozi_skupina WHERE id=1 AND
valid_to='2147166000'
# AND/OR: DELETE FROM zbozi_skupina WHERE id=1 AND valid_to='1'
INSERT INTO `zbozi_skupina_strom` ( `id_parent` , `id_child`)
VALUES ('1', '1');
# INCORRECT:
!
# At this point it is NOT possible to:
# 1) DELETE FROM zbozi_skupina WHERE id=1 AND
valid_to='2147166000'
# 2) OR DELETE FROM zbozi_skupina WHERE id=1 AND valid_to='1'
# 3) OR UPDATE zbozi_skupina SET valid_to=2 WHERE id='1' AND
valid_to= '1'
# 4) OR UPDATE zbozi_skupina SET id='2' WHERE id='1' AND
valid_to='1'
#
# MySQL reports: Cannot delete a parent row: a foreign key
constraint fails
# It is not true!
# Constraints are defined only for column zbozi_skupina.id AND
# there are two rows having id=='1'.
#
# It should be possible to do anything to one of these two rows!
# (There would still be at least one row having id=='1')
#
# It should be possible to update zbozi_skupina.valid_to for BOTH
of these rows!
# (There is no impact on zbozi_skupina.id)
#
# I think that constraints in MySQL behave in this incorrect way:
# If there is a child table having constraint to the row which is
tried to be DELETEd/(UPDATEd primary key)
# MySQL returns error
#
# The correct behavior should be:
# If there is a child table having constraint to the row which is
tried to be DELETEd/(UPDATEd primary key)
# CHECK if there exists another row with the same part of primary
key
# OR CHECK if after UPDATE the constraint will be OK
# IF above checks fails, THAN report error
# I tried to solve this problem with adding indexes on every
single column
Re: Incorrect handling of foreign key constraints in InnoDB tables in MySQL 3.23.55-max run on Windows ME
Jan, this is a design deficiency which was known to me, but was not documented. I will remove it in some version 4.0.xx. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, March 13, 2003 11:13 AM Subject: Incorrect handling of foreign key constraints in InnoDB tables in MySQL 3.23.55-max run on Windows ME Hi, If a parent table has multicolumn primary key AND child table have a constraint to one column of this parent primary key (let's say column id_a) AND there are more records in parent table having the same value of id_a (let's say id_a=3D=3D1) AND child table has record with id_a=3D=3D1 THAN it is not possible to UPDATE or DELETE any rows of parent table, which has id_a=3D=3D1 I hope you can use the following example to repeat the problem and fix this bug Thank you Jan Maz=E1nek [EMAIL PROTECTED] # =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # Incorrect handling of foreign key constraints in InnoDB tables in MySQL 3.23.55-max # run on Windows ME # =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # EXAMPLE # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # Table: zbozi_skupina # parent table =20 # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D create table zbozi_skupina ( id INTEGERdefault '1' not null AUTO_INCREMENT, valid_to INTEGERdefault '2147166000' not null, valid_from INTEGERdefault '1' not null, akce VARCHAR(64)default 'zbozi_vypis' not null, constraint pk_zbozi_skupina primary key (id, valid_to) )TYPE=3DInnoDB; # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # Table: zbozi_skupina_strom# child table =20 # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D create table zbozi_skupina_strom ( id_parentINTEGERdefault '1' not null, id_child INTEGERdefault '1' not null, constraint pk_zbozi_skupina_strom primary key (id_parent, id_child) )TYPE=3DInnoDB; # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # Index: fk_skupina_zbozi_nadrizena=20 # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D create index fk_skupina_zbozi_nadrizena on zbozi_skupina_strom (id_parent asc); # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D # Index: fk_skupina_zbozi_podrizena=20 # = =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D create index fk_skupina_zbozi_podrizena on zbozi_skupina_strom (id_child asc); alter table zbozi_skupina_strom add constraint fk_zbozi_sk_sz_rodic_zbozi_sk foreign key (id_child) references zbozi_skupina (id); alter table zbozi_skupina_strom add constraint fk_zbozi_sk_sz_dite_zbozi_sk foreign key (id_parent) references zbozi_skupina (id); INSERT INTO `zbozi_skupina` ( `id`, `valid_to`, `valid_from`, `akce`) VALUES ( '1', '2147166000', '1', 'zbozi_vypis'); INSERT INTO `zbozi_skupina` ( `id
Re: Re: Altering table which has FOREIGN KEY constraints
Jungsu, please add the line log to your my.cnf. If you want to know what happens within mysqld, you should start it with --log[=file]. This will log all connections and queries to the log file (by default named `'hostname'.log'). Then the next time mysqld crashes, you can look from the General Query Log what SQL statements led to it. Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-4.0 from http://www.mysql.com sql query - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, March 12, 2003 7:32 PM Subject: re: Re: Altering table which has FOREIGN KEY constraints On Wednesday 12 March 2003 02:30, Heo Jungsu wrote: What do you mean could not execute a query on those tables? Could you be more detailed? Ok. let me explain. When I was using MySQL 4.0.10, After I add columns with ALTER TABLE on foreign key referencing table so many times, when I execute SELECT * FROM child, MySQL session is deaded with Seg. fault. even if I execute drop table child. so I rebuilded entire InnoDB Table with back-up data. Hm.. Could you create a repeatable test-case and test it on 4.0.11? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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 - 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: Re: Altering table which has FOREIGN KEY constraints
On Wednesday 12 March 2003 02:30, Heo Jungsu wrote: What do you mean could not execute a query on those tables? Could you be more detailed? Ok. let me explain. When I was using MySQL 4.0.10, After I add columns with ALTER TABLE on foreign key referencing table so many times, when I execute SELECT * FROM child, MySQL session is deaded with Seg. fault. even if I execute drop table child. so I rebuilded entire InnoDB Table with back-up data. Hm.. Could you create a repeatable test-case and test it on 4.0.11? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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
Re: Altering table which has FOREIGN KEY constraints
Reznichenko, Thank you for your answer! What do you mean could not execute a query on those tables? Could you be more detailed? Ok. let me explain. When I was using MySQL 4.0.10, After I add columns with ALTER TABLE on foreign key referencing table so many times, when I execute SELECT * FROM child, MySQL session is deaded with Seg. fault. even if I execute drop table child. so I rebuilded entire InnoDB Table with back-up data. I don't have error log and test-bed at this time. I wanna know that this problem is fixed in MySQL 4.0.11. - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, March 10, 2003 10:32 PM Subject: re: Altering table which has FOREIGN KEY constraints On Saturday 08 March 2003 09:09, Heo Jungsu wrote: I have a question about altering table which has foreign key constraints. MySQL manual says that : In InnoDB versions 3.23.50 ALTER TABLE or CREATE INDEX should not be used in connection with tables which have foreign key constraints or which are referenced in foreign key constraints: .. (omitted) .. A CREATE INDEX statement is in MySQL processed as an ALTER TABLE, and these restrictions apply also to it. on http://www.mysql.com/doc/en/SEC457.html But, how abount InnoDB versions = 3.23.50? Version 4.0.11 still cannot alter referencing table and referenced table? It can and MySQL doesn't remove foreign key constraint. What's the safe query in below lits (MySQL 4.0.11) ? * alter referencing table * alter referenced table * create index on referencing table * create index on referenced table. They are all safe ... When I have used MySQL 4.0.10 If I alter referencing and referenced table several times, sometimes I could not execute a query on those tables even drop table (So I rebuilded entire InnoDB ;-( ) What do you mean could not execute a query on those tables? Could you be more detailed? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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 - 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: Altering table which has FOREIGN KEY constraints
On Saturday 08 March 2003 09:09, Heo Jungsu wrote: I have a question about altering table which has foreign key constraints. MySQL manual says that : In InnoDB versions 3.23.50 ALTER TABLE or CREATE INDEX should not be used in connection with tables which have foreign key constraints or which are referenced in foreign key constraints: .. (omitted) .. A CREATE INDEX statement is in MySQL processed as an ALTER TABLE, and these restrictions apply also to it. on http://www.mysql.com/doc/en/SEC457.html But, how abount InnoDB versions = 3.23.50? Version 4.0.11 still cannot alter referencing table and referenced table? It can and MySQL doesn't remove foreign key constraint. What's the safe query in below lits (MySQL 4.0.11) ? * alter referencing table * alter referenced table * create index on referencing table * create index on referenced table. They are all safe ... When I have used MySQL 4.0.10 If I alter referencing and referenced table several times, sometimes I could not execute a query on those tables even drop table (So I rebuilded entire InnoDB ;-( ) What do you mean could not execute a query on those tables? Could you be more detailed? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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
Altering table which has FOREIGN KEY constraints
Hello, there. I have a question about altering table which has foreign key constraints. MySQL manual says that : In InnoDB versions 3.23.50 ALTER TABLE or CREATE INDEX should not be used in connection with tables which have foreign key constraints or which are referenced in foreign key constraints: .. (omitted) .. A CREATE INDEX statement is in MySQL processed as an ALTER TABLE, and these restrictions apply also to it. on http://www.mysql.com/doc/en/SEC457.html But, how abount InnoDB versions = 3.23.50? Version 4.0.11 still cannot alter referencing table and referenced table? What's the safe query in below lits (MySQL 4.0.11) ? * alter referencing table * alter referenced table * create index on referencing table * create index on referenced table. When I have used MySQL 4.0.10 If I alter referencing and referenced table several times, sometimes I could not execute a query on those tables even drop table (So I rebuilded entire InnoDB ;-( ) Thank you for advanced answer! ## Heo, Jungsu Mr. SimpleX Internet. http://www.simplexi.com Filter : sql - 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: possible bug: alter table trashed foreign key constraints in innodb
Chuck, - Original Message - From: Chuck Simmons [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, July 09, 2002 4:29 AM Subject: possible bug: alter table trashed foreign key constraints in innodb sql query In version 3.23.49a when using an innodb table, alter table appears to corrupt foreign key constraints. Try the following test case: create table test_base ( base_id int not null, primary key (base_id) ) type = innodb; create table test_ref ( base_id int not null, ref_id int not null, primary key (base_id, ref_id), foreign key (base_id) references test_base (base_id) ) type = innodb; insert test_base (base_id) values (1); insert test_ref (base_id, ref_id) values (1, 1); alter table test_base add column value int not null; insert test_ref (base_id, ref_id) values (1, 2); The final insert fails with mysql insert test_ref (base_id, ref_id) values (1, 2); ERROR 1216: Cannot add a child row: a foreign key constraint fails which suggests that the foreign key constraint has become hosed? the fact that ALTER TABLE spoiled foreign key definitions was a documented bug. It was fixed in 3.23.50. Chuck Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB - 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: possible bug: alter table trashed foreign key constraints in innodb
Chuck, Tuesday, July 09, 2002, 4:26:31 AM, you wrote: CS In version 3.23.49a when using an innodb table, alter table appears to CS corrupt foreign key constraints. Try the following test case: It's described in the MySQL manual: http://www.mysql.com/doc/S/E/SEC446.html and fixed since 3.23.50 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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
possible bug: alter table trashed foreign key constraints in innodbtables
sql query In version 3.23.49a when using an innodb table, alter table appears to corrupt foreign key constraints. Try the following test case: create table test_base ( base_id int not null, primary key (base_id) ) type = innodb; create table test_ref ( base_id int not null, ref_id int not null, primary key (base_id, ref_id), foreign key (base_id) references test_base (base_id) ) type = innodb; insert test_base (base_id) values (1); insert test_ref (base_id, ref_id) values (1, 1); alter table test_base add column value int not null; insert test_ref (base_id, ref_id) values (1, 2); The final insert fails with mysql insert test_ref (base_id, ref_id) values (1, 2); ERROR 1216: Cannot add a child row: a foreign key constraint fails which suggests that the foreign key constraint has become hosed? Chuck - 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
InnoDB foreign key constraints
Hello list!
I'm having a bit of trouble getting foreign key constraints to work.
I'm running MySQL 2.23.50-Max.
Here's what I got:
mysql SHOW CREATE TABLE conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(10) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(80) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
KEY `ip_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Here's what I get:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn(name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
I know error 1005 with an errno 150 means the foreign key would be incorrectly
formed, but I can't just figure out what's wrong.
I've been reading TFM, and the sentence there must be an index where the
foreign key and the referenced key are listed as the first columns seems to
have something to do with my problem, I just find the above a bit... well...
cryptic. If anyone could lend me a hand, I'd be more than happy.
Thank's in advance!
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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: InnoDB foreign key constraints
First of all the referenced key must be on PRIMARY KEY.
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
...
UNIQUE KEY `name_id` (`name_id`), - THIS IS THE FIRST DEFINITION
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`) - AND THIS IS THE SECOND ONE
...
At 10:59 2002.06.10. +0300, you wrote:
Hello list!
I'm having a bit of trouble getting foreign key constraints to work.
I'm running MySQL 2.23.50-Max.
Here's what I got:
mysql SHOW CREATE TABLE conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(10) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(80) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
KEY `ip_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Here's what I get:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn(name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
I know error 1005 with an errno 150 means the foreign key would be incorrectly
formed, but I can't just figure out what's wrong.
I've been reading TFM, and the sentence there must be an index where the
foreign key and the referenced key are listed as the first columns seems to
have something to do with my problem, I just find the above a bit... well...
cryptic. If anyone could lend me a hand, I'd be more than happy.
Thank's in advance!
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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
-
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: InnoDB foreign key constraints
Heya!
You need an INDEX.
Try doing this first :
alter table ip_name_tbl add INDEX(name_id);
And add then your constraint.
EG
mysql SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
KEY `ip_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Here's what I get:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn(name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
I know error 1005 with an errno 150 means the foreign key would be
incorrectly
formed, but I can't just figure out what's wrong.
I've been reading TFM, and the sentence there must be an index where the
foreign key and the referenced key are listed as the first columns seems to
have something to do with my problem, I just find the above a bit... well...
cryptic. If anyone could lend me a hand, I'd be more than happy.
Thank's in advance!
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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
-
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: InnoDB foreign key constraints
On Monday 10 Jun 2002 11:17 am, you wrote:
First of all the referenced key must be on PRIMARY KEY.
...which means my 'id' -field can't be a primary key, right?
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
UNIQUE KEY `name_id` (`name_id`), - THIS IS THE FIRST DEFINITION
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`) - AND THIS IS THE SECOND ONE
I've probably messed something up while fooling around with indexes and trying
to get it to work. Now, I took the advices I got, but I still can't get it to
work. I altered the table to make the 'id' -field an ordinary index, and
changed the name_id -field to a primary key, without luck. So now my
SHOW CREATE TABLE gives:
mysql show create table ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
...and
mysql show create table conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(6) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(50) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
and, again:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn (name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
*sigh*
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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: InnoDB foreign key constraints
On Monday 10 Jun 2002 11:44 am, Markus Lervik wrote: mysql show create table ip_name_tbl\G *** 1. row *** Table: ip_name_tbl Create Table: CREATE TABLE `ip_name_tbl` ( [snip] `name_id` int(11) NOT NULL default '0', [snip] mysql show create table conn\G *** 1. row *** Table: conn Create Table: CREATE TABLE `conn` ( [snip] `name_id` int(10) unsigned NOT NULL default '0', [snip] Ok, thank's to Jocelyn the problem is solved and can be seen above. Kinda makes sence that both fields should be the same. : ) Slipped my attention ; ) Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - 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: InnoDB foreign key constraints
What I think, is that your syntax for creating the primary key is slightly
incorrect. I'm not sure if this is also true vor MySQL but I got teached at
school that a foreign key can only point to the primary key of a table.
Perhaps you can try to do the following:
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(6) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(50) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`name_id`),
FOREIGN KEY (name_id) REFERENCES conn ON DELETE CASCADES
KEY `id` (`id`)
) TYPE=InnoDB;
Notice the foreign key already in the table definition, and off course
creating table `conn` before the other one. If this doesn't work, try
rewriting your foreign key constraint to:
ALTER TABLE ip_name_tbl
ADD CONSTRAINT FOREIGN KEY (name_id)
REFERENCES conn
ON DELETE CASCADE;
* without pointing to which column the key points, just the table.
Btw, why do you have an `id` field, set as NOT NULL and with an
auto_increment, with besides another field `name_id` set as primary key?
Greetzz,
Wouter
(being my first msg to this list, btw: hello everybody .. i'm wouter and new
to this list ;) hihi )
--
Alle door mij verzonden email is careware. Dit houdt in dat het alleen
herlezen en bewaard mag worden als je goed omgaat met al het leven op aarde
en daar buiten. Als je het hier niet mee eens bent dien je mijn mailtje
binnen 24 uur terug te sturen, met opgaaf van reden van onenigheid.
All email sent by me is careware. This means that it can only be reread and
kept if you are good for all the life here on earth and beyond. If you don't
agree to these terms, you should return this email in no more than 24 hours
stating the reason of disagreement.
-Oorspronkelijk bericht-
Van: Markus Lervik [mailto:[EMAIL PROTECTED]]
Verzonden: maandag 10 juni 2002 10:45
Aan: Kiss Dániel
CC: [EMAIL PROTECTED]
Onderwerp: Re: InnoDB foreign key constraints
On Monday 10 Jun 2002 11:17 am, you wrote:
First of all the referenced key must be on PRIMARY KEY.
...which means my 'id' -field can't be a primary key, right?
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
UNIQUE KEY `name_id` (`name_id`), - THIS IS THE FIRST DEFINITION
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`) - AND THIS IS THE SECOND ONE
I've probably messed something up while fooling around with indexes and
trying
to get it to work. Now, I took the advices I got, but I still can't get it
to
work. I altered the table to make the 'id' -field an ordinary index, and
changed the name_id -field to a primary key, without luck. So now my
SHOW CREATE TABLE gives:
mysql show create table ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
...and
mysql show create table conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(6) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(50) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
and, again:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn (name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
*sigh*
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
Before posting, please check:
http://www.mysql.com
- Foreign key constraints
Hi all! Question. I try to understand with 4.3 Foreign key constraints InnoDB Engine in MySQL-Max-3.23.50/MySQL-4.0.1. I use MySQL Max (InnoDB) 3.23.49 for Win2000 I have created two tables: CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE ) TYPE=INNODB; As I have understood, this design in cascade allows to delete record child table at removal(distance) of the line connected to it(her) parent table. Or in other words to organize cascade removal(distance) of a line in child table at removal(distance) of the connected line in parent table. Problem in the following. At attempt of removal(distance): delete from parent where id = 1; There is a mistake: Error: Cannot delete a parent row: a foreign key constraint: fails. Probably it is connected with blocking? I do not understand. How to remove a line from parent table so that the line child table has in cascade left connected on a key parent_id? -- Best regards, maxim mailto:[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
Re: - Foreign key constraints
maxim, Tuesday, May 28, 2002, 4:47:13 PM, you wrote: m Question. I try to understand with 4.3 Foreign key constraints m InnoDB Engine in MySQL-Max-3.23.50/MySQL-4.0.1. m I use MySQL Max (InnoDB) 3.23.49 for Win2000 m I have created two tables: m CREATE TABLE parent (id INT NOT NULL, m PRIMARY KEY (id)) TYPE=INNODB; m CREATE TABLE child (id INT, parent_id INT, m INDEX par_ind (parent_id), m FOREIGN KEY (parent_id) REFERENCES parent (id) m ON DELETE CASCADE m ) TYPE=INNODB; m As I have understood, this design in cascade allows to delete record m child table at removal(distance) of the line connected to it(her) parent table. m Or in other words to organize cascade removal(distance) of a line in child m table at removal(distance) of the connected line in parent table. m Problem in the following. At attempt of removal(distance): m delete from parent where id = 1; m There is a mistake: Error: Cannot delete a parent row: a foreign key m constraint: fails. ON DELETE CASCADE have worked since 3.23.50, 3.23.49 doesn't support ON DELETE CASCADE. So, you should first delete rows from child table that have parent_id=1 and then delete row from parent table. m Probably it is connected with blocking? I do not understand. How to remove m a line from parent table so that the line child table has in cascade left m connected on a key parent_id? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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
- HELP Foreign key constraints
Hi all! Question. I try to understand with 4.3 Foreign key constraints InnoDB Engine in MySQL-Max-3.23.50/MySQL-4.0.1. I use MySQL Max (InnoDB) 3.23.49 for Win2000 I have created two tables: CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE ) TYPE=INNODB; As I have understood, this design in cascade allows to delete record child table at removal(distance) of the line connected to it(her) parent table. Or in other words to organize cascade removal(distance) of a line in child table at removal(distance) of the connected line in parent table. Problem in the following. At attempt of removal(distance): delete from parent where id = 1; There is a mistake: Error: Cannot delete a parent row: a foreign key constraint: fails. Probably it is connected with blocking? I do not understand. How to remove a line from parent table so that the line child table has in cascade left connected on a key parent_id? -- Best regards, maxim mailto:[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
Re: InnoDB Foreign Key Constraints
Daniel, - Original Message - From: Daniel Rand [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, May 13, 2002 5:24 PM Subject: InnoDB Foreign Key Constraints Hi, Does anyone know if it's possible to set up a foreign key constraint where one table references the primary key of another table in a different database (both tables being of InnoDB type). yes, you can use the databasename.tablename syntax: heikki@hundin:~/mysql/client mysql test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 3.23.51-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show create table aaa; +---+--- ---+ | Table | Create Table | +---+--- ---+ | aaa | CREATE TABLE `aaa` ( `a` int(11) NOT NULL default '0', `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `b` (`b`) ) TYPE=InnoDB | +---+--- ---+ 1 row in set (0.01 sec) mysql use test11 Database changed mysql create table mmm (a int not null, b int, primary key (a), index (b), fore ign key (b) references test.aaa (a)); Query OK, 0 rows affected (0.00 sec) mysql show create table mmm; +---+--- + | Table | Create Table | +---+--- + | mmm | CREATE TABLE `mmm` ( `a` int(11) NOT NULL default '0', `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `b` (`b`), FOREIGN KEY (`b`) REFERENCES `test.aaa` (`a`) ) TYPE=InnoDB | +---+--- + 1 row in set (0.00 sec) mysql Thanks, DAN 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
Re: FOREIGN KEY Constraints
Carl, Monday, April 08, 2002, 3:16:26 AM, you wrote: CS From the mysql docs, it looks like you can only use foreign keys if your CS tables are type InnoDB. Is this correct? Yes, you are right. In MyISAM tables you can use REFERENCE clause, but it does nothing. CS Carl -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ 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
RE: FOREIGN KEY Constraints
Hi, Yes, you are right but be aware with the delete cascade feature, maybe in the way?. Regards - Original Message - From: Carl Schmidt [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, April 07, 2002 7:16 PM Subject: FOREIGN KEY Constraints From the mysql docs, it looks like you can only use foreign keys if your tables are type InnoDB. Is this correct? Carl - 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 - 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
FOREIGN KEY Constraints
From the mysql docs, it looks like you can only use foreign keys if your tables are type InnoDB. Is this correct? Carl - 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: Creating and dropping foreign key constraints
MySQL doesn't support foreign keys. Did you bother checking the manual? http://www.mysql.com/doc/M/i/Missing_Foreign_Keys.html Hello Does mysql support creating and dropping of foreign key constraints? i tried it.While creating foreign key constraints it doesn't dive any error but while dropping i do get some syntax errors. what is the reason? any suggetions? - 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
Creating and dropping foreign key constraints
Hello Does mysql support creating and dropping of foreign key constraints? i tried it.While creating foreign key constraints it doesn't dive any error but while dropping i do get some syntax errors. what is the reason? any suggetions? regards Div - 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
