Re: Can not add foreign key constraint
Hello, My thanks to everyone who helped on this issue. The index did it. Thanks. Dave. On 4/28/17, Johan De Meersman <vegiv...@tuxera.be> wrote: > That is quite different, as I suspected :-) > > Referential keys require an index on the target table that begins with the > referenced field, so you'll need to add one on user, as was specified in the > create table you originally posted. > > On 28 April 2017 01:21:39 CEST, David Mehler <dave.meh...@gmail.com> wrote: >>Hello, >> >> Here's the output of the command show create table virtual_users: >> >> create table virtual_users; >>+---+-+ >> | Table | Create Table >> >> >> >> >> >> >> | >>+---+-+ >> | virtual_users | 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 '0', >> `quota_messages` int(11) NOT NULL DEFAULT '0', >> PRIMARY KEY (`id`), >> UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`), >> CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) >> REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE >> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | >>+---+-+ >> 1 row in set (0.00 sec) >> >>Thanks. >>Dave. >> >>> >>> >>> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote: >>>> >>>> I note that the innodb status says it couldn't find an index on the >>>> referenced column. Did the create statements come from your create >>>> scripts >>>> or from a show create table statement? I'm suspicious about the >>index on >>>> virtual_users(user). >>>> >>>> >>>> - Original Message - >>>>> From: "David Mehler" <dave.meh...@gmail.com> >>>>> To: "MySql" <mysql@lists.mysql.com> >>>>> Sent: Tuesday, 25 April, 2017 23:07:19 >>>>> Subject: Re: Can not add foreign key constraint >>>> >>>>> Hello, >>>>> >>>>> Tried recreating the virtual_users table didn't solve anything. >>Would >>>>> it be possible for anyone to check out my box directly? >>>>> >>>>> Thanks. >>>>> Dave. >>>> >>>> -- >>>> The bay-trees in our country are all wither'd >>>> And meteors fright the fixed stars of heaven; >>>> The pale-faced moon looks bloody on the earth >>>> And lean-look'd prophets whisper fearful change. >>>> These signs forerun the death or fall of kings. >>>> -- Wm. Shakespeare, "Richard II" >>>> >>> >>> Thanks. >>> Dave. >>> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe:http://lists.mysql.com/mysql > > -- > Sent from my Android device with K-9 Mail. Please excuse my brevity. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- 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 constraint
That is quite different, as I suspected :-) Referential keys require an index on the target table that begins with the referenced field, so you'll need to add one on user, as was specified in the create table you originally posted. On 28 April 2017 01:21:39 CEST, David Mehler <dave.meh...@gmail.com> wrote: >Hello, > > Here's the output of the command show create table virtual_users: > > create table virtual_users; >+---+-+ > | Table | Create Table > > > > > > > | >+---+-+ > | virtual_users | 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 '0', > `quota_messages` int(11) NOT NULL DEFAULT '0', > PRIMARY KEY (`id`), > UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`), > CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) > REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE > ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | >+---+-+ > 1 row in set (0.00 sec) > >Thanks. >Dave. > >> >> >> On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote: >>> >>> I note that the innodb status says it couldn't find an index on the >>> referenced column. Did the create statements come from your create >>> scripts >>> or from a show create table statement? I'm suspicious about the >index on >>> virtual_users(user). >>> >>> >>> - Original Message - >>>> From: "David Mehler" <dave.meh...@gmail.com> >>>> To: "MySql" <mysql@lists.mysql.com> >>>> Sent: Tuesday, 25 April, 2017 23:07:19 >>>> Subject: Re: Can not add foreign key constraint >>> >>>> Hello, >>>> >>>> Tried recreating the virtual_users table didn't solve anything. >Would >>>> it be possible for anyone to check out my box directly? >>>> >>>> Thanks. >>>> Dave. >>> >>> -- >>> The bay-trees in our country are all wither'd >>> And meteors fright the fixed stars of heaven; >>> The pale-faced moon looks bloody on the earth >>> And lean-look'd prophets whisper fearful change. >>> These signs forerun the death or fall of kings. >>> -- Wm. Shakespeare, "Richard II" >>> >> >> Thanks. >> Dave. >> > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/mysql -- Sent from my Android device with K-9 Mail. Please excuse my brevity. -- 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 constraint
Hello, Here's the output of the command show create table virtual_users: create table virtual_users; +---+-+ | Table | Create Table | +---+-+ | virtual_users | 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 '0', `quota_messages` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `UNIQUE_EMAIL` (`domain_id`,`user`), CONSTRAINT `virtual_users_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `virtual_domains` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 | +---+-+ 1 row in set (0.00 sec) Thanks. Dave. > > > On 4/26/17, Johan De Meersman <vegiv...@tuxera.be> wrote: >> >> I note that the innodb status says it couldn't find an index on the >> referenced column. Did the create statements come from your create >> scripts >> or from a show create table statement? I'm suspicious about the index on >> virtual_users(user). >> >> >> - Original Message - >>> From: "David Mehler" <dave.meh...@gmail.com> >>> To: "MySql" <mysql@lists.mysql.com> >>> Sent: Tuesday, 25 April, 2017 23:07:19 >>> Subject: Re: Can not add foreign key constraint >> >>> Hello, >>> >>> Tried recreating the virtual_users table didn't solve anything. Would >>> it be possible for anyone to check out my box directly? >>> >>> Thanks. >>> Dave. >> >> -- >> The bay-trees in our country are all wither'd >> And meteors fright the fixed stars of heaven; >> The pale-faced moon looks bloody on the earth >> And lean-look'd prophets whisper fearful change. >> These signs forerun the death or fall of kings. >> -- Wm. Shakespeare, "Richard II" >> > > 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 constraint
Hello, Tried recreating the virtual_users table didn't solve anything. Would it be possible for anyone to check out my box directly? Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 20:47, David Mehler wrote: >> Hello, >> >> Thanks. Here's the create statements for virtual_domains, >> virtual_users, and the one that isn't working lastauth: >> >> CREATE TABLE `virtual_domains` ( >>`id` int(11) NOT NULL auto_increment, >>`name` varchar(50) NOT NULL, >>PRIMARY KEY (`id`) >> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >> >> 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; >> >> 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; > > Again, that works in 5.6 and 5.7, so if it doesn't work for you, there > is something wrong in one of your files. Did you try recreating > virtual_users? > > PB > > - > >> >> Thanks. >> Dave. >> >> >> On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: >>> On 4/24/2017 18:16, David Mehler wrote: >>>> Hello, >>>> >>>> I'm running Mysql 5.7.18. >>>> >>>> My virtual_users are working fine, it's the new table that isn't. Or >>>> am I missing something? >>> MySQL says it cannot find an index which the table's create statement >>> declares. If that's the create statement that created the table, the >>> table's corrupted. >>> >>> PB >>> >>> >>>> Thanks. >>>> Dave. >>>> >>>> >>>> On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: >>>>> On 4/24/2017 17:41, David Mehler wrote: >>>>>> Hello, >>>>>> >>>>>> Here's the output. I hope it helps. >>>>>> >>>
Re: Can not add foreign key constraint
On 4/24/2017 20:47, David Mehler wrote: Hello, Thanks. Here's the create statements for virtual_domains, virtual_users, and the one that isn't working lastauth: CREATE TABLE `virtual_domains` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 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; 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; Again, that works in 5.6 and 5.7, so if it doesn't work for you, there is something wrong in one of your files. Did you try recreating virtual_users? PB - Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: On 4/24/2017 18:16, David Mehler wrote: Hello, I'm running Mysql 5.7.18. My virtual_users are working fine, it's the new table that isn't. Or am I missing something? MySQL says it cannot find an index which the table's create statement declares. If that's the create statement that created the table, the table's corrupted. PB Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: On 4/24/2017 17:41, David Mehler wrote: Hello, Here's the output. I hope it helps. root@localhost [mail]> 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; ERROR 1215 (HY000): Cannot add foreign key constraint root@localhost
Re: Can not add foreign key constraint
Hello, Thanks. Here's the create statements for virtual_domains, virtual_users, and the one that isn't working lastauth: CREATE TABLE `virtual_domains` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 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; 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; Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 18:16, David Mehler wrote: >> Hello, >> >> I'm running Mysql 5.7.18. >> >> My virtual_users are working fine, it's the new table that isn't. Or >> am I missing something? > > MySQL says it cannot find an index which the table's create statement > declares. If that's the create statement that created the table, the > table's corrupted. > > PB > > >> >> Thanks. >> Dave. >> >> >> On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: >>> On 4/24/2017 17:41, David Mehler wrote: >>>> Hello, >>>> >>>> Here's the output. I hope it helps. >>>> >>>> root@localhost [mail]> 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
Re: Can not add foreign key constraint
Hello, I'm running Mysql 5.7.18. My virtual_users are working fine, it's the new table that isn't. Or am I missing something? Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 17:41, David Mehler wrote: >> Hello, >> >> Here's the output. I hope it helps. >> >> root@localhost [mail]> 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;
Re: Can not add foreign key constraint
Hello,
Here's the output. I hope it helps.
root@localhost [mail]> 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;
ERROR 1215 (HY000): Cannot add foreign key constraint
root@localhost [mail]> s
Re: Can not add foreign key constraint
On 4/24/2017 15:28, Peter Brawley wrote:
On 4/24/2017 13:59, David Mehler wrote:
Hello,
root@localhost [mail]> show engine innodb_status;
ERROR 1286 (42000): Unknown storage engine 'innodb_status'
Well it's very unlikely InnoDB made that up, it's probably in one of
your Create texts.
Ah, an error cascade, as Shawn Green noticed, there's a typo in my
suggested command, should be ...
show engine innodb status;
PB
-
PB
-
This is on a Mysql 5.7 setup.
Thanks.
Dave.
On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote:
On 4/24/2017 12:28, David Mehler wrote:
Hello,
Here's the create table sand error message.
root@localhost [(none)]> use mail;
Database changed
root@localhost [mail]> 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;
ERROR 1215 (HY000): Cannot add foreign key constraint
For the table it's referencing here it is:
CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(32) 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;
Hope this helps.
Adding in a dummy Create Table for the missing referenced
`virtual_domains`, we have ...
drop table if exists lastauth, virtual_users, virtual_domains;
CREATE TABLE virtual_domains (
id int PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(32) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
`quota_messages` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY (`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE
CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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;
which executes without error. To find out what's going awry in your
setup, right after the error occurs execute ...
show engine innodb_status;
and search the result for LATEST FOREIGN KEY ERROR.
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
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 constraint
On 4/24/2017 2:10 PM, Peter Brawley wrote: On 4/24/2017 12:28, David Mehler wrote: ...snip Adding in a dummy Create Table for the missing referenced `virtual_domains`, we have ... drop table if exists lastauth, virtual_users, virtual_domains; CREATE TABLE virtual_domains ( id int PRIMARY KEY ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 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; which executes without error. To find out what's going awry in your setup, right after the error occurs execute ... show engine innodb_status; and search the result for LATEST FOREIGN KEY ERROR. PB Typo warning: "innodb status" is two words (no underscore) https://dev.mysql.com/doc/refman/5.6/en/show-engine.html -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Integrated Cloud Applications & Platform Services Office: Blountville, TN Become certified in MySQL! Visit https://www.mysql.com/certification/ for details. -- 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 constraint
Hello, Here's the engines I have: root@localhost [(none)]> show engines; ++-++--+--++ | Engine | Support | Comment | Transactions | XA | Savepoints | ++-++--+--++ | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES| | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV| YES | CSV storage engine | NO | NO | NO | | ARCHIVE| YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | ++-++--+--++ 9 rows in set (0.00 sec) Not sure why I'm getting the error 1286. Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 13:59, David Mehler wrote: >> Hello, >> >> root@localhost [mail]> show engine innodb_status; >> ERROR 1286 (42000): Unknown storage engine 'innodb_status' > > Well it's very unlikely InnoDB made that up, it's probably in one of > your Create texts. > > PB > > - > >> >> >> This is on a Mysql 5.7 setup. >> >> Thanks. >> Dave. >> >> >> >> On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: >>> On 4/24/2017 12:28, David Mehler wrote: >>>> Hello, >>>> >>>> Here's the create table sand error message. >>>> >>>> root@localhost [(none)]> use mail; >>>> Database changed >>>> root@localhost [mail]> 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; >>>> ERROR 1215 (HY000): Cannot add foreign key constraint >>>> >>>> >>>> For the table it's referencing here it is: >>>> >>>> CREATE TABLE `virtual_users` ( >>>> `id` int(11) NOT NULL auto_increment, >>>> `domain_id` int(11) NOT NULL, >>>> `user` varchar(40) NOT NULL, >>>> `password` varchar(32) 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; >>>> >>>>Hope this helps. >>> Adding in a dummy Create Table for the missing referenced >>> `virtual_domains`, we have ... >>> >>> drop table if exists lastauth, virtual_users, virtual_domains; >>> >>> CREATE TABLE virtual_domains ( >>> >>> id int PRIMARY KEY >>> >>> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; >>> >>> CREATE TABLE `virtual_users` ( >>> >>> `id` int(11) NOT NULL auto_increment, >>> >>> `domain_id` int(11) NOT NULL, >>> >>> `user` varchar(40) NOT NULL, >>> >>> `password` varchar(32) NOT NULL, >>> >>> `quota` bigint(20) NOT NULL DEFAULT 256, >>> >>> `quota_messages` int(11) NOT NULL DEFAULT 0,
Re: Can not add foreign key constraint
On 4/24/2017 13:59, David Mehler wrote:
Hello,
root@localhost [mail]> show engine innodb_status;
ERROR 1286 (42000): Unknown storage engine 'innodb_status'
Well it's very unlikely InnoDB made that up, it's probably in one of
your Create texts.
PB
-
This is on a Mysql 5.7 setup.
Thanks.
Dave.
On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote:
On 4/24/2017 12:28, David Mehler wrote:
Hello,
Here's the create table sand error message.
root@localhost [(none)]> use mail;
Database changed
root@localhost [mail]> 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;
ERROR 1215 (HY000): Cannot add foreign key constraint
For the table it's referencing here it is:
CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(32) 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;
Hope this helps.
Adding in a dummy Create Table for the missing referenced
`virtual_domains`, we have ...
drop table if exists lastauth, virtual_users, virtual_domains;
CREATE TABLE virtual_domains (
id int PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `virtual_users` (
`id` int(11) NOT NULL auto_increment,
`domain_id` int(11) NOT NULL,
`user` varchar(40) NOT NULL,
`password` varchar(32) NOT NULL,
`quota` bigint(20) NOT NULL DEFAULT 256,
`quota_messages` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY (`user`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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;
which executes without error. To find out what's going awry in your
setup, right after the error occurs execute ...
show engine innodb_status;
and search the result for LATEST FOREIGN KEY ERROR.
PB
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
--
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 constraint
Hello, root@localhost [mail]> show engine innodb_status; ERROR 1286 (42000): Unknown storage engine 'innodb_status' This is on a Mysql 5.7 setup. Thanks. Dave. On 4/24/17, Peter Brawley <peter.braw...@earthlink.net> wrote: > On 4/24/2017 12:28, David Mehler wrote: >> Hello, >> >>Here's the create table sand error message. >> >>root@localhost [(none)]> use mail; >>Database changed >>root@localhost [mail]> 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; >>ERROR 1215 (HY000): Cannot add foreign key constraint >> >> >>For the table it's referencing here it is: >> >>CREATE TABLE `virtual_users` ( >> `id` int(11) NOT NULL auto_increment, >> `domain_id` int(11) NOT NULL, >> `user` varchar(40) NOT NULL, >> `password` varchar(32) 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; >> >> Hope this helps. > > Adding in a dummy Create Table for the missing referenced > `virtual_domains`, we have ... > > drop table if exists lastauth, virtual_users, virtual_domains; > > CREATE TABLE virtual_domains ( > >id int PRIMARY KEY > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > CREATE TABLE `virtual_users` ( > >`id` int(11) NOT NULL auto_increment, > >`domain_id` int(11) NOT NULL, > >`user` varchar(40) NOT NULL, > >`password` varchar(32) NOT NULL, > >`quota` bigint(20) NOT NULL DEFAULT 256, > >`quota_messages` int(11) NOT NULL DEFAULT 0, > >PRIMARY KEY (`id`), > >UNIQUE KEY (`user`), > >FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE > > ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > > 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; > > > which executes without error. To find out what's going awry in your > setup, right after the error occurs execute ... > > show engine innodb_status; > > > and search the result for LATEST FOREIGN KEY ERROR. > > PB > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- 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 constraint
On 4/24/2017 12:28, David Mehler wrote: Hello, Here's the create table sand error message. root@localhost [(none)]> use mail; Database changed root@localhost [mail]> 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; ERROR 1215 (HY000): Cannot add foreign key constraint For the table it's referencing here it is: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) 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; Hope this helps. Adding in a dummy Create Table for the missing referenced `virtual_domains`, we have ... drop table if exists lastauth, virtual_users, virtual_domains; CREATE TABLE virtual_domains ( id int PRIMARY KEY ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) NOT NULL, `quota` bigint(20) NOT NULL DEFAULT 256, `quota_messages` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), UNIQUE KEY (`user`), FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 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; which executes without error. To find out what's going awry in your setup, right after the error occurs execute ... show engine innodb_status; and search the result for LATEST FOREIGN KEY ERROR. PB -- 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 constraint
Hello, Here's the create table sand error message. root@localhost [(none)]> use mail; Database changed root@localhost [mail]> 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; ERROR 1215 (HY000): Cannot add foreign key constraint For the table it's referencing here it is: CREATE TABLE `virtual_users` ( `id` int(11) NOT NULL auto_increment, `domain_id` int(11) NOT NULL, `user` varchar(40) NOT NULL, `password` varchar(32) 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; Hope this helps. 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
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
alter table for foreign key
Looking at the docs: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html how do I alter the child table, links, so that it has a foreign key with the parent table, feeds? The feed_id field in links should, in fact, be constrained by the foreign key of feeds with a RESTRICT reference option. mysql mysql show tables; +---+ | Tables_in_rome_aggregator | +---+ | feeds | | links | | pages | +---+ 3 rows in set (0.00 sec) mysql mysql describe feeds; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | created | datetime | NO | | NULL|| | accessed | varchar(45) | NO | | NULL|| | url | varchar(767) | NO | UNI | NULL|| | status | int(11) | NO | | 0 || +--+--+--+-+-++ 5 rows in set (0.05 sec) mysql mysql describe links; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | created | datetime | NO | | NULL|| | feed_id | int(11) | NO | | 0 || | link| varchar(767) | NO | UNI | NULL|| | status | int(11) | NO | | 0 || +-+--+--+-+-++ 5 rows in set (0.00 sec) mysql While I know that I should alter the table, I don't know the syntax to establish the foreign key constraint. thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table for foreign key
Hi, This is an example: ALTER TABLE cart ADD CONSTRAINT fk_cart_customers FOREIGN KEY (custid) REFERENCES customers (custid) ON DELETE CASCADE ON UPDATE NO ACTION; (take from the GUI tool Database Workbench, avoids having to know the syntax ;) ) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -Original Message- From: thufir Sent: Monday, November 17, 2014 10:42 AM To: mysql@lists.mysql.com Subject: alter table for foreign key Looking at the docs: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html how do I alter the child table, links, so that it has a foreign key with the parent table, feeds? The feed_id field in links should, in fact, be constrained by the foreign key of feeds with a RESTRICT reference option. mysql mysql show tables; +---+ | Tables_in_rome_aggregator | +---+ | feeds | | links | | pages | +---+ 3 rows in set (0.00 sec) mysql mysql describe feeds; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | created | datetime | NO | | NULL|| | accessed | varchar(45) | NO | | NULL|| | url | varchar(767) | NO | UNI | NULL|| | status | int(11) | NO | | 0 || +--+--+--+-+-++ 5 rows in set (0.05 sec) mysql mysql describe links; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | created | datetime | NO | | NULL|| | feed_id | int(11) | NO | | 0 || | link| varchar(767) | NO | UNI | NULL|| | status | int(11) | NO | | 0 || +-+--+--+-+-++ 5 rows in set (0.00 sec) mysql While I know that I should alter the table, I don't know the syntax to establish the foreign key constraint. thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table for foreign key
On 14-11-17 01:42 AM, thufir wrote: Looking at the docs: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html how do I alter the child table, links, so that it has a foreign key with the parent table, feeds? The workbench GUI came up with: ALTER TABLE `rome_aggregator`.`links` ADD INDEX `fk_links_1_idx` (`feed_id` ASC); ALTER TABLE `rome_aggregator`.`links` ADD CONSTRAINT `fk_links_1` FOREIGN KEY (`feed_id`) REFERENCES `rome_aggregator`.`feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; which would be a cascade, but otherwise seems correct. It's normal, or necessary, to have ADD INDEX? -Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table for foreign key
On 14-11-17 01:42 AM, thufir wrote: Looking at the docs: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html how do I alter the child table, links, so that it has a foreign key with the parent table, feeds? The workbench GUI came up with: ALTER TABLE `rome_aggregator`.`links` ADD INDEX `fk_links_1_idx` (`feed_id` ASC); In general, an index is used for performance, while a constraint is used for logical concepts, like uniqueness. Most often, the DBMS uses an index to implement these logical concepts, but it's better to understand the difference. A foreign key needs a unique constraint (either a primary of a secondary unique constraint) to point to: when there's no uniqueness in the parent table, it cannot point to a record in that table. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: alter table for foreign key
On Mon, 17 Nov 2014 10:59:15 +0100, Martijn Tonies (Upscene Productions) wrote: In general, an index is used for performance, while a constraint is used for logical concepts, like uniqueness. Most often, the DBMS uses an index to implement these logical concepts, but it's better to understand the difference. The GUI resulted in: | links | CREATE TABLE `links` ( `id` int(11) NOT NULL AUTO_INCREMENT, `created` datetime NOT NULL, `feed_id` int(11) NOT NULL DEFAULT '0', `link` varchar(767) NOT NULL, `status` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `UNQ_links_0` (`link`), KEY `fk_links_1_idx` (`feed_id`), CONSTRAINT `fk_links_1` FOREIGN KEY (`feed_id`) REFERENCES `feeds` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=270 DEFAULT CHARSET=latin1 | I'll be reading a bit more about this. thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key reference
2014/01/06 14:24 -0500, Morgan Tocker You might be hitting: Important The inline REFERENCES specifications where the references are defined as part of the column specification are silently ignored. MySQL only accepts REFERENCES clauses defined as part of a separate FOREIGN KEY specification. See: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html Ugh, that seems quite right. Now, why did they do that? A separate specification is less convenient, and also less transparent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key reference
Ugh, that seems quite right. Now, why did they do that? It was added for compatibility. A separate specification is less convenient, and also less transparent. Please click affects me on http://bugs.mysql.com/bug.php?id=47771 - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Foreign-key reference
Are INNODB foreign-key references ignored in 5.6? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key reference
On 1/6/2014, 12:21 PM, h...@tbbs.net wrote: Are INNODB foreign-key references ignored in 5.6? You might be hitting: Important The inline REFERENCES specifications where the references are defined as part of the column specification are silently ignored. MySQL only accepts REFERENCES clauses defined as part of a separate FOREIGN KEY specification. See: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html - Morgan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key reference
Am 06.01.2014 18:21, schrieb h...@tbbs.net: Are INNODB foreign-key references ignored in 5.6? why should they? http://www.catb.org/~esr/faqs/smart-questions.html#beprecise signature.asc Description: OpenPGP digital signature
Re: Foreign key on multiple columns
On 2013-03-21 8:12 AM, Norah Jones wrote: I'm trying to create a foreign key on two columns but getting error... Here's what I tried: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; But I get `ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)` Dupe reference column. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key on multiple columns
On 3/21/2013 12:43 PM, Abhishek Choudhary wrote: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; i think error is because of referencing the same column in test1 table (ID,ID) . try to change the column name then run the code hope ur problem will solve out . Another solution would be to make two FK declarations, one for each column. CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1) REFERENCES test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT , CONSTRAINT fk2 FOREIGN KEY (col2) REFERENCES test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; This is the preferred syntax and it meets your original intent of associating both col1 and col2 to the ID column of the other table. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key on multiple columns
   CREATE TABLE test2 (     ID INT NOT NULL AUTO_INCREMENT,     col1 INT NOT NULL,     col2 INT NOT NULL,     PRIMARY KEY (ID),     CONSTRAINT fk FOREIGN KEY (col1, col2)            REFERENCES test1(ID, ID)     ON UPDATE CASCADE     ON DELETE RESTRICT    ) ENGINE=InnoDB; i think error is because of referencing the same column in test1 table (ID,ID) . try to change the column name then run the code hope ur problem will solve out . Abhishek choudhary, www.tech4urhelp.blogspot.com - Original Message - From: Peter Brawley peter.braw...@earthlink.net To: mysql@lists.mysql.com Cc: Sent: Thursday, 21 March 2013 8:14 PM Subject: Re: Foreign key on multiple columns On 2013-03-21 8:12 AM, Norah Jones wrote: I'm trying to create a foreign key on two columns but getting error... Here's what I tried:    CREATE TABLE test2 (     ID INT NOT NULL AUTO_INCREMENT,     col1 INT NOT NULL,     col2 INT NOT NULL,     PRIMARY KEY (ID),     CONSTRAINT fk FOREIGN KEY (col1, col2)            REFERENCES test1(ID, ID)     ON UPDATE CASCADE     ON DELETE RESTRICT    ) ENGINE=InnoDB; But I get `ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)` Dupe reference column. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:  http://lists.mysql.com/mysql
Re: Foreign-key naming
When I wrote my comment after Larry Martell s problem, I already suspected it was somewhat out of place because to his problem it did not apply. 2012/12/12 08:25 -0500, Shawn Green This is a perfectly acceptable naming convention to use. For example if you have a field on the `art` table that references the ID column of the `person` table to indicate the owner and another field to indicate the person who created the art, you might want to use the names `owner_person_id` and `artist_person_id` to keep them separate from the `id` column used to uniquely identify the work of art itself. In this design pattern, each table has a numeric ID column (string-based primary keys are perfectly legal but have their drawbacks and should be used with care) and to reference it from another table you can use the pattern parenttable_id. It keeps your naming conventions clean and predictable. If I were to try to use a USING operator in my opening example, I would be trying to match the PK fields of two separate types of data. (the USING example) SELECT ... FROM art INNER JOIN person USING(id)... Let's say that I renamed the id fields to art_id and person_id to make them table-specific. This still fails because a person's identifier as an owner is not the same as a work of art's creator. It also does not allow me to use the `person` table more than once in a single query. (FAIL: a renamed USING example) SELECT ... FROM art INNER JOIN person USING(person_id) --- does this refer to the owner or the creator of the art? (the name template example) SELECT ... FROM art INNER JOIN person owner on art.owner_person_id = owner.id INNER JOIN person artist on art.artist_person_id = artist.id ... Well, you're right, a work can refer to people in at least two different aspects, there is the work's author, and the work s owner. Neither is appropriate for the same name as found in a list of people, because now a distinction is made in the undifferentiated mass. And, yes, in general I suspect that if in one table there are more foreign-key references to the same key in another table, there is enough difference in aspect that none of them is fittingly so named as in the original table. What if neither author nor owner directly referred to people, but, instead, author referred to a table of artists, with their training style listed, and owner referred to a table of owners, with preferred styles of work listed? These tables in the end would refer to people; shall their references bear a name distinct from the key in the original table's? I believe that for every chain of foreign-key references from one table to another, if there is no other chain of foreign-key references from that one table to that other table (and no design-change that changes this is likely!), it is quite all right if along the chain each foreign-key reference and the key to which each refers have the same name. The nice thing about USING and NATURAL is that in a query only one coalesced field is yielded. I find it not quite right to pick between one field or another to yield when both are alike unless one of them is NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key and uppercase / lowercase values
http://bugs.mysql.com/bug.php?id=65648 On Fri, Jun 15, 2012 at 7:00 PM, Rick James rja...@yahoo-inc.com wrote: You are very close to a standalone test case.  Please create such.  Then post it on bugs.mysql.com . -Original Message- From: GF [mailto:gan...@gmail.com] Sent: Friday, June 15, 2012 12:45 AM To: Rick James Cc: Shawn Green; mysql@lists.mysql.com Subject: Re: Foreign key and uppercase / lowercase values I think the following might give complete information (I removed some columns not involved in the problem) Server version: 5.1.49-3 (Debian) SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) show variables like '%colla%'; +--+-+ | Variable_name     | Value      | +--+-+ | collation_connection | utf8_unicode_ci | | collation_database  | utf8_unicode_ci | | collation_server   | utf8_unicode_ci | +--+-+ 3 rows in set (0.00 sec) show variables like '%char%'; +--++ | Variable_name       | Value            | +--++ | character_set_client   | utf8            | | character_set_connection | utf8            | | character_set_database  | utf8            | | character_set_filesystem | binary           | | character_set_results   | utf8            | | character_set_server   | utf8            | | character_set_system   | utf8            | | character_sets_dir    | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) select USER_ID from TBL_USER where USER_ID = 'GIUSEPPE'; +--+ | USER_ID  | +--+ | GIUSEPPE | +--+ 1 row in set (0.00 sec)  select USER_ID from TBL_USER where USER_ID = 'giuseppe'; +--+ | USER_ID  | +--+ | GIUSEPPE | +--+ 1 row in set (0.00 sec) update TBL_USER set USER_ID = LOWER(USER_ID) where USER_ID = 'GIUSEPPE'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`myschema`.`TBL_COMMENT`, CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`)) | TBL_USER | CREATE TABLE `TBL_USER` (  `USER_ID` varchar(50) COLLATE utf8_unicode_ci NOT NULL,  cut  PRIMARY KEY (`USER_ID`),  cut ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | | TBL_COMMENT | CREATE TABLE `TBL_COMMENT` (  cut  `ID` bigint(20) NOT NULL AUTO_INCREMENT,  cut  `USER_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,  cut  PRIMARY KEY (`ID`),  cut  KEY `FK4F6E52581590B46E` (`USER_ID`),  cut  CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`),  cut ) ENGINE=InnoDB AUTO_INCREMENT=7876 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key and uppercase / lowercase values
I think the following might give complete information (I removed some columns not involved in the problem) Server version: 5.1.49-3 (Debian) SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) show variables like '%colla%'; +--+-+ | Variable_name| Value | +--+-+ | collation_connection | utf8_unicode_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +--+-+ 3 rows in set (0.00 sec) show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) select USER_ID from TBL_USER where USER_ID = 'GIUSEPPE'; +--+ | USER_ID | +--+ | GIUSEPPE | +--+ 1 row in set (0.00 sec) select USER_ID from TBL_USER where USER_ID = 'giuseppe'; +--+ | USER_ID | +--+ | GIUSEPPE | +--+ 1 row in set (0.00 sec) update TBL_USER set USER_ID = LOWER(USER_ID) where USER_ID = 'GIUSEPPE'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`myschema`.`TBL_COMMENT`, CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`)) | TBL_USER | CREATE TABLE `TBL_USER` ( `USER_ID` varchar(50) COLLATE utf8_unicode_ci NOT NULL, cut PRIMARY KEY (`USER_ID`), cut ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | | TBL_COMMENT | CREATE TABLE `TBL_COMMENT` ( cut `ID` bigint(20) NOT NULL AUTO_INCREMENT, cut `USER_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, cut PRIMARY KEY (`ID`), cut KEY `FK4F6E52581590B46E` (`USER_ID`), cut CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`), cut ) ENGINE=InnoDB AUTO_INCREMENT=7876 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Foreign key and uppercase / lowercase values
You are very close to a standalone test case. Please create such. Then post it on bugs.mysql.com . -Original Message- From: GF [mailto:gan...@gmail.com] Sent: Friday, June 15, 2012 12:45 AM To: Rick James Cc: Shawn Green; mysql@lists.mysql.com Subject: Re: Foreign key and uppercase / lowercase values I think the following might give complete information (I removed some columns not involved in the problem) Server version: 5.1.49-3 (Debian) SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) show variables like '%colla%'; +--+-+ | Variable_name| Value | +--+-+ | collation_connection | utf8_unicode_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +--+-+ 3 rows in set (0.00 sec) show variables like '%char%'; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) select USER_ID from TBL_USER where USER_ID = 'GIUSEPPE'; +--+ | USER_ID | +--+ | GIUSEPPE | +--+ 1 row in set (0.00 sec) select USER_ID from TBL_USER where USER_ID = 'giuseppe'; +--+ | USER_ID | +--+ | GIUSEPPE | +--+ 1 row in set (0.00 sec) update TBL_USER set USER_ID = LOWER(USER_ID) where USER_ID = 'GIUSEPPE'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`myschema`.`TBL_COMMENT`, CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`)) | TBL_USER | CREATE TABLE `TBL_USER` ( `USER_ID` varchar(50) COLLATE utf8_unicode_ci NOT NULL, cut PRIMARY KEY (`USER_ID`), cut ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | | TBL_COMMENT | CREATE TABLE `TBL_COMMENT` ( cut `ID` bigint(20) NOT NULL AUTO_INCREMENT, cut `USER_ID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL, cut PRIMARY KEY (`ID`), cut KEY `FK4F6E52581590B46E` (`USER_ID`), cut CONSTRAINT `FK4F6E52581590B46E` FOREIGN KEY (`USER_ID`) REFERENCES `TBL_USER` (`USER_ID`), cut ) ENGINE=InnoDB AUTO_INCREMENT=7876 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key and uppercase / lowercase values
On 6/15/2012 1:00 PM, Rick James wrote: You are very close to a standalone test case. Please create such. Then post it on bugs.mysql.com . -Original Message- From: GF [mailto:gan...@gmail.com] Sent: Friday, June 15, 2012 12:45 AM To: Rick James Cc: Shawn Green; mysql@lists.mysql.com Subject: Re: Foreign key and uppercase / lowercase values I think the following might give complete information (I removed some columns not involved in the problem) Server version: 5.1.49-3 (Debian) SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected ... Before he submits a test case, he should also review http://bugs.mysql.com/bug.php?id=27877 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-21.html This has been a well-discussed problem both inside and outside the MySQL development processes. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Foreign key and uppercase / lowercase values
Those refer _only_ to German 'ß' LATIN SMALL LETTER SHARP S. The example GF gave did not involve that character. To my knowledge, that is the only case where MySQL changed a collation after releasing it. -Original Message- From: Shawn Green [mailto:shawn.l.gr...@oracle.com] Sent: Friday, June 15, 2012 12:06 PM To: Rick James Cc: GF; mysql@lists.mysql.com Subject: Re: Foreign key and uppercase / lowercase values On 6/15/2012 1:00 PM, Rick James wrote: You are very close to a standalone test case. Please create such. Then post it on bugs.mysql.com . -Original Message- From: GF [mailto:gan...@gmail.com] Sent: Friday, June 15, 2012 12:45 AM To: Rick James Cc: Shawn Green; mysql@lists.mysql.com Subject: Re: Foreign key and uppercase / lowercase values I think the following might give complete information (I removed some columns not involved in the problem) Server version: 5.1.49-3 (Debian) SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected ... Before he submits a test case, he should also review http://bugs.mysql.com/bug.php?id=27877 http://dev.mysql.com/doc/refman/5.5/en/news-5-5-21.html This has been a well-discussed problem both inside and outside the MySQL development processes. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key and uppercase / lowercase values
On 6/15/2012 3:19 PM, Rick James wrote: Those refer _only_ to German 'ß' LATIN SMALL LETTER SHARP S. The example GF gave did not involve that character. To my knowledge, that is the only case where MySQL changed a collation after releasing it. Yes, it has been the only occurrence. However, the esset (sharp S) is just one example of the alternative spelling letters that were affected by the collation change. Thorn, the AE ligand, and many others fall into that same category. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key and uppercase / lowercase values
At 16.40 15/06/2012 -0400, Shawn Green wrote: On 6/15/2012 3:19 PM, Rick James wrote: Those refer _only_ to German 'ß' LATIN SMALL LETTER SHARP S. The example GF gave did not involve that character. To my knowledge, that is the only case where MySQL changed a collation after releasing it. Yes, it has been the only occurrence. However, the esset (sharp S) is just one example of the alternative spelling letters that were affected by the collation change. Thorn, the AE ligand, and many others fall into that same category. ß = Eszett (which in German is the spelling of SZ, although it originated as a double S ligature (U+017F + s) - SZ comes from its sharp pronunciation). The absence of an uppercase equivalent and its ligature behavior more evident than for other ligatures (although this has changed with the 1996 reform of German writing) have caused more than a headache to people dealing with charsets and collations. Speaking of collations, I found this website useful (especially when I had to compare collations of different RDBMSs): http://www.collation-charts.org/ Sorry for being OT, but every now and then it's worthwhile to share also some OT knowledge. Walter Tross Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Foreign key and uppercase / lowercase values
To discuss this further, please provide SHOW CREATE TABLE for the table in question and the table(s) tied to it via FOREIGN KEYs. -Original Message- From: GF [mailto:gan...@gmail.com] Sent: Tuesday, June 12, 2012 6:20 AM To: Shawn Green Cc: mysql@lists.mysql.com Subject: Re: Foreign key and uppercase / lowercase values Good morning. The application is Java. The database version is : Server version: 5.1.49-3 (Debian) This is an example of the problem: __ mysql SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql show variables like '%colla%'; +--+-+ | Variable_name | Value | +--+-+ | collation_connection | utf8_unicode_ci | collation_database | | utf8_unicode_ci | collation_server | utf8_unicode_ci | +--+-+ 3 rows in set (0.00 sec) mysql update MY_TABLE set USER_ID = LOWER(USER_ID) where USER_ID = mysql 'XXYYZZ'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails etc. etc. __ Since the used collation is _ci (I suppose it means case insensitive) I don't understand why it is giving an error trying to change a value to lowercase. I did try on a test environment to use the trick (SET foreign_key_checks=0;) but I don't understand why I should disable the foreign key checks when I am NOT violating them. The application was able to write in some other tables the USER_ID in lowercase. And I think that was an expected behaviour because the collation is case insensitive! Why now I can't set some values from uppercase to lowercase? There is not any weird character in the USER_ID column, just from A to Z. Thank you. On Wed, May 16, 2012 at 5:35 PM, Shawn Green shawn.l.gr...@oracle.com wrote: Hello Ananda, On 5/16/2012 6:42 AM, Ananda Kumar wrote: why are not using any where condition in the update statment WHERE clauses are not required. Performing a command without one will affect ever row on the table. On Wed, May 16, 2012 at 1:24 PM, GFgan...@gmail.com  wrote: Good morning, I have an application where the user ids were stored lowercase. Some batch import, in the user table some users stored  a uppercase id, and for some applicative logic, in other tables that have a foreign key to the user table, their user ids are stored lowercase. ... Have you any idea how to solve this situation without stopping/recreating the DB? (it's a production environment) Thanks Have you tried ?  SET foreign_key_checks=0; http://dev.mysql.com/doc/refman/5.5/en/server-system- variables.html#sy svar_foreign_key_checks If that does not work, you would need to first un-create your Foreign Key relationships, update your key values (the USER_ID fields), then re-create your Foreign Key relationships. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key and uppercase / lowercase values
Good morning. The application is Java. The database version is : Server version: 5.1.49-3 (Debian) This is an example of the problem: __ mysql SET collation_connection = utf8_unicode_ci; Query OK, 0 rows affected (0.00 sec) mysql show variables like '%colla%'; +--+-+ | Variable_name | Value | +--+-+ | collation_connection | utf8_unicode_ci | | collation_database | utf8_unicode_ci | | collation_server | utf8_unicode_ci | +--+-+ 3 rows in set (0.00 sec) mysql update MY_TABLE set USER_ID = LOWER(USER_ID) where USER_ID = 'XXYYZZ'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails etc. etc. __ Since the used collation is _ci (I suppose it means case insensitive) I don't understand why it is giving an error trying to change a value to lowercase. I did try on a test environment to use the trick (SET foreign_key_checks=0;) but I don't understand why I should disable the foreign key checks when I am NOT violating them. The application was able to write in some other tables the USER_ID in lowercase. And I think that was an expected behaviour because the collation is case insensitive! Why now I can't set some values from uppercase to lowercase? There is not any weird character in the USER_ID column, just from A to Z. Thank you. On Wed, May 16, 2012 at 5:35 PM, Shawn Green shawn.l.gr...@oracle.com wrote: Hello Ananda, On 5/16/2012 6:42 AM, Ananda Kumar wrote: why are not using any where condition in the update statment WHERE clauses are not required. Performing a command without one will affect ever row on the table. On Wed, May 16, 2012 at 1:24 PM, GFgan...@gmail.com  wrote: Good morning, I have an application where the user ids were stored lowercase. Some batch import, in the user table some users stored  a uppercase id, and for some applicative logic, in other tables that have a foreign key to the user table, their user ids are stored lowercase. ... Have you any idea how to solve this situation without stopping/recreating the DB? (it's a production environment) Thanks Have you tried ?  SET foreign_key_checks=0; http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks If that does not work, you would need to first un-create your Foreign Key relationships, update your key values (the USER_ID fields), then re-create your Foreign Key relationships. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:   http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Foreign key and uppercase / lowercase values
Good morning, I have an application where the user ids were stored lowercase. Some batch import, in the user table some users stored a uppercase id, and for some applicative logic, in other tables that have a foreign key to the user table, their user ids are stored lowercase. MySQL didn't throw any error probalby because the collation used is case insensitive. My problem is that the application is Java and java strings are case sensitive, so now I want to set user ids to lowercase EVERYWHERE. I supposed that I could execute with ease these commands: - update mytable1 set USER_ID = LOWER(USER_ID); - update mytable2 set USER_ID = LOWER(USER_ID); - update mytable3 set USER_ID = LOWER(USER_ID); But for some tables I got some Foreign key constraint to throw an error. (butwhy they didn't throw an error on the insert but just on the update???) And if I try to disable foreign key checks during these updates, I get some duplicate key errors where USER_ID is a part of composite key with other columns. (but I don't have any data that might cause a real duplicate key error just changing the case of one column) Have you any idea how to solve this situation without stopping/recreating the DB? (it's a production environment) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key and uppercase / lowercase values
why are not using any where condition in the update statment On Wed, May 16, 2012 at 1:24 PM, GF gan...@gmail.com wrote: Good morning, I have an application where the user ids were stored lowercase. Some batch import, in the user table some users stored a uppercase id, and for some applicative logic, in other tables that have a foreign key to the user table, their user ids are stored lowercase. MySQL didn't throw any error probalby because the collation used is case insensitive. My problem is that the application is Java and java strings are case sensitive, so now I want to set user ids to lowercase EVERYWHERE. I supposed that I could execute with ease these commands: - update mytable1 set USER_ID = LOWER(USER_ID); - update mytable2 set USER_ID = LOWER(USER_ID); - update mytable3 set USER_ID = LOWER(USER_ID); But for some tables I got some Foreign key constraint to throw an error. (butwhy they didn't throw an error on the insert but just on the update???) And if I try to disable foreign key checks during these updates, I get some duplicate key errors where USER_ID is a part of composite key with other columns. (but I don't have any data that might cause a real duplicate key error just changing the case of one column) Have you any idea how to solve this situation without stopping/recreating the DB? (it's a production environment) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Foreign key and uppercase / lowercase values
What language(s) are involved? What collation are you using now? The German sharp-S does not work well with case folding. utf8_unicode_ci could have stuff folding together. Etc. If you can find a USER_ID that is causing trouble, please provide SELECT HEX(USER_ID) FROM ... WHERE ... for further discussion. Instead of changing the data, why not do the casefolding as you SELECT into Java? -Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Wednesday, May 16, 2012 3:43 AM To: GF Cc: mysql@lists.mysql.com Subject: Re: Foreign key and uppercase / lowercase values why are not using any where condition in the update statment On Wed, May 16, 2012 at 1:24 PM, GF gan...@gmail.com wrote: Good morning, I have an application where the user ids were stored lowercase. Some batch import, in the user table some users stored a uppercase id, and for some applicative logic, in other tables that have a foreign key to the user table, their user ids are stored lowercase. MySQL didn't throw any error probalby because the collation used is case insensitive. My problem is that the application is Java and java strings are case sensitive, so now I want to set user ids to lowercase EVERYWHERE. I supposed that I could execute with ease these commands: - update mytable1 set USER_ID = LOWER(USER_ID); - update mytable2 set USER_ID = LOWER(USER_ID); - update mytable3 set USER_ID = LOWER(USER_ID); But for some tables I got some Foreign key constraint to throw an error. (butwhy they didn't throw an error on the insert but just on the update???) And if I try to disable foreign key checks during these updates, I get some duplicate key errors where USER_ID is a part of composite key with other columns. (but I don't have any data that might cause a real duplicate key error just changing the case of one column) Have you any idea how to solve this situation without stopping/recreating the DB? (it's a production environment) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key and uppercase / lowercase values
Hello Ananda, On 5/16/2012 6:42 AM, Ananda Kumar wrote: why are not using any where condition in the update statment WHERE clauses are not required. Performing a command without one will affect ever row on the table. On Wed, May 16, 2012 at 1:24 PM, GFgan...@gmail.com wrote: Good morning, I have an application where the user ids were stored lowercase. Some batch import, in the user table some users stored a uppercase id, and for some applicative logic, in other tables that have a foreign key to the user table, their user ids are stored lowercase. ... Have you any idea how to solve this situation without stopping/recreating the DB? (it's a production environment) Thanks Have you tried ? SET foreign_key_checks=0; http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_foreign_key_checks If that does not work, you would need to first un-create your Foreign Key relationships, update your key values (the USER_ID fields), then re-create your Foreign Key relationships. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- 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
Newbie question: Association table and Foreign Key
Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren
Re: Newbie question: Association table and Foreign Key
Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Newbie question: Association table and Foreign Key
Oh yeah, forgot to mention that. Yes, one event will have multiple categories. And one category can be applicable to multiple events. On Tue, Mar 1, 2011 at 8:33 PM, Dhaval Jaiswal jaiswal.dha...@enzenglobal.com wrote: Just curious as it is not mentioned. Can Category ID also have multiple event id ? -- Cheers Dhaval Jaiswal On 01/03/2011 5:53 PM, Wagyu Beef wrote: Hey guys, Am a newbie here and need a little help. Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Can I get some help. Thanks a million! Regards, Suren font Face='Arial' style='font-size:9pt'This e-mail, and any attachments are strictly confidential and may also contain legally privileged information. It is intended for the addressee(s) only. If you are not the intended recipient, please do not print, copy, store or act in reliance on the e-mail or any of its attachments. Instead, please notify the sender immediately and then delete the e-mail and any attachments. Unless expressly stated to the contrary, the views expressed in this e-mail are not necessarily the views of Enzen Global Solutions (P) Limited or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees makes no representation and accept no liability for the accuracy or completeness of this e-mail. You are responsible for maintaining your own virus protection and the Group Companies do not accept any liability for viruses. Enzen reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Enzen e-mail system./font
Re: Newbie question: Association table and Foreign Key
2011/03/01 20:23 +0800, Wagyu Beef Part of the database consists of two tables events and categories which look like this +---+-+ | eventID | eventName | +---+-+ | 1 | Event A | | 2 | Event B | | 3 | Event C | +---+-+ Primary Key: eventID +---+-+ | categoryID | categoryName | +---+-+ | 1 | Category A | | 2 | Category B | | 3 | Category C | +---+-+ Primary Key: categoryID The idea is that an event may have multiple categories and from what I've read here (http://lists.mysql.com/mysql/171645), many-to-many relationships in the database should be avoid. According to the link and a couple of others I found, I'm supposed to create a separate events_categories table and make linkages using a Foreign Key. Am not sure how to translate this to a SQL query. Well, if your problem is really like that in the example that you quote, then look up 'REFERENCES' under 'CREATE TABLE'. That shows you what to put in the common table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
managing a foreign key constraint over two databases
Good afternoon, I am beginning to scale out my database and I have the following problem, which I'm sure is common enough, but I suspect, having done some reading, that there a few possible solutions. Replication and XA transactions spring to mind, for example. Please bear with me. This is my first shot at this kind of problem. And please tell me whether I should redirect this question to another list. Here goes: I have a blogging system and want to separate the file functionality from the theme functionality. A theme is a skin for a blog, if you like, namely the combination of HTML, CSS, and files that constitute a particular blog's appearance. When designing themes you can bundle files with the theme, in the sense that they are associated with the theme and therefore exported alongside the HTML, CSS, and meta-data when the theme is exported. I therefore have the following tables. In fact this is a simplified view to capture the essentials: FILE ID NAME DATA DELETED THEME = ID NAME ... DELETED FILE_THEME == FILE_ID THEME_ID FOREIGN KEY (FILE_ID) REFERENCES FILE(ID) FOREIGN KEY (THEME_ID) REFERENCES THEME(ID) Because the relationship between files and themes is many to many, I have an intermediate table. I have foreign keys to stop users deleting files when they're bundled with themes, bundling files with themes that have already been deleted, etc, etc. Now both the FILE and THEME tables have a DELETED column. Currently when these resources are deleted they are not really deleted, instead the current time is inserted into the DELETED column, rather than it having a NULL value, and the resources are removed about week later (0: this will allow me to implement a recycle bin at a later stage by disabling these delayed deletions for those prepared to pay for it :0) So in effect the foreign keys are already obsolete because I need to do something like this: - START TRANSACTION INSERT INTO `FILE_THEME` (`FILE_ID`,`THEME_ID`) VALUES (1013,372); /* Check that the relevant file and theme haven't been deleted*/ SELECT FROM `FILE` WHERE `ID`=1013 AND `DELETED`=NULL SELECT FROM `THEME` WHERE `ID`=372 AND `DELETED`=NULL /* If either select returns an empty result then... */ ROLLBACK /* otherwise */ COMMIT - This makes me think that I can easily move the FILE and THEME tables to different databases if I use a distributed transaction in the above. I understand that on a very large scale to insist on synchronicity. However, given that these kinds of operations are relatively rare, consider how often a user might upload a file, for example, compared with bundling it with a theme, I think this approach is acceptable. Apologies for the rather long email, but I thought it better to outline the problem in detail for the outset. Many thanks in advance for any help in this. Kind regards, James
Foreign Key Error
Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: LATEST FOREIGN KEY ERROR 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: 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/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Store| varchar(60) | YES | | NULL|| | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 4 rows in set (0.00 sec) Please advise. TIA, Victor
RE: Foreign Key Error
or column types in the table and the referenced table do not match for constraint The columns Parent and Child are signed integers and ID is unsigned. Regards, Gavin Towey -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Monday, February 14, 2011 3:09 PM To: mysql@lists.mysql.com Subject: Foreign Key Error Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: LATEST FOREIGN KEY ERROR 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: 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/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Store| varchar(60) | YES | | NULL|| | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 4 rows in set (0.00 sec) Please advise. TIA, Victor IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Foreign Key Error
Thank you! V On Mon, Feb 14, 2011 at 9:08 PM, Gavin Towey gto...@ffn.com wrote: or column types in the table and the referenced table do not match for constraint The columns Parent and Child are signed integers and ID is unsigned. Regards, Gavin Towey -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Monday, February 14, 2011 3:09 PM To: mysql@lists.mysql.com Subject: Foreign Key Error Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: LATEST FOREIGN KEY ERROR 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: 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/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Store| varchar(60) | YES | | NULL|| | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 4 rows in set (0.00 sec) Please advise. TIA, Victor IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you.
Re: Foreign key with more columns and a constant value
The idea of a foreign key is that is is, well, a *foreign key* :-) It's meant to match up data that is in one table with data that is in another table, and a constant obviously isn't data in your table. To be precise, what you specify in your constraint are not even fields, but *indices* - and a constant is not an index field. I'm afraid you're stuck with that particular column, if you really need it. On Sat, Oct 23, 2010 at 7:23 PM, Octavian Râºniþã orasn...@gmail.comwrote: Hi, I have the following table: create table client( id int unsigned not null auto_increment primary key, name varchar(200), type1 int unsigned not null, type2 int unsigned not null, constraint foreign key(type1, type2) references constants(id, type) ) engine=InnoDB; This table is OK, but the column type2 contains a unique value for all the records from this table, let's say the value 1. Is it possible to remove that column and use a definition like the following that uses the constant value 1? create table client( id int unsigned not null auto_increment primary key, name varchar(200), type1 int unsigned not null, constraint foreign key(type1, 1) references constants(id, type) ) engine=InnoDB; If I use this table format, it gives an error although it is strange that MySQL can't use that constant value instead of a column name. I have more tables that have foreign keys which reference the table constants and in this table the IDs of the constants are not unique alone, but only in combination with the column type. This is why I need to use a foreign key with 2 columns. Is there a solution for what I want, or I will need to add that extra column with unique values in all the tables that reference the table `constants`? Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Foreign key with more columns and a constant value
Hi, I have the following table: create table client( id int unsigned not null auto_increment primary key, name varchar(200), type1 int unsigned not null, type2 int unsigned not null, constraint foreign key(type1, type2) references constants(id, type) ) engine=InnoDB; This table is OK, but the column type2 contains a unique value for all the records from this table, let's say the value 1. Is it possible to remove that column and use a definition like the following that uses the constant value 1? create table client( id int unsigned not null auto_increment primary key, name varchar(200), type1 int unsigned not null, constraint foreign key(type1, 1) references constants(id, type) ) engine=InnoDB; If I use this table format, it gives an error although it is strange that MySQL can't use that constant value instead of a column name. I have more tables that have foreign keys which reference the table constants and in this table the IDs of the constants are not unique alone, but only in combination with the column type. This is why I need to use a foreign key with 2 columns. Is there a solution for what I want, or I will need to add that extra column with unique values in all the tables that reference the table `constants`? Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Retrieving table and field a foreign key references
For example, the following do not provide this information: - show create table address; - describe address; - select * from TABLE_CONSTRAINTS; - select * from key_column_usage; For tables not using transactional engines like InnoDB, MySQL discards foreign key specs, otherwise see Find child tables and Find parent tables at http://www.artfulsoftware.com/infotree/queries.php. PB - On 7/2/2010 6:56 PM, Kris wrote: Hello, I am having trouble finding a way to retrieve the table and field a foreign key references by querying MySQL. If you look at the following example tables, there is no way after the tables are created to learn that: - address.sid actually references state.id Is this possible ? For example, the following do not provide this information: - show create table address; - describe address; - select * from TABLE_CONSTRAINTS; - select * from key_column_usage; CREATE TABLE state ( id VARCHAR(2) PRIMARY KEY, name TEXT ); CREATE TABLE address (id INT PRIMARY KEY, address text, zipcode INT, sid VARCHAR(2), FOREIGN KEY(sid) REFERENCES state(id) ); No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.439 / Virus Database: 271.1.1/2977 - Release Date: 07/02/10 06:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Retrieving table and field a foreign key references
Kris, You can use SHOW CREATE TABLE. On Fri, Jul 2, 2010 at 7:56 PM, Kris mk...@gmx.net wrote: Hello, I am having trouble finding a way to retrieve the table and field a foreign key references by querying MySQL. If you look at the following example tables, there is no way after the tables are created to learn that: Â - address.sid actually references state.id Is this possible ? For example, the following do not provide this information: - show create table address; - describe address; - select * from TABLE_CONSTRAINTS; - select * from key_column_usage; CREATE TABLE state ( id VARCHAR(2) PRIMARY KEY, name TEXT ); CREATE TABLE address (id INT PRIMARY KEY, address text, zipcode INT, sid VARCHAR(2), FOREIGN KEY(sid) REFERENCES state(id) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: Â Â http://lists.mysql.com/mysql?unsub=ba...@xaprb.com -- Baron Schwartz Percona Inc http://www.percona.com/ Consulting, Training, Support Services for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Retrieving table and field a foreign key references
Hello, I am having trouble finding a way to retrieve the table and field a foreign key references by querying MySQL. If you look at the following example tables, there is no way after the tables are created to learn that: - address.sid actually references state.id Is this possible ? For example, the following do not provide this information: - show create table address; - describe address; - select * from TABLE_CONSTRAINTS; - select * from key_column_usage; CREATE TABLE state ( id VARCHAR(2) PRIMARY KEY, name TEXT ); CREATE TABLE address (id INT PRIMARY KEY, address text, zipcode INT, sid VARCHAR(2), FOREIGN KEY(sid) REFERENCES state(id) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Foreign Key Problem
Hi; mysql alter table Flights type=InnoDB; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add pilot_id int not null; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add foreign key (pilot_id) references Pilots (id); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`seaflight/#sql-4d89_3ac`, CONSTRAINT `#sql-4d89_3ac_ibfk_1` FOREIGN KEY (`pilot_id`) REFERENCES `Pilots` (`id`)) mysql alter table Pilots type=InnoDB; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add foreign key (pilot_id) references Pilots (id); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`seaflight/#sql-4d89_3ac`, CONSTRAINT `#sql-4d89_3ac_ibfk_1` FOREIGN KEY (`pilot_id`) REFERENCES `Pilots` (`id`)) mysql describe Pilots; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | first_name | varchar(20) | NO | | NULL|| | middle_name | varchar(20) | YES | | NULL|| | last_name | varchar(20) | NO | | NULL|| | weight | int(11) | NO | | NULL|| +-+-+--+-+-++ 5 rows in set (0.00 sec) Please advise how to alter Flights to take the foreign key. TIA, Victor
Re: Foreign Key Problem
Problem solved. I tried everything that *should* have worked and didn't. Then I just wiped the test database and started with everything *fixed* (all engine=innodb, all keys of same type, etc.) and it all worked. V
Re: Foreign Key Problem
Hi Victor, The actual problem is with the key field. Flights.pilot_id is set to INT NOT NULL and you had specified Pilots.id to INT NULL. You have to change both the columns to NULL or else NOT NULL to avoid the error. Regards, Jay MySQL DBA Datavail CORP On Tue, Jun 22, 2010 at 7:45 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; mysql alter table Flights type=InnoDB; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add pilot_id int not null; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add foreign key (pilot_id) references Pilots (id); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`seaflight/#sql-4d89_3ac`, CONSTRAINT `#sql-4d89_3ac_ibfk_1` FOREIGN KEY (`pilot_id`) REFERENCES `Pilots` (`id`)) mysql alter table Pilots type=InnoDB; Query OK, 1 row affected, 1 warning (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql alter table Flights add foreign key (pilot_id) references Pilots (id); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`seaflight/#sql-4d89_3ac`, CONSTRAINT `#sql-4d89_3ac_ibfk_1` FOREIGN KEY (`pilot_id`) REFERENCES `Pilots` (`id`)) mysql describe Pilots; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | first_name | varchar(20) | NO | | NULL|| | middle_name | varchar(20) | YES | | NULL|| | last_name | varchar(20) | NO | | NULL|| | weight | int(11) | NO | | NULL|| +-+-+--+-+-++ 5 rows in set (0.00 sec) Please advise how to alter Flights to take the foreign key. TIA, Victor
Re: Foreign Key Problem
On Tue, Jun 22, 2010 at 11:53 AM, jayabharath jbhara...@gmail.com wrote: Hi Victor, The actual problem is with the key field. Flights.pilot_id is set to INT NOT NULL and you had specified Pilots.id to INT NULL. You have to change both the columns to NULL or else NOT NULL to avoid the error. Thanks. V
Re: Foreign Key Problem
This is just for the sake of future googlers of this thread. The correct
mysql command is:
ursor.execute('create table if not exists Passengers (id int(11)
auto_increment primary key, flights_id int(11) not null, customer_id int(11)
not null, foreign key (flights_id) references Flights (id), foreign key
(customer_id) references Customers (id), name varchar(40), weight int, price
float(6,2)) engine=InnoDB;')
beno
Another Foreign Key Problem
Hi;
When I try to execute this code from my Python script, I get this error:
Traceback (most recent call last):
File
/var/www/html/creative.vi/clients/sea-flight/reservations/create_edit_bags3.py,
line 38, in ?
create_edit_bags3()
File
/var/www/html/creative.vi/clients/sea-flight/reservations/create_edit_bags3.py,
line 32, in create_edit_bags3
cursor.execute('insert into Baggage values (Null, %s, %s, %s,
%s)', (flight_id, customer_id, weight, ticket_no))
File /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py, line
163, in execute
self.errorhandler(self, exc, value)
File /usr/lib64/python2.4/site-packages/MySQLdb/connections.py,
line 35, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1452, 'Cannot add or update a child row: a foreign
key constraint fails (`seaflight/Baggage`, CONSTRAINT `Baggage_ibfk_2`
FOREIGN KEY (`customer_id`) REFERENCES `Customers` (`id`))')
However, when I try from the MySQL prompt after duly printing it out from
the code, it works. Why?
TIA,
Victor
Re: Another Foreign Key Problem
I'm canceling this thread. It belongs in the Python list. Sorry!
V
On Fri, May 21, 2010 at 1:24 PM, Victor Subervi victorsube...@gmail.comwrote:
Hi;
When I try to execute this code from my Python script, I get this error:
Traceback (most recent call last):
File
/var/www/html/creative.vi/clients/sea-flight/reservations/create_edit_bags3.py,
line 38, in ?
create_edit_bags3()
File
/var/www/html/creative.vi/clients/sea-flight/reservations/create_edit_bags3.py,
line 32, in create_edit_bags3
cursor.execute('insert into Baggage values (Null, %s, %s, %s, %s)',
(flight_id, customer_id, weight, ticket_no))
File /usr/lib64/python2.4/site-packages/MySQLdb/cursors.py, line 163, in
execute
self.errorhandler(self, exc, value)
File /usr/lib64/python2.4/site-packages/MySQLdb/connections.py, line 35,
in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (1452, 'Cannot add or update a child row: a foreign key
constraint fails (`seaflight/Baggage`, CONSTRAINT `Baggage_ibfk_2` FOREIGN
KEY (`customer_id`) REFERENCES `Customers` (`id`))')
However, when I try from the MySQL prompt after duly printing it out from
the code, it works. Why?
TIA,
Victor
Re: Foreign Key Problem
On Wed, May 19, 2010 at 12:02 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Victor Subervi wrote: On Wed, May 19, 2010 at 10:59 AM, Shawn Green shawn.l.gr...@oracle.com wrote: Shawn Green wrote: look again closely at your FK definitions. The pattern should be FOREIGN KEY (child_table_column) REFERENCES parent_table(parent_table_column) Yours appears to be something else. And indeed it was. This works: create table if not exists Passengers (id int(11) auto_increment primary key, flights_id int(11) not null, customer_id int(11) not null, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; Yay! Thanks! V -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN
Re: Foreign Key Problem
On Tue, May 18, 2010 at 2:23 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Shawn Green wrote: I may be confused but how can the ID of the Passengers table be both the ID of the Flight they are taking and their Customer ID at the same time? http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html You may want additional ID columns in the Passengers table to point to the parent values in those other tables. Please help me out here. This is what I have: mysql describe Customers; +-++--+-+-++ | Field | Type | Null | Key | Default | Extra | +-++--+-+-++ | id | int(11)| NO | PRI | NULL| auto_increment | mysql describe Flights; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | create table if not exists Passengers (id int(11) auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; Please help me see where I'm stumbling. All the fields have the same type. If I've got it right, foreign key (id) references Flights (flights_id) means that the field flights_id will be created in the table Customers and it will reference id in Flights. Trying to interchange those throws an error indicating that flights_id doesn't exist, presumably in Flights. I'm lost, but close to home ;) Please help. V
Re: Foreign Key Problem
Victor Subervi wrote: On Tue, May 18, 2010 at 2:23 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Shawn Green wrote: I may be confused but how can the ID of the Passengers table be both the ID of the Flight they are taking and their Customer ID at the same time? http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html You may want additional ID columns in the Passengers table to point to the parent values in those other tables. Please help me out here. This is what I have: mysql describe Customers; +-++--+-+-++ | Field | Type | Null | Key | Default | Extra | +-++--+-+-++ | id | int(11)| NO | PRI | NULL| auto_increment | mysql describe Flights; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | create table if not exists Passengers (id int(11) auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; Please help me see where I'm stumbling. All the fields have the same type. If I've got it right, foreign key (id) references Flights (flights_id) means that the field flights_id will be created in the table Customers and it will reference id in Flights. Trying to interchange those throws an error indicating that flights_id doesn't exist, presumably in Flights. I'm lost, but close to home ;) Please help. V AH! that's your mistake. You think that creating the FK will also create the column. That does not happen. You have to define the table completely before you can associate the columns on this table (the child table) with the correct column on the parent table (either Flights or Customers). You need to declare two more fields before you can link them through a Foreign Key relationship to a field on another table: CREATE TABLE PASSENGERS ( id int auto_increment , flights_id int not null , customer_id int not null ... other passenger table columns here ... , PRIMARY KEY (id) , FOREIGN KEY (flights_id) REFERENCES Flights(id) , FOREIGN KEY (customer_id) REFERENCES Customer(id) ) ENGINE=INNODB; Try it that way and see if it helps. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Foreign Key Problem
On Wed, May 19, 2010 at 10:59 AM, Shawn Green shawn.l.gr...@oracle.comwrote: Shawn Green wrote: AH! that's your mistake. You think that creating the FK will also create the column. That does not happen. You have to define the table completely before you can associate the columns on this table (the child table) with the correct column on the parent table (either Flights or Customers). You need to declare two more fields before you can link them through a Foreign Key relationship to a field on another table: CREATE TABLE PASSENGERS ( id int auto_increment , flights_id int not null , customer_id int not null ... other passenger table columns here ... , PRIMARY KEY (id) , FOREIGN KEY (flights_id) REFERENCES Flights(id) , FOREIGN KEY (customer_id) REFERENCES Customer(id) ) ENGINE=INNODB; Try it that way and see if it helps. Well, that was certainly one error, but there's another: LATEST FOREIGN KEY ERROR 100519 8:46:10 Error in foreign key constraint of table seaflight/Passengers: foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB: Cannot resolve column name close to: ), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB Now, I've added the innodb engine to all tables and constraints on the id columns of the foreign key tables. What else? TIA, V
Re: Foreign Key Problem
Victor Subervi wrote: On Wed, May 19, 2010 at 10:59 AM, Shawn Green shawn.l.gr...@oracle.comwrote: Shawn Green wrote: AH! that's your mistake. You think that creating the FK will also create the column. That does not happen. You have to define the table completely before you can associate the columns on this table (the child table) with the correct column on the parent table (either Flights or Customers). You need to declare two more fields before you can link them through a Foreign Key relationship to a field on another table: CREATE TABLE PASSENGERS ( id int auto_increment , flights_id int not null , customer_id int not null ... other passenger table columns here ... , PRIMARY KEY (id) , FOREIGN KEY (flights_id) REFERENCES Flights(id) , FOREIGN KEY (customer_id) REFERENCES Customer(id) ) ENGINE=INNODB; Try it that way and see if it helps. Well, that was certainly one error, but there's another: LATEST FOREIGN KEY ERROR 100519 8:46:10 Error in foreign key constraint of table seaflight/Passengers: foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB: Cannot resolve column name close to: ), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB Now, I've added the innodb engine to all tables and constraints on the id columns of the foreign key tables. What else? TIA, V look again closely at your FK definitions. The pattern should be FOREIGN KEY (child_table_column) REFERENCES parent_table(parent_table_column) Yours appears to be something else. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Foreign Key Problem
Hi; mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)); Query OK, 0 rows affected (0.00 sec) mysql select c.first_name, c.middle_name, c.last_name, c.suffix, c.discount, p.flights_id from Customers c join Passengers p on c.id=p.customer_id where flights_id=1; ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' mysql describe Passengers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | name | varchar(40) | YES | | NULL|| | weight | tinyint(3) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.01 sec) So, why didn't the foreign key get created? It exists as a primary key in Customers. Please advise. TIA, Victor
Re: Foreign Key Problem
You're not specifying an engine, and the default is MyISAM, which doesn't support foreign keys and will likely silently ignore requests for them. Can you confirm that you've changed the default engine to InnoDB ? On Tue, May 18, 2010 at 3:44 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)); Query OK, 0 rows affected (0.00 sec) mysql select c.first_name, c.middle_name, c.last_name, c.suffix, c.discount, p.flights_id from Customers c join Passengers p on c.id=p.customer_id where flights_id=1; ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' mysql describe Passengers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | name | varchar(40) | YES | | NULL|| | weight | tinyint(3) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.01 sec) So, why didn't the foreign key get created? It exists as a primary key in Customers. Please advise. TIA, Victor -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Foreign Key Problem
On Tue, May 18, 2010 at 10:06 AM, Johan De Meersman vegiv...@tuxera.bewrote: You're not specifying an engine, and the default is MyISAM, which doesn't support foreign keys and will likely silently ignore requests for them. Can you confirm that you've changed the default engine to InnoDB ? Got me. No, it wasn't and I'm new to this. Set up my.cnf like this: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysqld_safe] skip-external-locking max_connections=200 read_buffer_size=1M sort_buffer_size=1M log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory innodb_buffer_pool_size=256M innodb_additional_mem_pool_size=20M # # Set the log file size to about 25% of the buffer pool size innodb_log_file_size=64M innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 and restarted mysqld. Then this: mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; ERROR 1005 (HY000): Can't create table './seaflight/Passengers.frm' (errno: 150) So apparently it didn't like my foreign key. Do I need to do something with the table I'm referencing or what? TIA, V On Tue, May 18, 2010 at 3:44 PM, Victor Subervi victorsube...@gmail.comwrote: Hi; mysql create table if not exists Passengers (id int unsigned auto_increment primary key, foreign key (id) references Flights (flights_id), foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)); Query OK, 0 rows affected (0.00 sec) mysql select c.first_name, c.middle_name, c.last_name, c.suffix, c.discount, p.flights_id from Customers c join Passengers p on c.id=p.customer_id where flights_id=1; ERROR 1054 (42S22): Unknown column 'p.flights_id' in 'field list' mysql describe Passengers; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(10) unsigned | NO | PRI | NULL| auto_increment | | name | varchar(40) | YES | | NULL|| | weight | tinyint(3) | YES | | NULL|| ++--+--+-+-++ 3 rows in set (0.01 sec) So, why didn't the foreign key get created? It exists as a primary key in Customers. Please advise. TIA, Victor -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Foreign Key Problem
On Tue, May 18, 2010 at 6:00 PM, Victor Subervi victorsube...@gmail.comwrote: So apparently it didn't like my foreign key. Do I need to do something with the table I'm referencing or what? TIA. Well, quickfix is to convert your tables to innoDB, starting with the lowest-level (foreign-key only ones) first - I'm not sure what happens if you set a referential constraint from an innodb table to a myisam table. You can easily convert tables with alter table *yourtable* engine=innodb;. For the tables where you also want to add constraints and/or indices, alter table *yourtable* add constraint *yourconstrainthere* engine=innodb; does the trick in one go. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Foreign Key Problem
Johan De Meersman wrote: On Tue, May 18, 2010 at 6:00 PM, Victor Subervi victorsube...@gmail.comwrote: So apparently it didn't like my foreign key. Do I need to do something with the table I'm referencing or what? TIA. Well, quickfix is to convert your tables to innoDB, starting with the lowest-level (foreign-key only ones) first - I'm not sure what happens if you set a referential constraint from an innodb table to a myisam table. You can easily convert tables with alter table *yourtable* engine=innodb;. For the tables where you also want to add constraints and/or indices, alter table *yourtable* add constraint *yourconstrainthere* engine=innodb; does the trick in one go. Both ends of the key need to be InnoDB. The fields also need to match in type, nullability, and collation. For additional details about failed FK attempts, check the error details in the SHOW INNODB STATUS report. -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Foreign Key Problem
On Tue, May 18, 2010 at 1:09 PM, Shawn Green shawn.l.gr...@oracle.comwrote: Johan De Meersman wrote: For additional details about failed FK attempts, check the error details in the SHOW INNODB STATUS report. I get this: 100518 10:26:22 Error in foreign key constraint of table seaflight/Passengers: constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB: Cannot resolve column name close to: ), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB Here's my command: create table if not exists Passengers (id int unsigned auto_increment primary key, constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; I think I've got those constraints right. I'm calling the data from those other tables, not the other way around. Please advise. TIA, V
Re: Foreign Key Problem
Victor Subervi wrote: On Tue, May 18, 2010 at 1:09 PM, Shawn Green shawn.l.gr...@oracle.com mailto:shawn.l.gr...@oracle.com wrote: Johan De Meersman wrote: For additional details about failed FK attempts, check the error details in the SHOW INNODB STATUS report. I get this: 100518 10:26:22 Error in foreign key constraint of table seaflight/Passengers: constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB: Cannot resolve column name close to: ), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB Here's my command: create table if not exists Passengers (id int unsigned auto_increment primary key, constraint foreign key (id) references Flights (flights_id), constraint foreign key (id) references Customers (customer_id), name varchar(40), weight tinyint(3)) engine=InnoDB; I think I've got those constraints right. I'm calling the data from those other tables, not the other way around. Please advise. TIA, V I may be confused but how can the ID of the Passengers table be both the ID of the Flight they are taking and their Customer ID at the same time? http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html You may want additional ID columns in the Passengers table to point to the parent values in those other tables. Also, -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Can't create foreign key
I'm trying to create a foreign key by executing the following statement: ALTER TABLE `cc`.`takenlessons` ADD CONSTRAINT `fk_lessons` FOREIGN KEY (`LessonID` ) REFERENCES `cc`.`lessons` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION , ADD INDEX `fk_lessons` (`LessonID` ASC) ; I'm using the RC of MySQL workbench to do this. When I execute this statement, I get the following error: Error Code: 1005 Can't create table 'cc.#sql-115c_61' (errno: 121)) In the past when I got a similar error, it was because the fields didn't match exactly. For instance, one may be Int(10) and one Int(11), or one may be Unsigned, and the other not. But, in this case, both match exactly. What other reasons are there for a foreign key creation to fail like that? Thanks, Jesse
RE: Can't create foreign key
Perror 121 says: OS error code 121: Remote I/O error Which I'm not too sure why an ALTER to add an constraint would give that error. Normally though, foreign key errors are shown in the SHOW ENGINE INNODB STATUS \G output, look for more details there. Regards, Gavin Towey -Original Message- From: j...@msdlg.com [mailto:j...@msdlg.com] Sent: Friday, May 14, 2010 10:18 AM To: mysql@lists.mysql.com Subject: Can't create foreign key I'm trying to create a foreign key by executing the following statement: ALTER TABLE `cc`.`takenlessons` ADD CONSTRAINT `fk_lessons` FOREIGN KEY (`LessonID` ) REFERENCES `cc`.`lessons` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION , ADD INDEX `fk_lessons` (`LessonID` ASC) ; I'm using the RC of MySQL workbench to do this. When I execute this statement, I get the following error: Error Code: 1005 Can't create table 'cc.#sql-115c_61' (errno: 121)) In the past when I got a similar error, it was because the fields didn't match exactly. For instance, one may be Int(10) and one Int(11), or one may be Unsigned, and the other not. But, in this case, both match exactly. What other reasons are there for a foreign key creation to fail like that? Thanks, Jesse This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can't create foreign key
Haven't done this in a while, but I'm guessing that you can't create both a constraint and an index with the same name? Type mismatch will in my experience most often generate an errno 150. / Carsten j...@msdlg.com skrev: I'm trying to create a foreign key by executing the following statement: ALTER TABLE `cc`.`takenlessons` ADD CONSTRAINT `fk_lessons` FOREIGN KEY (`LessonID` ) REFERENCES `cc`.`lessons` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION , ADD INDEX `fk_lessons` (`LessonID` ASC) ; I'm using the RC of MySQL workbench to do this. When I execute this statement, I get the following error: Error Code: 1005 Can't create table 'cc.#sql-115c_61' (errno: 121)) In the past when I got a similar error, it was because the fields didn't match exactly. For instance, one may be Int(10) and one Int(11), or one may be Unsigned, and the other not. But, in this case, both match exactly. What other reasons are there for a foreign key creation to fail like that? Thanks, Jesse !DSPAM:451,4bed85fe818443309765824! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Can't create foreign key
Interesting. Thanks for that tip on showing the INNODB STATUS. It turned up the error: A foreign key constraint of name `cc/fk_lessons` already exists. ...which tells me that a foreign key name cannot already be in use even if on a different table (at least that's what it appears to be saying). I named the foreign key something totally different than anything else in the database, and it worked. So, I guess foreign keys must have unique names like tables do. Thanks, Jesse -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, May 14, 2010 1:45 PM To: j...@msdlg.com; mysql@lists.mysql.com Subject: RE: Can't create foreign key Perror 121 says: OS error code 121: Remote I/O error Which I'm not too sure why an ALTER to add an constraint would give that error. Normally though, foreign key errors are shown in the SHOW ENGINE INNODB STATUS \G output, look for more details there. Regards, Gavin Towey -Original Message- From: j...@msdlg.com [mailto:j...@msdlg.com] Sent: Friday, May 14, 2010 10:18 AM To: mysql@lists.mysql.com Subject: Can't create foreign key I'm trying to create a foreign key by executing the following statement: ALTER TABLE `cc`.`takenlessons` ADD CONSTRAINT `fk_lessons` FOREIGN KEY (`LessonID` ) REFERENCES `cc`.`lessons` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION , ADD INDEX `fk_lessons` (`LessonID` ASC) ; I'm using the RC of MySQL workbench to do this. When I execute this statement, I get the following error: Error Code: 1005 Can't create table 'cc.#sql-115c_61' (errno: 121)) In the past when I got a similar error, it was because the fields didn't match exactly. For instance, one may be Int(10) and one Int(11), or one may be Unsigned, and the other not. But, in this case, both match exactly. What other reasons are there for a foreign key creation to fail like that? Thanks, Jesse This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub...@msdlg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Can't create foreign key
Nope, that wasn't the problem. I wasn't aware that the foreign key name must be unique. So, if you have a foreign key named fk_lesson, that same name cannot exist already, even if on a different table. I named the foreign key something completely different and that solved the problem. Jesse -Original Message- From: Carsten Pedersen [mailto:cars...@bitbybit.dk] Sent: Friday, May 14, 2010 1:49 PM To: j...@msdlg.com Cc: mysql@lists.mysql.com Subject: Re: Can't create foreign key Haven't done this in a while, but I'm guessing that you can't create both a constraint and an index with the same name? Type mismatch will in my experience most often generate an errno 150. / Carsten j...@msdlg.com skrev: I'm trying to create a foreign key by executing the following statement: ALTER TABLE `cc`.`takenlessons` ADD CONSTRAINT `fk_lessons` FOREIGN KEY (`LessonID` ) REFERENCES `cc`.`lessons` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION , ADD INDEX `fk_lessons` (`LessonID` ASC) ; I'm using the RC of MySQL workbench to do this. When I execute this statement, I get the following error: Error Code: 1005 Can't create table 'cc.#sql-115c_61' (errno: 121)) In the past when I got a similar error, it was because the fields didn't match exactly. For instance, one may be Int(10) and one Int(11), or one may be Unsigned, and the other not. But, in this case, both match exactly. What other reasons are there for a foreign key creation to fail like that? Thanks, Jesse !DSPAM:451,4bed85fe818443309765824! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub...@msdlg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Can't create foreign key
I have faced the same issue in past. * key name must be a unique*. On Fri, May 14, 2010 at 11:33 PM, j...@msdlg.com wrote: Nope, that wasn't the problem. I wasn't aware that the foreign key name must be unique. So, if you have a foreign key named fk_lesson, that same name cannot exist already, even if on a different table. I named the foreign key something completely different and that solved the problem. Jesse -Original Message- From: Carsten Pedersen [mailto:cars...@bitbybit.dk] Sent: Friday, May 14, 2010 1:49 PM To: j...@msdlg.com Cc: mysql@lists.mysql.com Subject: Re: Can't create foreign key Haven't done this in a while, but I'm guessing that you can't create both a constraint and an index with the same name? Type mismatch will in my experience most often generate an errno 150. / Carsten j...@msdlg.com skrev: I'm trying to create a foreign key by executing the following statement: ALTER TABLE `cc`.`takenlessons` ADD CONSTRAINT `fk_lessons` FOREIGN KEY (`LessonID` ) REFERENCES `cc`.`lessons` (`id` ) ON DELETE CASCADE ON UPDATE NO ACTION , ADD INDEX `fk_lessons` (`LessonID` ASC) ; I'm using the RC of MySQL workbench to do this. When I execute this statement, I get the following error: Error Code: 1005 Can't create table 'cc.#sql-115c_61' (errno: 121)) In the past when I got a similar error, it was because the fields didn't match exactly. For instance, one may be Int(10) and one Int(11), or one may be Unsigned, and the other not. But, in this case, both match exactly. What other reasons are there for a foreign key creation to fail like that? Thanks, Jesse !DSPAM:451,4bed85fe818443309765824! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub...@msdlg.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Remove - Foreign key constraint in innodb
Hi, I tried to remove foreign key constraint in innodb table. I tried with different ways; but i am unable to drop the constraints. http://lists.mysql.com/mysql/113053 It says that, droping the foreign key constraint it is not possible in innodb engine. Is it so? or any other possibilities? I am using mysql 5.1.32 Please, Can any one you help me? Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: Remove - Foreign key constraint in innodb
Yes - you can drop a foreign key constraint, use the 'alter table ... drop foreign key ...' command. If you get an error message, post the error message. On Thu, Feb 25, 2010 at 6:03 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I tried to remove foreign key constraint in innodb table. I tried with different ways; but i am unable to drop the constraints. http://lists.mysql.com/mysql/113053 It says that, droping the foreign key constraint it is not possible in innodb engine. Is it so? or any other possibilities? I am using mysql 5.1.32 Please, Can any one you help me? Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Remove - Foreign key constraint in innodb
An example how to delete a foreign key from an InnoDB table: test CREATE TABLE table_1 (id int unsigned NOT NULL auto_increment PRIMARY KEY) ENGINE=InnoDB; Query OK, 0 rows affected (0.56 sec) test CREATE TABLE table_2 (table1_id int unsigned NOT NULL, FOREIGN KEY (table1_id) REFERENCES table_1 (id)) ENGINE=InnoDB; Query OK, 0 rows affected (0.11 sec) test SHOW CREATE TABLE table_2\G *** 1. row *** Table: table_2 Create Table: CREATE TABLE `table_2` ( `table1_id` int(10) unsigned NOT NULL, KEY `table1_id` (`table1_id`), CONSTRAINT `table_2_ibfk_1` FOREIGN KEY (`table1_id`) REFERENCES `table_1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.47 sec) test ALTER TABLE table_2 DROP FOREIGN KEY table_2_ibfk_1; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 test SHOW CREATE TABLE table_2\G *** 1. row *** Table: table_2 Create Table: CREATE TABLE `table_2` ( `table1_id` int(10) unsigned NOT NULL, KEY `table1_id` (`table1_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) On 25/02/2010, at 11:33 PM, Jim Lyons wrote: Yes - you can drop a foreign key constraint, use the 'alter table ... drop foreign key ...' command. If you get an error message, post the error message. On Thu, Feb 25, 2010 at 6:03 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I tried to remove foreign key constraint in innodb table. I tried with different ways; but i am unable to drop the constraints. http://lists.mysql.com/mysql/113053 It says that, droping the foreign key constraint it is not possible in innodb engine. Is it so? or any other possibilities? I am using mysql 5.1.32 Please, Can any one you help me? Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to add foreign key in alter command
Hi guys Please tell me the command syntax, how to add a colmmen foreign key in alter syntax thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: how to add foreign key in alter command
Date: Sun, 17 May 2009 14:25:55 +0800 From: nathan.vorbei.t...@gmail.com To: mysql@lists.mysql.com Subject: how to add foreign key in alter command Hi guys Please tell me the command syntax, how to add a colmmen foreign key in alter syntax thanks Hello Nathan, Try the syntax below: ALTER TABLE tbl ADD foreign key(x) references(x); N.B: Where (x) is your foreign key. Hope that helps. Greetings from Nigeria. Alugo Abdulazeez www.frangeovic.com _ Show them the way! Add maps and directions to your party invites. http://www.microsoft.com/windows/windowslive/products/events.aspx
Foreign Key Issue
Hi,
I have the script below to create 5 tables. Three of them create fine but
two return an error of 150 which I understand to be a foreign key issue,
however I can't see anything wrong with the foreign key statements.
Could someone possibly have a look and see if they can identify the issue
please? I think I have been looking at it so long now 'I can't see the
wood for the trees'.
The two tables which fail are `cube_security` and `cube_measures`. Im
running MySQL 5.1.32 on a Windows XP test machine.
SCRIPT--
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `cubedoc` DEFAULT CHARACTER SET latin1 COLLATE
latin1_general_ci ;
SHOW WARNINGS;
USE `cubedoc`;
-- -
-- Table `cubedoc`.`cubes`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cubes` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cubes` (
`idcubes` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`cube_name` CHAR(30) NOT NULL ,
`cube_kit_location` VARCHAR(100) NOT NULL DEFAULT 'On Development Pc' ,
`Developer` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcubes`) )
ENGINE = InnoDB
COMMENT = 'General Cube Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_dimensions`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_dimensions` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_dimensions` (
`idcube_dimensions` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`dimension_name` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
PRIMARY KEY (`idcube_dimensions`) ,
INDEX `dimensions_cubes_fk` (`idcubes` ASC) ,
CONSTRAINT `dimensions_cubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Dimension Data ';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_security`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_security` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_security` (
`idcube_security` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`dimension` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
`restricted_user_group` CHAR(30) NOT NULL ,
`restriction_details` TEXT NOT NULL ,
PRIMARY KEY (`idcube_security`) ,
INDEX `security_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `security_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Internal Cube Security';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_measures`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_measures` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_measures` (
`idcube_measures` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`measure_name` CHAR(30) NOT NULL ,
`measure_source_calculation` VARCHAR(80) NOT NULL ,
PRIMARY KEY (`idcube_measures`) ,
INDEX `measures_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `measures_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Measure Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_changelog`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_changelog` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_changelog` (
`idcube_changelog` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`change_date` DATE NOT NULL ,
`version` DECIMAL(4,2) NOT NULL ,
`status` ENUM('Development','User Acceptance','Live','Retired') NOT NULL
DEFAULT 'Development' ,
`change_call_work_request_no` INT NOT NULL ,
`change_detail` TEXT NOT NULL ,
`actioned_by` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcube_changelog`) ,
INDEX `changelog_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `changelog_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Changes log';
SHOW WARNINGS;
SET sql_mo...@old_sql_mode;
SET foreign_key_chec...@old_foreign_key_checks;
SET unique_chec...@old_unique_checks;
END SCRIPT--
TIA
John
SOLVED: Re: Foreign Key Issue
Sorry all, I was being a dummy! Missed the unsigned attribute off the
foreign key columns on the problem tables.
Regards
Hi,
I have the script below to create 5 tables. Three of them create fine but
two return an error of 150 which I understand to be a foreign key issue,
however I can't see anything wrong with the foreign key statements.
Could someone possibly have a look and see if they can identify the issue
please? I think I have been looking at it so long now 'I can't see the
wood for the trees'.
The two tables which fail are `cube_security` and `cube_measures`. Im
running MySQL 5.1.32 on a Windows XP test machine.
SCRIPT--
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `cubedoc` DEFAULT CHARACTER SET latin1 COLLATE
latin1_general_ci ;
SHOW WARNINGS;
USE `cubedoc`;
-- -
-- Table `cubedoc`.`cubes`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cubes` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cubes` (
`idcubes` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`cube_name` CHAR(30) NOT NULL ,
`cube_kit_location` VARCHAR(100) NOT NULL DEFAULT 'On Development Pc' ,
`Developer` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcubes`) )
ENGINE = InnoDB
COMMENT = 'General Cube Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_dimensions`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_dimensions` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_dimensions` (
`idcube_dimensions` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`dimension_name` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
PRIMARY KEY (`idcube_dimensions`) ,
INDEX `dimensions_cubes_fk` (`idcubes` ASC) ,
CONSTRAINT `dimensions_cubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Dimension Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_security`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_security` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_security` (
`idcube_security` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`dimension` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
`restricted_user_group` CHAR(30) NOT NULL ,
`restriction_details` TEXT NOT NULL ,
PRIMARY KEY (`idcube_security`) ,
INDEX `security_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `security_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Internal Cube Security';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_measures`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_measures` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_measures` (
`idcube_measures` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`measure_name` CHAR(30) NOT NULL ,
`measure_source_calculation` VARCHAR(80) NOT NULL ,
PRIMARY KEY (`idcube_measures`) ,
INDEX `measures_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `measures_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Measure Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_changelog`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_changelog` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_changelog` (
`idcube_changelog` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`change_date` DATE NOT NULL ,
`version` DECIMAL(4,2) NOT NULL ,
`status` ENUM('Development','User Acceptance','Live','Retired') NOT NULL
DEFAULT 'Development' ,
`change_call_work_request_no` INT NOT NULL ,
`change_detail` TEXT NOT NULL ,
`actioned_by` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcube_changelog`) ,
INDEX `changelog_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `changelog_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
Re: SOLVED: Re: Foreign Key Issue
I had the same problem and was going crazy,
the ket/foreign key fields must be exactly the same.
I 'forward' engineered the database with MySQL Workbench and was almost
posting a bug!
Cheers
Claudio
2009/3/26 John Daisley john.dais...@mypostoffice.co.uk
Sorry all, I was being a dummy! Missed the unsigned attribute off the
foreign key columns on the problem tables.
Regards
Hi,
I have the script below to create 5 tables. Three of them create fine but
two return an error of 150 which I understand to be a foreign key issue,
however I can't see anything wrong with the foreign key statements.
Could someone possibly have a look and see if they can identify the issue
please? I think I have been looking at it so long now 'I can't see the
wood for the trees'.
The two tables which fail are `cube_security` and `cube_measures`. Im
running MySQL 5.1.32 on a Windows XP test machine.
SCRIPT--
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
CREATE SCHEMA IF NOT EXISTS `cubedoc` DEFAULT CHARACTER SET latin1
COLLATE
latin1_general_ci ;
SHOW WARNINGS;
USE `cubedoc`;
-- -
-- Table `cubedoc`.`cubes`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cubes` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cubes` (
`idcubes` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`cube_name` CHAR(30) NOT NULL ,
`cube_kit_location` VARCHAR(100) NOT NULL DEFAULT 'On Development Pc' ,
`Developer` ENUM('John Daisley','Daxa Everitt','Dave Hartley','Rob
Dando','Will Morley') NOT NULL ,
PRIMARY KEY (`idcubes`) )
ENGINE = InnoDB
COMMENT = 'General Cube Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_dimensions`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_dimensions` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_dimensions` (
`idcube_dimensions` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`dimension_name` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
PRIMARY KEY (`idcube_dimensions`) ,
INDEX `dimensions_cubes_fk` (`idcubes` ASC) ,
CONSTRAINT `dimensions_cubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Dimension Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_security`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_security` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_security` (
`idcube_security` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`dimension` CHAR(30) NOT NULL ,
`level_name` CHAR(30) NOT NULL ,
`restricted_user_group` CHAR(30) NOT NULL ,
`restriction_details` TEXT NOT NULL ,
PRIMARY KEY (`idcube_security`) ,
INDEX `security_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `security_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Internal Cube Security';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_measures`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_measures` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_measures` (
`idcube_measures` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT NOT NULL ,
`measure_name` CHAR(30) NOT NULL ,
`measure_source_calculation` VARCHAR(80) NOT NULL ,
PRIMARY KEY (`idcube_measures`) ,
INDEX `measures_idcubes_fk` (`idcubes` ASC) ,
CONSTRAINT `measures_idcubes_fk`
FOREIGN KEY (`idcubes` )
REFERENCES `cubedoc`.`cubes` (`idcubes` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB
COMMENT = 'Cube Measure Data';
SHOW WARNINGS;
-- -
-- Table `cubedoc`.`cube_changelog`
-- -
DROP TABLE IF EXISTS `cubedoc`.`cube_changelog` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `cubedoc`.`cube_changelog` (
`idcube_changelog` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`idcubes` SMALLINT UNSIGNED NOT NULL ,
`change_date` DATE NOT NULL ,
`version` DECIMAL(4,2) NOT NULL ,
`status` ENUM('Development','User Acceptance','Live','Retired') NOT
NULL
DEFAULT 'Development
Algorithm for resolving foreign key dependencies?
Hi, First of all, I apologise in advance for any mind-altering, or headache-inducing effects this question may have. I've spent the past two days trying to figure it out, and all I've got to show for it is a mostly-working recursive depth-first-search routine and an empty packet of painkillers. MySQL version: 5.0.67-0ubuntu6 I'm trying to write a code generator (in Python) that reads in a MySQL database, enumerates all the tables, then produces INSERT, DELETE and UPDATE code in PHP. The INSERT and UPDATE code generation was fairly easy, and works quite well. What I'm having trouble with is the DELETE code generator -- more specifically, resolving foreign key references. Basically, what I have is a tree built in memory, so I can go: tableinfo['thetable']['fieldname']['refs'] And get a complete list of all the tables (and the fields within that table) that reference 'fieldname' in 'thetable'. What I want is an answer to the question: If all my foreign keys were set to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table 'Y' without violating any foreign key constraints? Here's an example. Let's say I've got these tables: CREATE TABLE `Manufacturers` ( `idManufacturer` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`idManufacturer`) ) ENGINE=InnoDB CREATE TABLE `Parts` ( `idPart` int(11) NOT NULL auto_increment, `idManufacturer` int(11) NOT NULL, `partnumber` int(11) NOT NULL, PRIMARY KEY (`idPart`), KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`), CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES `Manufacturers` (`idManufacturer`) ) ENGINE=InnoDB And my database contains: Manufacturers: idManufacturername 123 Any Company Inc. Parts: idPart idManufacturer partnumber 1 123 12345 Now, let's say I want to do this: DELETE FROM Manufacturers WHERE idManufacturer=123 Because I have a part that references Manufacturer #123, I have to do this instead: DELETE FROM Parts WHERE idManufacturer=123 DELETE FROM Manufacturer WHERE idManufacturer=123 What I want is something I can feed the table definitions to, and the name of the table I want to delete a row from (in this case 'Manufacturers'), and generate a list of the DELETE commands that would allow me to delete that row while enforcing FK dependencies. I figure this is going to have to work something like mathematical expression evaluation -- build up a list of dependencies, then deal with the deepest dependency first. Catch being I can't see an obvious way to deal with generating the necessary DELETE commands without having to write a massive if recursion_level = 0 then generate_a_straight_delete else if recursion_level = 1 then... statement... Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Try looking at the information_schema.KEY_COLUMN_USAGE table (where referenced_table_schema is not null). It will show you the FK relationships. You could then create a tree that you could use to find the hierarchy. For that, I suggest looking at http://www.artfulsoftware.com/infotree/mysqlquerytree.php. The information_schema table is already sort of an edge-list, although each node is made up of the tuple (table_schema, table_name, column_name) or (referenced_table_schema, referenced_table_name, referenced_column_name). Donna news n...@ger.gmane.org wrote on 02/03/2009 05:38:34 PM: Andy Shellam wrote: Am I missing something here? (It is late after a long day, I admit!) Only something I forgot to mention. All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's response to a foreign key violation is to spit out an error message to the effect of I'm sorry, Dave, I can't let you do that. The problem is, the target platform doesn't use foreign keys for performance reasons. I want to use foreign keys in development as a bug-trappingmethod -- I'd rather see an FK violation error in development than get an angry email from a customer asking why there's a part listed that doesn't seem to have a manufacturer. The plan was to write a code-generator that would generate all the database code for me, then I could deal with the page templates and display logic myself (thus eliminating ~80% of the boring, repetitive work). I want the generated code to handle foreign keys itself, rather than relying on the database. As I said above, if foreign key constraints didn't slow things down markedly, I'd use them in production. Based on the (admittedly limited) testing I've done, application-side FK enforcement is considerably faster than using ON DELETE CASCADE and letting MySQL deal with the foreign keys. I don't like writing database code by hand (it all follows a standard template), so I figured I'd write a program to do it for me. Work smarter not harder and all that :) Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? unsub=ddevaudre...@intellicare.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Sounds like you want to walk tables in order of their fk dependencies - a topological ordering. You might want to take a look at SQLAlchemy which has some methods to do just this in sqlalchemy.sql.util: def sort_tables(tables, reverse=False): sort a collection of Table objects in order of their foreign-key dependency. ~Andrew On Tue, Feb 3, 2009 at 3:40 PM, Philip Pemberton usene...@philpem.me.uk wrote: Hi, First of all, I apologise in advance for any mind-altering, or headache-inducing effects this question may have. I've spent the past two days trying to figure it out, and all I've got to show for it is a mostly-working recursive depth-first-search routine and an empty packet of painkillers. MySQL version: 5.0.67-0ubuntu6 I'm trying to write a code generator (in Python) that reads in a MySQL database, enumerates all the tables, then produces INSERT, DELETE and UPDATE code in PHP. The INSERT and UPDATE code generation was fairly easy, and works quite well. What I'm having trouble with is the DELETE code generator -- more specifically, resolving foreign key references. Basically, what I have is a tree built in memory, so I can go: tableinfo['thetable']['fieldname']['refs'] And get a complete list of all the tables (and the fields within that table) that reference 'fieldname' in 'thetable'. What I want is an answer to the question: If all my foreign keys were set to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table 'Y' without violating any foreign key constraints? Here's an example. Let's say I've got these tables: CREATE TABLE `Manufacturers` ( `idManufacturer` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`idManufacturer`) ) ENGINE=InnoDB CREATE TABLE `Parts` ( `idPart` int(11) NOT NULL auto_increment, `idManufacturer` int(11) NOT NULL, `partnumber` int(11) NOT NULL, PRIMARY KEY (`idPart`), KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`), CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES `Manufacturers` (`idManufacturer`) ) ENGINE=InnoDB And my database contains: Manufacturers: idManufacturername 123 Any Company Inc. Parts: idPart idManufacturer partnumber 1 123 12345 Now, let's say I want to do this: DELETE FROM Manufacturers WHERE idManufacturer=123 Because I have a part that references Manufacturer #123, I have to do this instead: DELETE FROM Parts WHERE idManufacturer=123 DELETE FROM Manufacturer WHERE idManufacturer=123 What I want is something I can feed the table definitions to, and the name of the table I want to delete a row from (in this case 'Manufacturers'), and generate a list of the DELETE commands that would allow me to delete that row while enforcing FK dependencies. I figure this is going to have to work something like mathematical expression evaluation -- build up a list of dependencies, then deal with the deepest dependency first. Catch being I can't see an obvious way to deal with generating the necessary DELETE commands without having to write a massive if recursion_level = 0 then generate_a_straight_delete else if recursion_level = 1 then... statement... Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=andrew.b.gar...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Andy Shellam wrote: Am I missing something here? (It is late after a long day, I admit!) Only something I forgot to mention. All the foreign keys are set up as ON DELETE RESTRICT, meaning MySQL's response to a foreign key violation is to spit out an error message to the effect of I'm sorry, Dave, I can't let you do that. The problem is, the target platform doesn't use foreign keys for performance reasons. I want to use foreign keys in development as a bug-trapping method -- I'd rather see an FK violation error in development than get an angry email from a customer asking why there's a part listed that doesn't seem to have a manufacturer. The plan was to write a code-generator that would generate all the database code for me, then I could deal with the page templates and display logic myself (thus eliminating ~80% of the boring, repetitive work). I want the generated code to handle foreign keys itself, rather than relying on the database. As I said above, if foreign key constraints didn't slow things down markedly, I'd use them in production. Based on the (admittedly limited) testing I've done, application-side FK enforcement is considerably faster than using ON DELETE CASCADE and letting MySQL deal with the foreign keys. I don't like writing database code by hand (it all follows a standard template), so I figured I'd write a program to do it for me. Work smarter not harder and all that :) Thanks, -- Phil. usene...@philpem.me.uk http://www.philpem.me.uk/ If mail bounces, replace 08 with the last two digits of the current year. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Algorithm for resolving foreign key dependencies?
Hi Philip, Am I missing something here? (It is late after a long day, I admit!) In the example case you've given, if the foreign key in Parts is set to ON DELETE CASCADE, and you delete a row from Manufacturer, MySQL will first delete the associated records in Parts before deleting the row from Manufacturer - all you have to do is issue the DELETE FROM Manufacturer WHERE query - MySQL will handle the rest. If Parts was also the primary key table for another table (e.g. PartNumbers) and that relationship was set to ON DELETE CASCADE, and you issued the DELETE FROM Manufacturer... query, MySQL would delete the associated records from all 3 tables automatically. The only time you'd have to manually issue all 3 DELETE statements were if your foreign keys didn't have the ON DELETE CASCADE option set. Andy Philip Pemberton wrote: Hi, First of all, I apologise in advance for any mind-altering, or headache-inducing effects this question may have. I've spent the past two days trying to figure it out, and all I've got to show for it is a mostly-working recursive depth-first-search routine and an empty packet of painkillers. MySQL version: 5.0.67-0ubuntu6 I'm trying to write a code generator (in Python) that reads in a MySQL database, enumerates all the tables, then produces INSERT, DELETE and UPDATE code in PHP. The INSERT and UPDATE code generation was fairly easy, and works quite well. What I'm having trouble with is the DELETE code generator -- more specifically, resolving foreign key references. Basically, what I have is a tree built in memory, so I can go: tableinfo['thetable']['fieldname']['refs'] And get a complete list of all the tables (and the fields within that table) that reference 'fieldname' in 'thetable'. What I want is an answer to the question: If all my foreign keys were set to 'ON DELETE CASCADE', what would I need to do to delete row 'X' in table 'Y' without violating any foreign key constraints? Here's an example. Let's say I've got these tables: CREATE TABLE `Manufacturers` ( `idManufacturer` int(11) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`idManufacturer`) ) ENGINE=InnoDB CREATE TABLE `Parts` ( `idPart` int(11) NOT NULL auto_increment, `idManufacturer` int(11) NOT NULL, `partnumber` int(11) NOT NULL, PRIMARY KEY (`idPart`), KEY `Parts_idManufacturer_FKIndex` (`idManufacturer`), CONSTRAINT `Parts_ibfk_1` FOREIGN KEY (`idManufacturer`) REFERENCES `Manufacturers` (`idManufacturer`) ) ENGINE=InnoDB And my database contains: Manufacturers: idManufacturername 123 Any Company Inc. Parts: idPart idManufacturer partnumber 1 123 12345 Now, let's say I want to do this: DELETE FROM Manufacturers WHERE idManufacturer=123 Because I have a part that references Manufacturer #123, I have to do this instead: DELETE FROM Parts WHERE idManufacturer=123 DELETE FROM Manufacturer WHERE idManufacturer=123 What I want is something I can feed the table definitions to, and the name of the table I want to delete a row from (in this case 'Manufacturers'), and generate a list of the DELETE commands that would allow me to delete that row while enforcing FK dependencies. I figure this is going to have to work something like mathematical expression evaluation -- build up a list of dependencies, then deal with the deepest dependency first. Catch being I can't see an obvious way to deal with generating the necessary DELETE commands without having to write a massive if recursion_level = 0 then generate_a_straight_delete else if recursion_level = 1 then... statement... Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
