2022 Developer Survey is open! Take survey.

Questions tagged [cascade]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
18 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&...
user avatar
  • 103
0 votes
1 answer
117 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 ...
user avatar
0 votes
1 answer
89 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 ...
user avatar
0 votes
5 answers
3k 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 ...
user avatar
2 votes
1 answer
899 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. ...
user avatar
0 votes
1 answer
703 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 ...
user avatar
2 votes
2 answers
245 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 ...
user avatar
  • 281
5 votes
2 answers
5k 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: -- ...
user avatar
  • 173
-2 votes
1 answer
49 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 ...
user avatar
  • 15
1 vote
2 answers
320 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 ...
user avatar
  • 1,407
4 votes
1 answer
1k 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 ...
user avatar
4 votes
1 answer
375 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 ...
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 ...
user avatar
-1 votes
1 answer
302 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 ...
user avatar
0 votes
1 answer
3k 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 ...
user avatar
  • 173
2 votes
2 answers
720 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.) ...
user avatar
  • 121
1 vote
2 answers
4k 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 (...
user avatar
  • 55.7k
10 votes
2 answers
4k 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 ...
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 ...
user avatar
3 votes
1 answer
3k 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 ...
user avatar
3 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 ...
user avatar
  • 131
3 votes
1 answer
206 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 ...
user avatar
  • 1,363
3 votes
1 answer
9k 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 ...
user avatar
11 votes
2 answers
13k 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 &...
user avatar
4 votes
4 answers
2k 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 ...
user avatar
4 votes
1 answer
13k 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 ...
user avatar
  • 151
3 votes
1 answer
1k 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 ...
user avatar
  • 1,667
1 vote
0 answers
181 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 ...
user avatar
1 vote
1 answer
3k 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) ...
user avatar
  • 111
1 vote
1 answer
140 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 ...
user avatar
  • 1,689
1 vote
1 answer
212 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....
user avatar
3 votes
0 answers
125 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? ...
user avatar
  • 131
3 votes
1 answer
10k 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 ...
user avatar
1 vote
1 answer
920 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 ...
user avatar
  • 109
1 vote
0 answers
90 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 ...
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, ...
user avatar
9 votes
2 answers
26k views

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

I am still relatively new to psql 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. My ...
user avatar
  • 195
2 votes
1 answer
696 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 ...
user avatar
  • 1,814
4 votes
1 answer
968 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 ...
user avatar
  • 41
3 votes
2 answers
1k 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 ...
user avatar
  • 204
1 vote
0 answers
28 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 ...
user avatar
  • 250
1 vote
1 answer
935 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 ...
user avatar
  • 701
2 votes
1 answer
849 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 ...
user avatar
  • 123
14 votes
1 answer
6k 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-...
user avatar
1 vote
2 answers
5k views

MySQL - Creating an associative table

I have the following table structures: Cases: (PK) case_id | case_notes | time 1 | blah | timestamp 2 | blah | timestamp 3 | blah | timestamp . | . ...
user avatar
2 votes
1 answer
11k views

ON DELETE CASCADE not deleting entry for foreign key

I am running the below query in ideone CREATE TABLE AUTHOR ( AUTHOR_ID NUMBER(5) PRIMARY KEY, AUTHOR_NAME VARCHAR2(18) ); INSERT INTO AUTHOR (AUTHOR_ID, AUTHOR_NAME) VALUES (1234, 'MC GRAW HILL'); ...
user avatar
5 votes
2 answers
4k views

Foreign key set to cascading but fails to update - why?

As mentioned elsewhere, I am rather new to SQL and databases. Getting my hands dirty at the moment and trying all the different possibilities. I have a server running MySQL and set up the following ...
user avatar
  • 353
6 votes
2 answers
3k views

Can I use `on delete set null` on a compound foreign key which contains a primary key column?

The docs say: Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the ...
user avatar
  • 37.4k
2 votes
1 answer
8k views

ON DELETE CASCADE not working

Trying to implement a simple user follower system in MySQL, using a junction table: CREATE TABLE users ( id int NOT NULL AUTO_INCREMENT, email varchar(255) NOT NULL, username varchar(25) ...
user avatar
  • 21