foreign key

[filipel]

Hi.I have a record in this table with id=0:

DROP TABLE IF EXISTS `junta`.`modelo`;
CREATE TABLE `junta`.`modelo` (
`id` int(10) unsigned NOT NULL default '0',
`foto1` varchar(200) default '',
`descri1` varchar(500) default '',
`foto2` varchar(200) default '',
`descri2` varchar(500) default '',
`foto3` varchar(200) default '',
`descri3` varchar(500) default '',
`foto4` varchar(200) default '',
`descri4` varchar(500) character set latin1 collate latin1_bin default '',
`modelo` int(10) unsigned NOT NULL default '0',
`logo` varchar(200) NOT NULL default '',
`novidadesID` int(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I have another table like this with a foreign key that references id on first table:

DROP TABLE IF EXISTS `junta`.`subdominios`;
CREATE TABLE `junta`.`subdominios` (
`id` int(10) unsigned NOT NULL auto_increment,
`nome` varchar(45) NOT NULL default '',
`descricao` varchar(45) NOT NULL default '',
`modelo` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
CONSTRAINT `FK_subdominios_1` FOREIGN KEY (`id`) REFERENCES `modelo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

when i try to insert this record:

insert into subdominios(nome,descricao,modelo) values('nome','descri',0)

i get this error:

Cannot add or update a child row: a foreign key constraint fails (`junta/subdominios`, CONSTRAINT `FK_subdominios_1` FOREIGN KEY (`id`) REFERENCES `modelo` (`id`))

Why?

 

[r937]

because you have (incorrectly) linked the auto_increment id

change this --

CONSTRAINT FK_subdominios_1 FOREIGN KEY (id) REFERENCES modelo (id)

to this --

CONSTRAINT FK_subdominios_1 FOREIGN KEY (modelo) REFERENCES modelo (id)

and please, stop using those backticks, they just clutter everything up



r937.com | rudy.ca

 

[filipel]

i have just copied the sql script from mysql query browser!!

 

[r937]

well, it's wrong, somehow you ended up trying to link the wrong column

r937.com | rudy.ca

 

[ESquared]

Rudy's right that something is wrong. You have an auto increment column that's a foreign key. So either you're using the wrong foreign key column as he says, or the auto increment should be on the other table, or the foreign key constraint belongs in the other table.

I rather think it's the last case.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]