Eduardo Fuerte
asked on
Could you point how to code a MySQL constraint to delete a PK that is in use as FK in another table?
Hi Experts!
Could you point how to code a MySQL constraint to deny delete a PK that is in use as FK in another table?
Table system_reclamacao has a FK from table system_seguradora - I need to avoid an PK from system_seguradora to be deleted.
Thanks in advance
Could you point how to code a MySQL constraint to deny delete a PK that is in use as FK in another table?
ALTER TABLE system_reclamacao
ADD FOREIGN KEY id_fk_seguradora(fk_seguradora)
REFERENCES system_seguradora(id_seguradora)
ON DELETE ????
ON UPDATE CASCADE;
Table system_reclamacao has a FK from table system_seguradora - I need to avoid an PK from system_seguradora to be deleted.
Thanks in advance
Try ON DELETE RESTRICT . Though IIRC, that is the default anyway.
ASKER
Hi
Doing that way:
TABLE system_seguradora and its PK (41)
TABLE system_reclamacao - uses the FK (41)
But the deletion has no restrictions:
That's the point: I need it to be restricted.
Doing that way:
ALTER TABLE system_reclamacao
ADD FOREIGN KEY id_fk_seguradora(fk_seguradora)
REFERENCES system_seguradora(id_seguradora)
ON DELETE RESTRICT
ON UPDATE CASCADE;
TABLE system_seguradora and its PK (41)
TABLE system_reclamacao - uses the FK (41)
But the deletion has no restrictions:
DELETE FROM system_seguradora WHERE id_seguradora=41;
That's the point: I need it to be restricted.
Is the db type InnoDB? This test case worked fine for me:
Result:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`childtable`, CONSTRAINT `childtable_ibfk_1` FOREIGN KEY (`fk_MainTable`) REFERENCES `maintable` (`id`) ON UPDATE CASCADE)
CREATE TABLE MainTable
(
id integer AUTO_INCREMENT primary key
, title varchar(100)
);
CREATE TABLE ChildTable
(
id integer AUTO_INCREMENT primary key
, title varchar(100)
, fk_MainTable INT
)
;
ALTER TABLE ChildTable
ADD FOREIGN KEY id_fk_MainTable(fk_MainTable)
REFERENCES MainTable(id)
ON DELETE RESTRICT
ON UPDATE CASCADE;
INSERT INTO MainTable (id, title) VALUES (1, 'First Value');
INSERT INTO ChildTable (title, fk_MainTable) VALUES ('First Child Value', 1);
DELETE FROM MainTable WHERE id = 1;
Result:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`test`.`childtable`, CONSTRAINT `childtable_ibfk_1` FOREIGN KEY (`fk_MainTable`) REFERENCES `maintable` (`id`) ON UPDATE CASCADE)
ASKER
Hi _agx_
No, ENGINE = MYISAM
And the tables already exists, with data.
No, ENGINE = MYISAM
And the tables already exists, with data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
After some tests I concluded that MYISAM tables couldn't accept referential integrity, isnt it?
After applying the correspondent code:
In MYISAM tables:
In INNODB tables:
(the FK in child table had to be unsigned)
The dependencies are not created when using MYISAM - just an index is created, so I concluded the RI must be programatically done, isn't it?
After applying the correspondent code:
ALTER TABLE system_reclamacao
ADD FOREIGN KEY id_fk_Seguradora(fk_seguradora)
REFERENCES system_seguradora(id_seguradora)
ON DELETE RESTRICT
ON UPDATE CASCADE;
In MYISAM tables:
In INNODB tables:
(the FK in child table had to be unsigned)
The dependencies are not created when using MYISAM - just an index is created, so I concluded the RI must be programatically done, isn't it?
Yes. Not sure if you saw my previous post, but like I mentioned here You have to use InnoDB for FK support. MyISAM doesn't support them.
ASKER
Hi _agx_
Our messages crossed!!!
Almost same conclusion.
Our messages crossed!!!
Almost same conclusion.
ASKER
Thank you for the help!