Questions tagged [cascade]
The cascade tag has no usage guidance.
51
questions
0
votes
1
answer
118
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 ...
- 23
0
votes
0
answers
768
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 ...
- 101
0
votes
1
answer
414
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&...
- 113
1
vote
1
answer
1k
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 ...
0
votes
1
answer
852
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 ...
- 157
1
vote
5
answers
8k
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 ...
- 241
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. ...
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 ...
2
votes
2
answers
319
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 ...
- 291
5
votes
2
answers
9k
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:
-- ...
- 183
-2
votes
1
answer
52
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 ...
- 15
1
vote
2
answers
660
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 ...
- 1,457
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 ...
- 143
4
votes
1
answer
581
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 ...
- 654
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 ...
- 13
-1
votes
1
answer
338
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 ...
- 37
0
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 ...
- 183
2
votes
2
answers
1k
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.) ...
- 121
1
vote
2
answers
6k
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 (...
- 62.2k
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 ...
- 202
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 ...
- 21
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 ...
- 654
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 ...
- 141
3
votes
1
answer
257
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 ...
- 1,381
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 ...
19
votes
2
answers
20k
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 &...
- 873
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 ...
- 87
4
votes
1
answer
14k
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 ...
- 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
...
- 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 ...
- 11
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) ...
- 109
1
vote
1
answer
156
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 ...
- 1,959
1
vote
1
answer
299
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....
- 163
3
votes
0
answers
146
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?
...
- 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 ...
- 33
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 ...
- 109
1
vote
0
answers
98
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 ...
- 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,
...
- 163
11
votes
2
answers
37k
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. ...
- 215
2
votes
1
answer
769
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 ...
- 1,886
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 ...
- 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 ...
- 214
1
vote
0
answers
44
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 ...
- 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 ...
- 751
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 ...
- 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-...
- 1,417
2
votes
2
answers
6k
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
. | . ...
- 21
2
votes
1
answer
13k
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');
...
- 21
5
votes
2
answers
5k
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 ...
- 363
7
votes
2
answers
4k
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 ...
- 39.7k