self relations... (what??!!!)
Posted by:
info
Date: March 16, 2005 10:48AM
Hi, i'm from spain, excuse languaje :)
when i have this table:
CREATE TABLE p (
id int( 11 ) NOT NULL auto_increment ,
id_p int( 11 ) NOT NULL default '0' ,
PRIMARY KEY( id ) ,
INDEX( id_p )
FOREIGN KEY ( id_p )
REFERENCES p( id )
ON DELETE NO ACTION
ON UPDATE NO ACTION
) TYPE=InnoDB
I can insert data in p with
"insert into p ( id , id_p ) values ( 1 , 1 )"
"insert into p ( id , id_p ) values ( 2 , 2 )"
"insert into p ( id , id_p ) values ( 3 , 3 )"
"insert into p ( id , id_p ) values ( 4 , 1 )"
But i only can delete (4,1): when i do
"delete from p where id = 1", or
"delete from p where id = 2", or
"delete from p where id = 3"
MySQL says "can't delete because there is a constraint"....
What constraint???
Is there any problem in MySQL with self relations??
Thnx very :)