Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [cascade]

57 questions
25
votes
2 answers

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 "YYYYY". HINT: Truncate table "XXXXX" at the same time, or…
davetapley
  • 953
  • 4
  • 9
  • 16
19
votes
1 answer

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-paths…
Saeed Neamati
  • 1,515
  • 3
  • 21
  • 35
12
votes
2 answers

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 current restrictions. When we try to create a table…
Gert Arnold
  • 224
  • 2
  • 10
11
votes
2 answers

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. My expectation was that any table that had a…
smac89
  • 215
  • 1
  • 3
  • 8
8
votes
2 answers

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 constraint is checked, an error is raised; this is…
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
5
votes
2 answers

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 table for testing: Field Type …
Kolja
  • 363
  • 2
  • 9
  • 22
5
votes
2 answers

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: -- TABLE t_parent CREATE TABLE t_parent (m_id INT…
Matthieu
  • 239
  • 1
  • 3
  • 14
5
votes
1 answer

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 `b` ( id INT UNSIGNED NOT NULL, a INT…
Dinesh Kumar
  • 664
  • 2
  • 11
  • 30
5
votes
1 answer

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, `parent` int(11) NOT NULL, `comment`…
Farhad Nežad
  • 163
  • 7
4
votes
2 answers

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) from table foo, but all the records in all the…
Timur Shtatland
  • 125
  • 2
  • 11
4
votes
5 answers

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 disadvantages, gotchas, or things to bear in mind when adding ON UPDATE…
Danny Beckett
  • 269
  • 4
  • 6
  • 19
4
votes
1 answer

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 would like to use if it were possible. All types…
Keith Stein
  • 143
  • 5
4
votes
1 answer

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 for every constraint that would normally block…
Anders Lindén
  • 664
  • 3
  • 12
  • 24
4
votes
1 answer

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 cascade path. So I'm trying to find a way to…
TGlatzer
  • 141
  • 5
4
votes
4 answers

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 corresponding record in OrderInfo, but it won't…
user18139
  • 87
  • 1
  • 4
1
2 3 4