Link to home
Start Free TrialLog in
Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil

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?


ALTER TABLE system_reclamacao
ADD FOREIGN KEY id_fk_seguradora(fk_seguradora)
REFERENCES system_seguradora(id_seguradora)
ON DELETE ????
ON UPDATE CASCADE;

Open in new window


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
Avatar of _agx_
_agx_
Flag of United States of America image

Try ON DELETE RESTRICT .  Though IIRC, that is the default anyway.
Avatar of Eduardo Fuerte

ASKER

Hi

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;

Open in new window


TABLE system_seguradora and its PK (41)
User generated image
TABLE system_reclamacao - uses the FK (41)
User generated image
But the deletion has no restrictions:

DELETE FROM system_seguradora WHERE id_seguradora=41;

Open in new window


User generated image
That's the point: I need it to be restricted.
Is the db type InnoDB?  This test case worked fine for me:

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;

Open in new window


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)
Hi _agx_


No, ENGINE = MYISAM

And the tables already exists, with data.
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
After some tests I concluded that MYISAM tables couldn't accept referential integrity, isnt 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;

Open in new window



In  MYISAM tables:
User generated image
In INNODB tables:
User generated image
(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.
Hi _agx_

Our messages crossed!!!

Almost same conclusion.
Thank you for the help!