Skip to main content

Questions tagged [cascade]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
0 answers
86 views

Why does a cascade delete cause such high logical reads?

I am doing some testing and have a simple setup. An order table linked to a customer table via a foreign key with On cascade delete. There is a unique nonclustered index on the (customer_id, order_id) ...
Backend's user avatar
  • 11
3 votes
3 answers
214 views

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

Suppose that I have many tables linked by a chain of ON DELETE CASCADE foreign keys. To the best of my knowledge and experience, deleting from one table gives me no notice at all that I have in fact ...
J. Mini's user avatar
  • 501
2 votes
2 answers
2k views

CASCADE DELETE per statement

Can I execute something like this in Postgresql, that is, execute CASCADE DELETE for just one statement? DELETE CASCADE FROM foo WHERE bar = 'baz'; My goal is to delete not just the selected record(s)...
Timur Shtatland's user avatar
1 vote
2 answers
240 views

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

I understand that a regular view will be invalid if a table it is dependent on is dropped. Does the same apply to a materialized view? I would assume that in a materialized view since the data has ...
vfclists's user avatar
  • 1,053
0 votes
1 answer
217 views

"ON UPDATE" in self referencing table

I have a table Category with a parent_id column referencing the self id column, for this, I used "ON DELETE CASCADE" meaning if the parent is deleted, other children would be deleted too. At ...
bguernouti's user avatar
0 votes
0 answers
955 views

Foreign key cascade & triggers

I have a question on how to elaborate a schema in PostgreSQL which involves: A trigger function used to keep a certain consistency calculation in a table; A foreign key whose value in the original ...
alecov's user avatar
  • 101
0 votes
1 answer
545 views

How can I prevent a rows with a particular attribute from being deleted in a cascade?

I have the following table CREATE TABLE IF NOT EXISTS node ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, type TEXT CHECK(type IN ("DIRECTORY", "EXECUTABLE", "DOCUMENT&...
Robert W's user avatar
  • 113
1 vote
1 answer
2k views

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

I am using postgres v and I am trying to get a multi table cascading delete to work. However, when I do it, 1 table/entry doesn't get removed and I can't for the life of me understand why. Here is my ...
Christian Bongiorno's user avatar
0 votes
1 answer
1k views

Cascading PostgreSQL triggers between three tables

I have a ‘test’ PostgreSQL 12 schema, defined with initial tables: begin; drop schema if exists test cascade; create schema test; set search_path = test,public; create table jobs ( job_id serial ...
Rudy Stricklan's user avatar
2 votes
5 answers
11k views

Disadvantages to using ON DELETE CASCADE on every foreign key?

Our application currently has "soft delete" built-in to most tables with a BIT "deleted" column. There is now a requirement for "hard delete" functionality. Are there any ...
Danny Beckett's user avatar
2 votes
1 answer
3k views

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

So while publishing my database scripts in Visual Studio, I get this exact error: Introducing FOREIGN KEY constraint 'FK_Users_Manager' on table 'Users' may cause cycles or multiple cascade paths. ...
Julius Rupšys's user avatar
0 votes
1 answer
1k views

How to "cascade delete" children without deleting parent?

I have a table, let's call it parent: CREATE TABLE parent ( id TEXT PRIMARY KEY, name TEXT, description TEXT, -- other parent attributes created_at TIMESTAMPTZ ...
Laurence Gonsalves's user avatar
2 votes
2 answers
350 views

Deletion of orphaned rows in a violation-free & non-blocking manner: predictive order of execution of predicates in case of double-checked locking

I have tables person and toy. There is a foreign key person.favorite_toy_id from person to toy. This foreign key is declared as ON DELETE RESTRICT. I now want to delete all toys which are no longer ...
pbillen's user avatar
  • 291
5 votes
2 answers
10k views

Solving ON DELETE CASCADE cycles with triggers on MS SQL Server

I have code that is working fine in PostgreSQL and I now have to port it to MS SQL Server. It involves tables with potential cycles on delete/update events and SQL Server is complaining about it: -- ...
Matthieu's user avatar
  • 233
-2 votes
1 answer
54 views

ON DELETE / ON UPDATE doubts

I was looking at database past exam from the school that I attend, and I have a question about ON DELETE and ON UPDATE. The question in the past exam was: Show the contents of the tables after ...
CS336's user avatar
  • 15
1 vote
2 answers
776 views

Simulate DELETE CASCADE

I need to delete many rows from one table persons, that is referenced by many other tables as FK. If I had DELETE CASCADE, I could just delete the records from persons, and automatically delete from ...
carlo.borreo's user avatar
  • 1,487
4 votes
1 answer
2k views

Complex Delete Cascade Including Self-Referencing Table

Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I ...
Keith Stein's user avatar
5 votes
1 answer
606 views

Standard Behaviour for mix of 'ON-DELETE-CASCADE' and 'ON-DELETE-RESTRICT' constraints in Mysql

In mysql 5.6, Consider these 2 examples creating relationships between A, B, C and D. Example 1 CREATE TABLE `a` ( id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ) ENGINE = INNODB; CREATE TABLE ...
Dinesh Kumar's user avatar
1 vote
1 answer
4k views

SQLITE: Multiple foreign key referenced to multiple table cascade delete

In SQLite I would like to delete parent table row and it has to delete all the related child tables. I have gone through all the questions in StackExchange and other websites but my query no where ...
user2660409's user avatar
-1 votes
1 answer
347 views

cascade constraints in Oracle

Can we use the cascade constraints instead of specifying FK in our code I mean can we Use this execute immediate 'ALTER TABLE @PCWSCHEMA@pcwscannermaster DROP CASCADE CONSTRAINTS'; instead of the ...
Dothertechie's user avatar
2 votes
1 answer
5k views

Postgres taking 3h to DELETE CASCADE

I have a Postgresql 9.3 with a "mother" table containing items, and a number of "child" tables containing parts of different kinds (e.g. item "AAA" is composed of 1000 parts "P1", 800 parts "P2", 40 ...
Matthieu's user avatar
  • 233
2 votes
2 answers
2k views

Dangers in dropping all tables in a schema with CASCADE

The answer to this question suggests I can use the CASCADE option of DROP SCHEMA to remove all a schema and all its tables, but warns: CASCADE - Automatically drop objects (tables, functions, etc.) ...
Mike S's user avatar
  • 121
1 vote
2 answers
7k views

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

If I have a table that references another one CREATE TABLE foo ( x int PRIMARY KEY ); CREATE TABLE bar ( x int, CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x) ); DROP TABLE foo; ERROR 1451 (...
Evan Carroll's user avatar
  • 63.7k
10 votes
2 answers
6k views

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

I'm asking this question to check if my reasoning about cascaded delete is correct and if I'm not overlooking anything. I understand the behavior and I'm not asking why it is implemented with the ...
Gert Arnold's user avatar
1 vote
1 answer
2k views

Tables with mixed ON DELETE CASCADE and ON DELETE RESTRICT, rule set with recursion?

Not sure if this is a generic question, but unless not, this is MySQL 5.6. We have a database consisting of ~300 tables, INNODB, all connected with proper foreign keys. One of these tables can be ...
Matthias's user avatar
4 votes
1 answer
4k views

Can I enforce cascade delete in a delete from statement?

I am using constraints in a database meaning that records of a certain table cannot be removed lightly. That is normally the desired case. However, is there a way to write a DELETE FROM query so that ...
Anders Lindén's user avatar
4 votes
1 answer
2k views

Is there a possibility to see cascade path in MS SQL

I'm having trouble with creating a foreign key in a database, because of multiple cascade paths. I am aware of some of my FKs cascading the delete, but I cannot traverse back to find the multiple ...
TGlatzer's user avatar
  • 141
3 votes
1 answer
263 views

Unused Memory Grant on Delete

I have this delete statement delete from dbo.HighSchoolGradeLevel where Id=@P0 This is the execution plan https://www.brentozar.com/pastetheplan/?id=H1qzvl3nW when the application is busy and ...
sebeid's user avatar
  • 1,391
3 votes
1 answer
11k views

Cascade primary key update to all referencing foreign keys in oracle

Is it possible to update all referencing foreign keys in Oracle when updating primary key? A procedure like this that gets the table name and primary key and finds all foreign keys in all tables and ...
ali akbar azizkhani's user avatar
21 votes
2 answers
22k views

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

When running: TRUNCATE TABLE YYYYY RESTART IDENTITY I am seeing this error message: ERROR: cannot truncate a table referenced in a foreign key constraint DETAIL: Table "XXXXX" references &...
davetapley's user avatar
4 votes
4 answers
3k views

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

As you can see here, in OrderInfo and OrderNumber, I have a relationship set. But I'm not sure what kind. I was hoping to set a relationship so if I deleted an OrderNumber, it would cascade delete the ...
user18139's user avatar
4 votes
1 answer
15k views

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

I have inserted some data in a table where I should not have. I am trying to delete the data, but it has a 1:M relationship and I need to also delete data that references this one. I do not want to ...
BogdanBiv's user avatar
  • 151
3 votes
1 answer
2k views

Data guard broker cascaded standby delay parameter is ignored

I have 3 databases: db01 primary, db02 standby cascading, db03 standby cascaded. I have configured broker parameters by the following way: DGMGRL> show configuration Configuration – DB_HQ_DR ...
kupa's user avatar
  • 1,707
1 vote
0 answers
201 views

SET NULL fails with consecutive cascade operations

I have tables for country/province/district/commune/village. Each have a foreign key on its parent with a cascade delete. I then have a location table with foreign keys to each tables above with a ...
Jerome B's user avatar
0 votes
1 answer
4k views

Trigger to delete with cascade effects [closed]

Hi I am new to Oracle triggers. Your help will be much appreciated! I have the following 2 tables: Product: CREATE TABLE Product (prodId varchar(10) NOT NULL PRIMARY KEY, prodName varchar(30) ...
omar's user avatar
  • 109
1 vote
1 answer
159 views

Tricky condition for PostgreSQL UPDATE query

I need help with a PostgreSQL query. I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to ...
Hassan Baig's user avatar
  • 1,989
1 vote
1 answer
308 views

Remove accidental duplicates in PK table, cascade changes to FK table

Lets say I have three tables in a postgreSQL database, one of plants, one of samples, and one of mappings to change variables in plants. There is a PK-FK relationship between plants.id and samples....
Amadou Kone's user avatar
3 votes
0 answers
155 views

Visualize the effects of a cascading delete or update?

In MySQL, I'm working with a database that has integrity constraints on foreign keys (nothing overly special here). Is there a method to somehow visualize the effects of a cascading delete or update? ...
Jacco's user avatar
  • 131
3 votes
1 answer
11k views

Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
kelsohmm's user avatar
1 vote
1 answer
1k views

Is it possible to drop tablespace CASCADE in MySQL?

I am newbie with MySQL. I have created a tablespace with a datafile, and I have tried to drop this tablespace without drop any object in that tablespace by using that SQL query: DROP TABESPACE ...
BongSey's user avatar
  • 119
1 vote
0 answers
99 views

Changing Action on Delete to Cascade

We have a rather big database (about 400'000'000 records) in roughly 100 tables. The foreign keys in these tables are defined, so that No Action is taken, when deleting data from a table. We now have ...
xeraphim's user avatar
  • 597
5 votes
1 answer
2k views

Alter ON DELETE CASCADE in 1 table

I'm really new to ON DELETE CASCADE. How can I delete child comments when the parent comment gets deleted in a table? Table Schema 'CREATE TABLE `comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
Farhad Nežad's user avatar
11 votes
2 answers
40k views

DROP TABLE ... CASCADE does not drop any dependent table

I am still relatively new to postgres and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. ...
smac89's user avatar
  • 215
2 votes
1 answer
791 views

Differentiating physical standby to cascaded standby

Why is it that for non-real time apply from cascading to cascaded standby that we have to mention as SYNC as the redo transport mode? Ideally we mention SYNC for real time apply right? Also, should ...
tesla747's user avatar
  • 1,896
4 votes
1 answer
1k views

Soft ON DELETE CASCADE

I am familiar with the ON DELETE CASCADE method, but I don't want to do a real delete, just a soft delete. This is implemented with a column named isDeleted (0/1). Is there a way for me to cascade ...
kenpino's user avatar
  • 41
3 votes
2 answers
2k views

Deleting large amounts of cascading records with minimal locking

I'm looking for a strategy to purge large amounts of logically deleted data from a MySQL database. The deleted records are in a relatively small table, but are referred to with many ON DELETE CASACDE ...
shevron's user avatar
  • 214
1 vote
0 answers
48 views

Is it possible to cascade delete a column value? [duplicate]

So say I delete catID from table A and table B has the same foreign key value? It's hard to explain so I'll try and show you with the two images below: items table Is it possible to delete the catID ...
James111's user avatar
  • 250
1 vote
1 answer
1k views

parent child records - cascade deletes or transactions?

I'm about to create a parent and child table. In planning how to do CRUD operations on this data, I was wondering what is better design when it comes to deleting data. I'm using SQLite. I know ...
dot's user avatar
  • 771
2 votes
1 answer
1k views

Several Foreign Key and Cascade Delete SQL Server

In SQL Server 2014, I'm trying to add CASCADE DELETING (I want to set field to null actually, but is the same ) on 3 FK. If I add a Cascade Delete in one relationship, it works fine. If I add more ...
Romias's user avatar
  • 123
17 votes
1 answer
8k views

Why can't we have multiple cascade paths?

You can see that many questions have being asked about multiple cascade paths. For example: https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-...
Saeed Neamati's user avatar