Questions tagged [cascade]
The cascade tag has no usage guidance.
57 questions
0
votes
1
answer
50
views
How to know wether a DELETE CASCADE will delete child records?
Lets say that I have a table A which is referenced by table B. Each record in table A may be referenced by multiple records from table B. This would be the classic "A has many B".
Table B ...
- 11
1
vote
0
answers
122
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) ...
- 11
3
votes
3
answers
269
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 ...
- 823
2
votes
2
answers
4k
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)...
- 105
1
vote
2
answers
573
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 ...
- 1,093
0
votes
1
answer
241
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
1k
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
617
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&...
- 123
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 ...
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 ...
- 167
3
votes
5
answers
13k
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 ...
- 259
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
364
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 ...
- 301
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:
-- ...
- 239
-2
votes
1
answer
55
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
810
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,497
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
5
votes
1
answer
619
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 ...
- 674
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
359
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
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 ...
- 239
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.) ...
- 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 (...
- 64.6k
11
votes
2
answers
7k
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 ...
- 214
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
4
votes
1
answer
5k
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 ...
- 664
2
votes
1
answer
830
views
Foreign Key, on delete set null
Am I misunderstanding something here? I have two tables, table1 and table2. Table2 has a foreign key that references a field in table1 with ON DELETE SET NULL associated with the foreign key. Table2 ...
- 25
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
273
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,401
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 ...
22
votes
2
answers
24k
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 &...
- 923
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
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 ...
- 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
...
- 1,717
1
vote
0
answers
202
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
167
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 ...
- 2,039
1
vote
1
answer
312
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....
- 193
3
votes
0
answers
164
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 ...
- 119
1
vote
0
answers
102
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
42k
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
801
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,926
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
49
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 ...
- 771