MySQL: delete a row ignoring foreign key constraint

So I am working on a few tables and there are some data inconsistency between them... One or two tables have a foreign key constraint on a particular table (call it table X), but that table has multiple rows with the foreign key column.

What I want to do is to remove the duplicated rows in table X, but the foreign key constraint is preventing me from doing this. Is there a way to force delete the rows while ignoring the foreign key constraint since I know what I'm doing?

 Answers

SET foreign_key_checks = 0

That will prevent MySQL from checking foreign keys. Make sure to set it back to 1 when you are done though.

Also, you could always drop the foreign key and then add it later if you wanted to only affect a singular key

ALTER TABLE t DROP FOREIGN KEY fk

Mysql: Delete all foreign keys in database

I would like to rename a column in a table that is a foreign key to many tables. 

Apparently this is only possible if you delete the constraints.

I dont want to delete all the constraints manually is there a way to delete all the foreign key constraints in the database?

I have also tried SET FOREIGN_KEY_CHECKS=0; but I still cant rename the column.

 Answers

Executing the following query

select * from information_schema.key_column_usage

will show you all the constraints (with the column name, constraint type, table and schema) that exist in your database. You'll notice these columns:

CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
POSITION_IN_UNIQUE_CONSTRAINT
REFERENCED_TABLE_SCHEMA
REFERENCED_TABLE_NAME
REFERENCED_COLUMN_NAME

Then, if you're planning to delete each constraint you have referencing your column, you should consider the REFERENCED_* columns and run something like:

DELETE FROM information_schema.key_column_usage 
WHERE 
    REFERENCED_TABLE_SCHEMA='myschema'
    AND
    REFERENCED_TABLE_NAME='mytable'
    AND
    REFERENCED_COLUMN_NAME='mycolumn'

http://dev.mysql.com/doc/refman/5.1/en/key-column-usage-table.html

---

You Can Try using As Like of Following ..

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName);

Also you can try with Reference Key.As like .....

ALTER TABLE tableName
DROP FOREIGN KEY fieldName;
ADD FOREIGN KEY (newForignKeyFieldName)
REFERENCES anotherTableName(reference_id);

Mysql: Foreign key constraints: When to use ON UPATE and ON DELETE

I'm designing my database schema using MySQL Workbench, which is pretty cool because you can do diagrams and it converts them :P

Anyways, I've decided to use InnoDB because of it's Foreign Key support. One thing I noticed though is that it allows you to set On Update and on Delete options for foreign keys. Can someone explain where "Restrict", "Cascade" and set null could be used in a simple example?

For example, say I have a user table which includes a userID. And say I have a message table message which is a many-to-many which has 2 foreign keys (which reference the same primary key, userID in the user table). Is setting the On Update and On Delete options any useful in this case? If so, which one do I choose? If this isn't a good example, could you please come up with a good example to illustrate how these could be useful?

Answers

Do not hesitate to put constraints on the database. You'll be sure to have a consistent database, and that's one of the good reasons to use a database. Especially if you have several applications requesting it (or just one application but with a direct mode and a batch mode using different sources).

With MySQL you do not have advanced constraints like you would have in postgreSQL but at least the foreign key constraints are quite advanced.

We'll take an example, a company table with a user table containing people from theses company

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

Let's look at the ON UPDATE clause:

• ON UPDATE RESTRICT : the default : if you try to update a company_id in table COMPANY the engine will reject the operation if one USER at least links on this company.
• ON UPDATE NO ACTION : same as RESTRICT.
• ON UPDATE CASCADE : the best one usually : if you update a company_id in a row of table COMPANY the engine will update it accordingly on all USER rows referencing this COMPANY (but no triggers activated on USER table, warning). The engine will track the changes for you, it's good.
• ON UPDATE SET NULL : if you update a company_id in a row of table COMPANY the engine will set related USERs company_id to NULL (should be available in USER company_id field). I cannot see any interesting thing to do with that on an update, but I may be wrong.

And now on the ON DELETE side:

• ON DELETE RESTRICT : the default : if you try to delete a company_id Id in table COMPANY the engine will reject the operation if one USER at least links on this company, can save your life.
• ON DELETE NO ACTION : same as RESTRICT
• ON DELETE CASCADE : dangerous : if you delete a company row in table COMPANY the engine will delete as well the related USERs. This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANY<->USER example)
• ON DELETE SET NULL : handful : if you delete a COMPANY row the related USERs will automatically have the relationship to NULL. If Null is your value for users with no company this can be a good behavior, for example maybe you need to keep the users in your application, as authors of some content, but removing the company is not a problem for you.

usually my default is: ON DELETE RESTRICT ON UPDATE CASCADE. with some ON DELETE CASCADE for track tables (logs--not all logs--, things like that) and ON DELETE SET NULL when the master table is a 'simple attribute' for the table containing the foreign key, like a JOB table for the USER table.

Edit

It's been a long time since I wrote that. Now I think I should add one important warning. MySQL has one big documented limitation with cascades. Cascades are not firing triggers. So if you were over confident enough in that engine to use triggers you should avoid cascades constraints.

• http://dev.mysql.com/doc/refman/5.6/en/triggers.html

MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==> See below the last edit, things are moving on this domain

Triggers are not activated by foreign key actions.

And I do not think this will get fixed one day. Foreign key constraints are managed by the InnoDb storage and Triggers are managed by the MySQL SQL engine. Both are separated. Innodb is the only storage with constraint management, maybe they'll add triggers directly in the storage engine one day, maybe not.

But I have my own opinion on which element you should choose between the poor trigger implementation and the very useful foreign keys constraints support. And once you'll get used to database consistency you'll love PostgreSQL.

12/2017-Updating this Edit about MySQL:

as stated by @IstiaqueAhmed in the comments, the situation has changed on this subject. So follow the link and check the real up-to-date situation (which may change again in the future).

---

Addition to @MarkR answer - one thing to note would be that many PHP frameworks with ORMs would not recognize or use advanced DB setup (foreign keys, cascading delete, unique constraints), and this may result in unexpected behaviour.

For example if you delete a record using ORM, and your DELETE CASCADE will delete records in related tables, ORM's attempt to delete these related records (often automatic) will result in error.

MySQL: Delete records in one table that are not in another

Last week I looked at how to find records in a table with MySQL that are not in another table and this week look at how to delete records in a table with MySQL that are not in another table.

Example tables

The examples below are the same as in last week's post and use three tables as follows:

content: contains the content pages for a website. The primary key is content_id.

tags: the "tags" that a page is tagged with. The primary key is tag_id.

content_to_tags: a table that creates a many-to-many relationship between the above two tables; a page can belong to multiple tags.

Example

This example deletes records in content_to_tags that have no associated record in content. This could have happened if the application deleted a record from content but didn't delete the associated records from content_to_tags.

DELETE FROM content_to_tags
WHERE NOT EXISTS (
    SELECT *
    FROM content
    WHERE content_id = content_to_tags.content_id
)

In a properly ACID compliant database with foreign key constraints there shouldn't be any records in content_to_tagsthat aren't in content but if you haven't set up the constraints (when using INNODB) or are using MyISAM tables then it's quite possible for this to have happened.

Table aliasing

Note that you can't use table aliasing and must use the full table name in the NOT EXISTS part of the query. The following example won't work (the aliased table name is in red):

DELETE FROM content_to_tags ctt
WHERE NOT EXISTS (
    SELECT *
    FROM tags
    WHERE tag_id = ctt.tag_id
)

This will result in the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ctt
WHERE NOT EXISTS (
SELECT *
FROM tags
WHERE tag_id = ctt.tag_id
)' at line 1

You probably wouldn't have done this yourself, but I did when I was testing out the queries for this article and got that error so thought it best to share :)

Related posts:

• MySQL: Find records in one table that are not in another
• Delete All Data in a MySQL Table
• Cross Table Delete with MySQL
• Cross Table Update with MySQL

Delete All Data in a MySQL Table

MySQL is the world's most popular open source database, recognized for its speed and reliability. This article looks at how to delete all the data from a MySQL database table and how it affects auto incremental fields.

Delete and Truncate

There are two ways to delete all the data in a MySQL database table.

TRUNCATE TABLE tablename; This will delete all data in the table very quickly. In MySQL the table is actually dropped and recreated, hence the speed of the query. The number of deleted rows for MyISAM tables returned is zero; for INNODB it returns the actual number deleted.

DELETE FROM tablename; This also deletes all the data in the table, but is not as quick as using the "TRUNCATE TABLE" method. In MySQL >= 4.0 the number of rows deleted is returned; in MySQL 3.23 the number returned is always zero.

Auto Increment Columns for MyISAM Tables

If you have an auto increment primary key column in your MyISAM table the result will be slightly different depending which delete method you use. When using the "TRUNCATE TABLE" method the auto increment seed value will be reset back to 1. When using the "DELETE FROM" method the auto increment seed will be left as it was before (eg if the auto increment field of last inserted record was 123 the next inserted record will be set to 124).

Note that this is true for MySQL >= 4.0; from my reading of the TRUNCATE manual page in MySQL 3.23 TRUNCATE works just like DELETE which would mean the auto increment seed is not reset. I do not currently have a 3.23 database set up to test it so cannot confirm this.

Auto Increment Columns for INNODB Tables

For INNODB tables, whether you use the "TRUNCATE TABLE" or "DELETE FROM" methods, the auto increment field will not be reset. If you inserted 5 records into a new table, then deleted all records and inserted another record, the field would have a value of 6, regardless of which method you used.

Update 17 Feb 2009: I originally wrote this post when MySQL 4.0 was the current version. I've just tested the above now on an INNODB table using MySQL 5.0 and using TRUNCATE does reset the auto increment field back to the default. So either the behaviour changed at some point or I was incorrect when making the above statement.

Are you really sure you want to delete all data?

Before deleting all the data in a database you should make sure you really intend to delete all the data. It often pays first to "SELECT * FROM tablename" or "SELECT COUNT(*) FROM tablename" before doing so to check that it really is safe to delete all data. Maybe you really want to do something like "DELETE FROM tablename WHERE foo = 'bar'" instead.

Cross Table Delete with MySQL

Deleting records with MySQL can be done by referencing records in another table by joining them together. This is useful if you need to delete data from one table based on the values in another, or if you want to delete records from one table where there are no associated records in the second table. Note that although the examples in this article show joins between two tables you can join three, four or more tables if required.

Using a join to delete records in MySQL is only possible with version 4.0 or higher. Unfortunately this is not possible using earlier versions of MySQL, ie 3.23 and earlier.

With MySQL you can do a cross table delete in one of two ways. The first is to use commas with an implicit inner join like in the example below. In these examples we're using a product and productPrice table where product info is stored in the product table and price information in the productPrice table. Each table has a productId field which is what we'll be joining them on.

DELETE product.*, productPrice.*
FROM product p, productPrice pp
WHERE p.productId = pp.productId
AND p.created < '2004-01-01'

The second way is to use "inner join" syntax as in the example below. This is my own personal preference for how to join tables together as it keeps the join conditions with the join statement instead of burying it in the where clause.

DELETE product.*, productPrice.*
FROM product p
INNER JOIN productPrice pp
ON p.productId = pp.productId
WHERE p.created < '2004-01-01'

Note that you don't necessarily need to delete all records from all tables in the query. The example above could just delete from the productPrice table by changing the first line to delete product.*.

You can also use a left join to delete records using MySQL. An example of this is using our product and productPrice tables below, where we are deleting all the records from the product table where there is not an associated record in the productPrice table.

DELETE product.*
FROM product p
LEFT JOIN productPrice pp
ON p.productId = pp.productId
WHERE pp.productId is null

When testing these queries on a test database to make sure they actually executed I discovered it appears you cannot use the alias names for the data to delete (eg delete p.*) otherwise you get the error error 1066 not unique table/alias. To fix this error type in the full table name instead of the alias.

Note there is another article about how to update records with MySQL using a cross join which uses similar examples to update instead of delete.

php delete sql query does not work

     <?php
      include('session.php');
         ?>

       <?php
       $conn = new mysqli("127.0.0.1","root","","foo");
    if ($conn->connect_errno) {
       echo "Failed to connect to MySQL: (" . $conn->connect_errno . ") " .           $conn->connect_error;
    }
  $sew = $_SESSION['login_user'];
  $a=$_GET["en"];
  $l=1;
  $d= -1;

   if($a==1)
  {
     $sqlw = " INSERT into dlkeuser VALUES('$a','$sew')" ;

   if ($conn->query($sqlw) === FALSE)
   {
 echo "you have already disliked the song";

  }
 else
  {
 //query1
   $sql  = " DELETE FROM lkeuser WHERE userid = '$sew' AND songid = '$a' " ;

 //query2
       $sql = "UPDATE liking
       SET count = count - 1 ";

       if ($conn->query($sql) === TRUE) {

        echo "you disliked the song";

       }
   else {
          echo "Error: " . $sql . "<br>" . $conn->error;
  }

    }

In this php code snippet , query1 is not working whereas query 2 is fine . I am trying to insert (songid,userid) in dlkeuser(dislike) table against user i/p($_GET["en"]) and delete the record(songid,userid) from lkeuser(like) table if it exists. (songid,userid) pair is the composite primary key here. "count" is the net like/dislike of a song. I am new to php . Thanks for any help you can provide.

---

let's try this,

it will work

<?php
      include('session.php');
         ?>

       <?php
       $conn = new mysqli("127.0.0.1","root","","foo");
    if ($conn->connect_errno) {
       echo "Failed to connect to MySQL: (" . $conn->connect_errno . ") " .           $conn->connect_error;
    }
  $sew = $_SESSION['login_user'];
  $a=$_GET["en"];
  $l=1;
  $d= -1;

   if($a==1)
  {
     $sqlw = " INSERT into dlkeuser VALUES('$a','$sew')" ;

   if ($conn->query($sqlw) === FALSE)
   {
 echo "you have already disliked the song";

  }
 else
  {
 //query1
   $sql  = " DELETE FROM lkeuser WHERE userid = '$sew' AND songid = '$a' " ;

 //query2
       $sql1 = "UPDATE liking
       SET count = count - 1 ";

      if ($conn->query($sql) === TRUE) {

        echo "deleted the song";

       } 

       if ($conn->query($sql1) === TRUE) {

        echo "you disliked the song";

       }
   else {
          echo "Error: " . $sql . "<br>" . $conn->error;
  }

    }




Mysql: The SQL subquery does not return what I expect

I have this problem to solve: If two students A and B are friends, and A likes B but not vice-versa, remove the Likes entry.

For background, the Friend table has two columns, STU1 and STU2. If they are friends, then there will be an entry showing STU1, STU2 AND STU2, STU1.

In the Likes table, if Student A likes Student B, there will be an entry for STU1, STU2, but if Student B does not like Student A, there will NOT be an entry for STU2, STU1.

So, here is what I have tried. The problem is that it still leaves two rows in the Likes table that should be out of there. Any ideas on how to solve this on?

delete from Likes
where exists
    (select F.STU1, F.STU2 from Friend F
        where exists
        (select L.STU1, L.STU2 from Likes L, Friend F where
            F.STU1 = L.STU1 and F.STU2 = L.STU2)
        )
    and not exists
        (select L.STU1, L.STU2 from Likes L, Friend F where
            F.STU1 = L.STU2 and F.STU2 = L.STU1)

---

edit:

with onewayfriends as (
select f.* from friend f
left outer join likes l1 on l1.stu1=f.stu1 and l1.stu2=f.stu2
left outer join likes l2 on l2.stu1=f.stu2 and l2.stu2=f.stu1
where l1.stu1 is null or l2.stu1 is null)

delete l from likes l, onewayfriends f
where l.stu1 in (f.stu1, f.stu2) and l.stu2 in (f.stu1, f.stu2)

edit 2, since it's sqllite rewrite the cte as a nested query and the delete with join as a 'where row id in..'

delete from likes where rowid in (select l.rowid from
(
  select f.* from friend f
  left outer join likes l1 on l1.stu1=f.stu1 and l1.stu2=f.stu2
  left outer join likes l2 on l2.stu1=f.stu2 and l2.stu2=f.stu1
  where l1.stu1 is null or l2.stu1 is null
) f, likes l
where l.stu1 in (f.stu1, f.stu2) and l.stu2 in (f.stu1, f.stu2))

Mysql: Delete data from a MySQL database

<?php
/* * Change the first line to whatever
* you use to connect to the database.
* * Change tablename to the name of your 
* database table.
* * This example would delete a row from
* a table based on the id of the row.
* You can change this to whatever you
* want.
*/// Your database connection code
db_connect();
$query = "DELETE FROM tablename WHERE id = ('$id')";
$result = mysql_query($query);
echo "The data has been deleted.";
?>

Mysql: Delete records from multiple tables with one query

Delete records from multiple tables with one query.

CREATE TABLE table_a (
  id INT(11) DEFAULT NULL
);

CREATE TABLE table_b (
  id INT(11) DEFAULT NULL
);

INSERT INTO table_a VALUES 
  (2),
  (3),
  (1);

INSERT INTO table_b VALUES 
  (2),
  (5),
  (1);

Delete records:

DELETE t1, t2
FROM
  table_a t1
JOIN table_b t2
  ON t1.id = t2.id
WHERE
  t1.id = 1;

Check results:

SELECT * FROM table_a;
+------+
| id   |
+------+
|    2 |
|    3 |
+------+

SELECT * FROM table_b;
+------+
| id   |
+------+
|    2 |
|    5 |
+------+