You can read more about it on the official docs of Postgres. The second is not deleting the child rows from the database server with RESTRICT or NO ACTION. The first is to let the database server delete the related child rows with ON DELETE CASCADE. To delete the related child rows for a parent row, there are mainly two ways. So you have to be careful about how to set up on delete cascade.Īfter the author_id column, the quote table has created_at and updated_at similar to the columns with the same name on the author table. This creates a ripple effect that can delete many related children and children of children rows. This is like a domino effect when the parent row is deleted all its child rows are deleted too.ĭepending on how ON DELETE CASCADE is added on relational databases like Postgres, deleting one row on table A can delete related rows on table B, similarly deleting one row on table B can delete related rows on table C. The main point here is it has ON DELETE CASCADE which means when an author is deleted all the related quotes will also be deleted. The auhtor_id foreign key column is an integer and refers to the id column on the author. The interesting column is the foreign key to the author table called author_id as highlighed above. Then, it has a quote column that can hold up to 255 characters and always needs a value. The quote entity (table) also has an integer serial primary key similar to the author table. The created_at and updated_at columns are timestamp columns by default have the current timestamp. The name column is a varying character that can be up to 255 characters and cannot be null, it holds the author's name. ID is a serial which is an integer autoincremented by 1 and the primary key of the table. The author table has 4 columns id, name, created_at, and udpated_at. There are two entities (tables) author and quote. Quote character varying ( 255 ) NOT NULL UNIQUE ,Īuthor_id INTEGER REFERENCES author (id ) ON DELETE CASCADE , Updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL ![]() ![]() ![]() Name character varying ( 255 ) NOT NULL ,Ĭreated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL , In terms of Postgres Data Definition Language (DDL), the above ER diagram with two related entities translates to the following create table statements: CREATE TABLE author ( It can be represented in an ER diagram as follows (diagram generated with dbdiagram.io): One author can have multiple quotes and one quote will always have only one author. Author and Quotes example #įor this tutorial, you will use an example of an author and quotes. Given that is mentioned, you can continue with the guide now. Being able to read Entity Relationship (ER) diagrams will make it easier for you to understand the context.Previous understanding of how foreign keys work in RDBMS will be advantageous.Prior experience with any relations database management system (RDBMS) would be helpful.You can use a local Postgres Docker instance or simply use a free database on Elephant SQL or Neon.tech You should have a working Postgres server instance to test your code.In this guide, you will learn how to set up and use ON DELETE CASCADE in foreign keys in Postgres, let’s get going! Table of contents #īefore diving into the SQL code, below are some prerequisites for this tutorial: One of the useful features of PostgreSQL is on delete cascade that simplifies the code in your application. ![]() Postgres is one of the most popular open-source relational database systems in use today.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |