CODEWITHSUNDEEP
sql
Aaron
Aaron

Reputation: 151

SQL - Delete Cascade is used with?

I'm learning SQL and I'm stuck on one of the review questions and can't find an answer in the text book. When you 'delete cascade'

What is it 'used' with?

I want to say primary key because it is going to identify the correct tuple when it cascades? Am I right/wrong, I just don't know which one is right.

Thanks.

Upvotes: 1

Views: 315

Answers (2)

vol7ron
vol7ron

Reputation: 42109

It deletes all records that have FKs to that record.

See Fiddle

CREATE TABLE foo (
   id  serial, 
   num int, 
   PRIMARY KEY (id)
);
INSERT INTO foo(num) VALUES(1),(2),(3),(4);

CREATE TABLE bar ( 
   foo_id bigint unsigned,
   FOREIGN KEY (foo_id) REFERENCES foo (id) ON DELETE CASCADE
);
INSERT INTO bar(foo_id) VALUES (1),(2),(3),(4);

DELETE FROM bar WHERE foo_id = 3;  -- notice 3 is only removed from bar
DELETE FROM foo WHERE id = 2;      -- notice 2 is removed from both foo and bar

When the record from foo is deleted, any constraints that reference a key of that record and have delete cascade specified, will also automatically be deleted. Without that specification, when the record in foo is deleted, the database will send an error message if any foreign dependencies still exist at the end of the transaction.

Upvotes: 1

Avin Varghese
Avin Varghese

Reputation: 4370

it is used together with a referential constraint.

more about Referential CONSTRAINT

Upvotes: 1

Related Questions