CODEWITHSUNDEEP
phpmysqlsqlforeign-keys
Nicolas Thery
Nicolas Thery

Reputation: 2448

SQL Delete with 1 to 1 relation

My database is MySQL with foreign key management. It has 2 tables :

Table1 :
|id|foreignKeyToTable2|data...|

Table2 : 
|id|foreignKeyToTable1|data...|

I want to delete a couple of these lines but when I delete one of them I have the meaningful foreign key error.

How can I delete the couple of lines in one time?

Am I forced to disable foreign key checks?

Edit : Problem with delete cascade

I need both of the keys because there is actually 2 relations. Lets be more practical :

Tables are :

DDL
|id|name|defaultValue (FK delete cascade)|
Value
|id|name|DDLiD (FK)|

Following your answers, I added a DELETE CASCADE to the DDL table. But I still have a FK error .

Cannot delete or update a parent row: a foreign key constraint fails (Value, CONSTRAINT fk_Value_DDL FOREIGN KEY (DDL) REFERENCES DDL (id) ON DELETE NO ACTION ON UPDATE NO ACTION)

I don't want to add a delete cascade on the Value table because i want to be able to delete a value.

Upvotes: 3

Views: 4357

Answers (6)

Abdullah Tahan
Abdullah Tahan

Reputation: 2129

lets assume we have two tables foo has Id | Name | carId

bar has Id | Color

to delete both tables 

delete bar where id in (select f.Id from foo f inner join boo b on b.Id = f.Id where f.carId = 22)

Upvotes: 0

Tathagat Verma
Tathagat Verma

Reputation: 548

Using a cascade delete / update should handle the necessary.

I've got an alternative suggestion though, which is more of an architectural solvent/resolve.

  • By design: rather than deleting data, always deactivate it
  • You could keep a column: Status | BIT | Default(1)
  • This column may or may not be present in the secondary table
  • By code-design all you've got to handle is, when you get (all SELECT queries) secondary data always put the condition: PrimaryTable.Status = 1 when joining with the primary table
  • Doing so will eliminate the need to apply constraints on the primary-table design

Upvotes: 1

H-Man2
H-Man2

Reputation: 3189

You have two options here:

  1. Alter the foreign key definition to also delete referenced rows. This can be done with ON DELETE CASCADE.
  2. Use a transaction around the two delete statements.

The second option is my prefered one, because a database user is informed about the usage in the other table and can then decide to use a transaction to remove data from both rows.

Also, one usage scenario of a 1:1 relation is to allow different permissions on both tables. When using delete cascade I don't know if these are checked.

EDIT

After I was pointed that MySQL is handling foreign key checking not as stated in the SQL standards, my second option is changed to:

2. Because MySQL does not support deferred checking of foreign key constraints while using transactions (this is a deviation from SQL standards), you need to disable foreign key checks for the delete statements:

    SET foreign_key_checks = 0;
    DELETE ...;
    SET foreign_key_checks = 1;

Upvotes: 4

Nicolas Thery
Nicolas Thery

Reputation: 2448

The solution I found is to remove one of the foreign keys and manage it with the application. tables are now :

DDL
|id|name|defaultValue|
Value
|id|name|DDLiD (FK)|

defaultValue is an id of the table Value but not a foreign key...

When I want to delete, first I delete the values, then I delete the DDL.

Thank you all.

Nicolas.

Upvotes: 0

Johan
Johan

Reputation: 76567

You need to set the foreign key to cascade mode:

CREATE TABLE table1 (
  fields....
  FOREIGN KEY fk_name(fk_to_table2) REFERENCES table2.id  
  ON DELETE CASCADE ON UPDATE CASCADE   <<--- Change this.

If you change the table layout like this, MySQL will not block, but instead propagate the deletion.

Note that if you have a 1-to-1 relation, you do not have a separate foreign key field, but your primary key links to another primary key, like so:

CREATE TABLE t1 (
  id UNSIGNED INTEGER AUTO_INCREMENT PRIMARY KEY,
  otherfields....,
) ENGINE = InnoDB;  <<-- no foreign key in T1, because t1 is the master.


CREATE TABLE t2 (
  id UNSIGNED INTEGER AUTO_INCREMENT PRIMARY KEY,
  otherfields....,
  FOREIGN KEY fk_t1_id(id) REFERENCES t1(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;  <<-- t2 follows t1.

Now you enforce a 1-1 relationship, instead of a 1-n.

Note that setting NO ACTION is the same as RESTRICT: http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. InnoDB rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

Upvotes: 2

Rahul
Rahul

Reputation: 77876

Use on delete cascade while creating your table

Upvotes: 0

Related Questions