Menu
I have two tables
TableOne (TABLE_ONE_ID (pk), TABLE_TWO_ID (fk), ...(something else) )
TableTwo (TABLE_TWO_ID (pk), TABLE_ONE_ID (fk), ...(something else) )
How can I delete records from these tables?
P.S. I think that it is bad design, but this is not my fault and I don't have permission to change the database structure. I just need to know how to delete records from these tables.
640
The syntax also supports deleting rows from multiple tables at once. To delete rows from both tables where there are matching id values, name them both after the DELETE keyword: DELETE t1, t2 FROM t1 INNER JOIN t2 ON t1.id = t2.id; What if you want to delete nonmatching rows?
The FOREIGN KEY constraint differs from the PRIMARY KEY constraint in that, you can create only one PRIMARY KEY per each table, with the ability to create multiple FOREIGN KEY constraints in each table by referencing multiple parent table.
Here, ON DELETE CASCADE is added because when any row is deleted in one table the same gets deleted in the foreign referenced tables that are referencing the primary key in that table.
There are many good foreign key related table manipulation tips, please check out the following: Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation. has anyone found a way to get around the error?
In Foreign key, when an attribute in one table which is a non-primary key references the same attribute which is the primary key in another table then the non-prime key is called the foreign key. We use the foreign key references in order to make the links between the tables and whatever action is done in any of the tables. Attention reader!
One of our requirements is that they run without flaw, for obvious reasons. The first option (and not one I’d chose) is to either disable or drop the foreign key. Then afterwards we add them back.
-- Always delete the children first. Again, this is a pretty simple example, but try thinking about it when you have a whole series of foreign keys. Say half a dozen, or even more, of them. All of a sudden, if you aren’t familiar with the structure of your database it could take some time to get that order right.
You can disable the foreign key with:
alter table TableOne disable constraint fk_table_two_id;
After that, you should be able to delete the rows.
Per DaveCosta's comment, you could defer the constraint checks. That way the constraints are checked over the entire transaction, not each individual SQL statement. For example:
begin transaction;
set constraints all deferred;
delete from TableTwo;
delete from TableOne;
commit transaction;
Either defer one of the foreign keys, or assign NULL (assuming it is NULL-able) to break the cycle.
BTW, how are you inserting the data? You must have done something to break the cycle there too.
NOTE: Disabling or deleting the FK may be OK if just one carefully controlled session is modifying the database, but would leave you wide open for data corruption in a concurrent environment where other clients may not be aware that the FK they expect is no longer enforced.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
