Deletion from parent table question

Databases
1

Groan: having re-read this I think the answer is obvious but I feel a bit unsure.

I’ve got a parent table and a child table.

foreign key constraints are set up correctly I think, using ON DELETE RESTRICT.

I want to deletion from the parent table to be possible only, if the are no related child records.

should my delete statement simply rely on the constraint to decide if deletion is to be effected or should I join on the child table and make sure there are no matching records? (Then, if there is none, perform the deletion)

bazz

2

FK delete restriction is default behavior… just ensure that your FKs are properly defined and that you’re using InnoDB for your engine (the default MyISAM driver doesn’t properly implement FK relationships).

Example:


CREATE TABLE parent_table (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    CONSTRAINT parent_table_pk PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE child_table (
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    CONSTRAINT child_table_pk PRIMARY KEY (id),
    CONSTRAINT child_table_fk_parent_id FOREIGN KEY (parent_id) REFERENCES parent_table(id)
) TYPE=InnoDB;

(Added some constraint names per my convention… I use pk for primary keys, fk_column_name for foreign keys, and ix_column_name for indexes)