Skip to main content
28 votes
Accepted

Postgres suggests "Truncate table ... at the same time"

What does it mean to truncate a table at the same time? It means with the same statement. You can truncate more than one tables: TRUNCATE xxxxx, yyyyy RESTART IDENTITY ; More details in Postgres ...
ypercubeᵀᴹ's user avatar
12 votes
Accepted

TSQL: How to DELETE in cascade without altering table / indexes

Perhaps simply do it manually with two deletes, in a transaction to ensure consistency: BEGIN TRANSACTION DELETE T2 WHERE employeeID IN (SELECT employeeID FROM T1 WHERE <...
David Spillett's user avatar
12 votes
Accepted

Why can't we have multiple cascade paths?

This question "what can go wrong" can only be answered by the developers at Microsoft or Sybase. When there are multiple cascade paths to the same record, it's possible that the code might attempt to ...
cliffordheath's user avatar
7 votes

Is there anything wrong with linking two identical primary keys in two different tables?

If you want OrderInfo.OrderID to be a foreign key that references OrderNumber.OrderID, then you have to declare it just as INTEGER not as an INTEGER IDENTITY. If it were an identity, you couldn't ...
joanolo's user avatar
  • 13.5k
7 votes

Is there anything wrong with linking two identical primary keys in two different tables?

It looks like your model is a one-to-one relationship between OrderNumber and OrderInfo, with OrderInfo.OrderID as the OrderInfo primary key and as a foreign key referencing OrderNumber.OrderID. The ...
Dan Guzman's user avatar
  • 28.3k
6 votes
Accepted

Complex Delete Cascade Including Self-Referencing Table

I think I have captured what you need in this basic design: db<>fiddle ElementBase Self-fk for the hierarchy: CREATE TABLE dbo.ElementBase ( id integer NOT NULL, parent_id integer NOT ...
Paul White's user avatar
  • 86.4k
6 votes
Accepted

Cycles or multiple cascade paths with on delete set null: really?

From Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths You receive this error message because in SQL Server, a table cannot appear more than ...
Mikael Eriksson's user avatar
6 votes

Postgres suggests "Truncate table ... at the same time"

1) Cascaded truncates can be dangerous. If you have a lookup table (in this case, I believe XXXX), kiss it good-bye because it's going to be truncated and you'll lose all that data. For example, you ...
codedawg82's user avatar
6 votes
Accepted

Disadvantages to using ON DELETE CASCADE on every foreign key?

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything. A thing ...
D Kramer's user avatar
  • 503
5 votes

Cascade primary key update to all referencing foreign keys in oracle

With DEFERRABLE foreign key constraints: create or replace procedure cascade_update_primary ( p_table_name in varchar2, p_old_value in number, p_new_value in number ) as l_column_name ...
Balazs Papp's user avatar
  • 40.8k
5 votes

Postgres foreign key on delete cascade deletes all but 1, why?

Cascading only works in one direction, from the referenced item to the records that depend on it. So team_platforms will be deleted if their installations or platforms are removed. Similarly users ...
gwaigh's user avatar
  • 259
5 votes
Accepted

Does dropping a Postgres table cascade to materialized views that depend on it?

I see two distinct questions: Does dropping a Postgres table cascade to materialized views that depend on it? No, it raises an exception. Unless you add the keyword CASCADE. Then yes. (Same as with ...
Erwin Brandstetter's user avatar
4 votes

Is there anything wrong with linking two identical primary keys in two different tables?

Before worrying about how to get passed this error, you should consider that perhaps this isn't a technical problem, but instead a logical problem. Looking at the data model, I question why you are ...
Solomon Rutzky's user avatar
4 votes

Cycles or multiple cascade paths with on delete set null: really?

If this error message did not occur, and ON DELETE SET NULL were to set a column to null, any child-rows referencing the old value would no longer have a valid parent row. Cascading the update to ...
Hannah Vernon's user avatar
  • 70.2k
4 votes

Dangers in dropping all tables in a schema with CASCADE

run it in a transaction, you'll get warnings for every cascading drop, ROLLBACK if don't like what you see.
Jasen's user avatar
  • 3,593
4 votes

Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

You're close. AFTER triggers happen after foreign key constraint checking. So you need an INSTEAD OF trigger. That way you can modify the child tables before performing the DELETE on the target ...
David Browne - Microsoft's user avatar
4 votes
Accepted

Introducing FOREIGN KEY constraint 'FK_X_Y' on table 'X' may cause cycles or multiple cascade paths

You can use an INSTEAD OF trigger here, or have logic that re-assigns all the direct-reports prior to deleting a user. EG --drop table if exists users go CREATE TABLE [dbo].[Users] ( [UserId] ...
David Browne - Microsoft's user avatar
4 votes

Disadvantages to using ON DELETE CASCADE on every foreign key?

Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database? One reason not to : Oops!! If you ...
Phill  W.'s user avatar
  • 8,860
4 votes
Accepted

CASCADE DELETE per statement

There is no way to automatically delete all dependent rows; you will have to delete them before you delete the referenced row.
Laurenz Albe's user avatar
  • 53.5k
4 votes
Accepted

How can I know how many tables my DELETE has changed?

If you’re just generally curious, look at the query plan. It will show all the tables involved. If you’re looking to log or audit them, that’s a different story. You would need to use triggers, or ...
Erik Darling's user avatar
  • 41.9k
3 votes
Accepted

Postgres taking 3h to DELETE CASCADE

Danke schön to @a_horse_with_no_name for pointing EXPLAIN ANALYZE VERBOSE that shed light on a foreign key loop in my definitions. I need to explain more about the tables: An item is composed of ...
Matthieu's user avatar
  • 233
2 votes

Deleting large amounts of cascading records with minimal locking

I had the same issue. Deleting in batches helps, but still tends to lock up my Percona cluster because of the delay in replicating the delete to other nodes. In my case, most of the cascaded deletes ...
Edward Hibbert's user avatar
2 votes
Accepted

Unused Memory Grant on Delete

Try adding Id as one of the key columns of ix_HighSchoolCourseGrade_CourseId_includes. That may help with the additional sort needs as well as back the foreign key. If you can change the foreign key, ...
Erik Darling's user avatar
  • 41.9k
2 votes

Can I enforce cascade delete in a delete from statement?

As you probably know, the way to do this is a foreign key whith ON DELETE CASCADE. But you don't want this, so I see 2 options (there may be more as I'm not a SQL Server expert): Make this in the ...
Federico Razzoli's user avatar
2 votes

Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

For simplicity in the SQL generator, I ended up dropping all foreign key constraints and used AFTER DELETE triggers on tables to better address deletion of t_link records (I want to delete them when ...
Matthieu's user avatar
  • 233
2 votes

ON DELETE / ON UPDATE doubts

if we were to update student relation wouldn't this be rejected This is correct. If there is no ON UPDATE action defined in the foreign key reference, your update of referenced key would coflict with ...
sepupic's user avatar
  • 11k
2 votes
Accepted

Simulate DELETE CASCADE

The information required to generate the queries and the counts is all available in the catalog views, like sys.columns and sys.foreign_key_columns. We need to find all of the child tables, and then ...
Aaron Bertrand's user avatar
2 votes

Simulate DELETE CASCADE

I'm not aware of such tool, but you can easily create one your self (foreign-keys-in-a-sql-server-database): SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], C.TABLE_SCHEMA [PKTABLE_OWNER], ...
Lennart - Slava Ukraini's user avatar
2 votes

How do I CASCADE DROP a table being referenced in MariaDB?

I don't think that's how CASCADE works with MySQL derivatives! Tables: CREATE TABLE foo ( x int PRIMARY KEY ); CREATE TABLE bar ( x int, CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x) ...
Vérace's user avatar
  • 30.2k
2 votes
Accepted

How to "cascade delete" children without deleting parent?

Simply re-insert deleted records: WITH cte AS ( DELETE FROM parent WHERE {conditions} RETURNING * ) INSERT INTO parent SELECT * FROM cte; fiddle
Akina's user avatar
  • 20k

Only top scored, non community-wiki answers of a minimum length are eligible