difference between cascade and restrict? SQL DDL database

sql database ddl
user3102872 picture user3102872 · Dec 16, 2013 · Viewed 89.8k times · Source

Could anyone tell me what exactly cascade, and restrict mean? It's in database systems subject the DDL Part

and what if i don't write any of them in my delete statement?

Answer

Aram picture Aram · Dec 16, 2013

The ON DELETE CASCADE and ON DELETE RESTRICT are the foreign key property and you set them when you are creating the relationship between two tables.

If you set the relationship to be ON DELETE CASCADE, when you run a DELETE statement on a parent table it will DELETE all the corresponding rows from the CHILD table automatically. But the RESTRICT (which is the default foreign key relationship behavior) is when you try to delete a row from the parent table and there is a row in the child table with the same ID, it will fail complaining about the existing child rows.

Either way, you don't need to mention anything in your DELETE clause.

I also wrote a blog post about the different rules for Delete and Update commands in more detail here:

https://koukia.ca/sql-server-foreign-key-update-and-delete-rules-556cf09117fe

Related questions

Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?

I want to create a database which does not exist through JDBC. Unlike MySQL, PostgreSQL does not support create if not exists syntax. What is the best way to accomplish this? The application does not know if the database exists …

Read More
What is a good Visio Enterprise Architect replacement?

I've been using Visio 2002/2003 Enterprise Architect to do my database schema design visually and then forward-generate the DDL to create the database. I wanted to switch to Visio 2007, but while it does have database diagramming support, it doesn't have the …

Read More
Postgres database create if not exists

Is there an analog to CREATE TABLE IF NOT EXISTS for creating databases? Background: I am writing a script to automatically set up the schema in PostgreSQL on an unknown system. I am not sure if the database (or even …

Read More