Error update or delete on table violates foreign key constraint

There are a few problems with your tables. I’ll try to address the foreign keys first, since you question asked about them

But before that, we should realize that the two sets of tables (the first three you created and the second set, which you created after dropping the first set) are the same. Of course, the definition of Table3 in your second attempt has syntax and logical errors, but the basic idea is:

CREATE TABLE table3 (   
  "ID" bigint NOT NULL DEFAULT '0',   
  "DataID" bigint DEFAULT NULL,   
  "Address" numeric(20) DEFAULT NULL,   
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),   
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,   
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

This definition tell PostgreSQL roughly the following: «Create a table with four columns, one will be the primary key (PK), the others can be NULL. If a new row is inserted, check DataID and Address: if they contain a non-NULL value (say 27856), then check Table1 for DataID˙and Table2 for Address. If there is no such value in those tables, then return an error.» This last point which you’ve seen first:

ERROR: insert or update on table "Table3" violates foreign key constraint 
    "Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not present in table "Table1".

So simple: if there is no row in Table1 where DataID = 27856, then you can’t insert that row into Table3.

If you need that row, you should first insert a row into Table1 with DataID = 27856, and only then try to insert into Table3. If this seems to you not what you want, please describe in a few sentences what you want to achieve, and we can help with a good design.

And now about the other problems.

You define your PKs as

CREATE all_your_tables (
    first_column NOT NULL DEFAULT '0',   
    [...]
    PRIMARY KEY ("ID"),  

A primary key means that all the items in it are different from each other, that is, the values are UNIQUE. If you give a static DEFAULT (like '0') to a UNIQUE column, you will experience bad surprises all the time. This is what you got in your third error message.

Furthermore, '0' means a text string, but not a number (bigint or numeric in your case). Use simply 0 instead (or don’t use it at all, as I written above).

And a last point (I may be wrong here): in Table2, your Address field is set to numeric(20). At the same time, it is the PK of the table. The column name and the data type suggests that this address can change in the future. If this is true, than it is a very bad choice for a PK. Think about the following scenario: you have an address ‘1234567890454’, which has a child in Table3 like

ID        DataID           Address             Data
123       3216547          1234567890454       654897564134569

Now that address happens to change to something other. How do you make your child row in Table3 follow its parent to the new address? (There are solutions for this, but can cause much confusion.) If this is your case, add an ID column to your table, which will not contain any information from the real world, it will simply serve as an identification value (that is, ID) for an address.

Before we begin looking at the different solutions to implement ON DELETE CASCADE in PostgreSQL, we will first understand what ON DELETE CASCADE means and what it does.

Let us suppose you have two tables, with one of them inheriting a KEY from the parent table and using its values, also known as ASSOCIATION. Now let’s say that a user wants to delete this row in the parent table.

But wait? What will happen to the inherited row in the child table?

You might be thinking there is an error that will be raised for this VIOLATION. Correct! Fortunately, this won’t work, and an error will be produced.

ERROR:  update or delete on table [your_table] violates foreign key constraint [f_key] on table [your_table]

But what if you didn’t want to restrict the DELETE, but rather go ahead and DELETE this row from both tables. That is where our operation comes in.

Let us see how it works.

Use ON DELETE CASCADE in PostgreSQL

Let’s begin with creating a Vehicle table first.

create table vehicle (
	id int PRIMARY KEY,
	OWNER TEXT
);

Now let’s define another table called BUS, which will inherit the key ID from VEHICLE.

create table bus (
	id int PRIMARY KEY references vehicle,
	Model TEXT
);

You can see the REFERENCES tag at the end of the definition of ID. This means that it now references the rows from the VEHICLE table, and any INSERT operation in this table where the ID does not match the ID in the VEHICLE table will be rejected.

Now let’s suppose that we INSERT some values into the VEHICLE table.

Insert into vehicle values (1, 'mark'), (2, 'john');

And let’s also INSERT a value into the BUS table.

insert into bus values (2, 'High_Van');

So now the BUS table refers to the KEY 2 in the VEHICLE table, meaning that High_Van belongs to John.

Now let’s try to delete this entry from BUS.

If you call:

delete from vehicle where id = 2

An error will be returned.

ERROR:  update or delete on table "vehicle" violates foreign key constraint "bus_id_fkey" on table "bus"
DETAIL:  Key (id)=(2) is still referenced from table "bus".

This tells you that KEY 2 is still referenced in the table BUS. Thus the DELETE would not work. Now let’s define what should happen if we call DELETE.

In the BUS table, modify the column ID.

create table bus (
	id int PRIMARY KEY references vehicle ON DELETE CASCADE,
	Model TEXT
);

Now when we try to DELETE, it works perfectly. Why? Because CASCADE tends to drop the row proposed for DELETE in the child table.

Suppose you are better off with the original and want to define your method. In that case, you can try changing the ON DELETE CASCADE to ON DELETE RESTRICT, which will eventually restrict any DELETE operations that come into conflict.

You can even use these options for the ON UPDATE operation in other ways.

a Short Note on the Issues Faced on Defining Multiple ON CASCADE DELETE Constraints in PostgreSQL

An ON DELETE CASCADE put on all the inheriting tables will make an issue when you delete a row that references thousands of tables. This will create an issue but rolling back to any changes will be highly unlikely.

Always make sure to use good practices for DELETE. If you want to CASCADE, call a function for DELETE, then make a TRANSACTION and constantly check for mishappenings rather than at the end.

This will guarantee the security and safety of your database and avoid problems in the future.

We hope you learned how to do the ON DELETE CASCADE operation in PostgreSQL.

In relational databases, foreign keys are used to define a constrained relationship between two entities. Foreign keys are useful for ensuring data integrity.

What is a foreign key

Foreign keys are used to refer to other tables relative to the primary key. The foreign key is defined in the child table, which corresponds one or more columns of the child table to the primary key or unique key value of the parent table, and establishes an association relationship between the rows of the child table and the rows of the parent table.

Let’s take a look at two tables country and city from Sakila sample database. Here is their relationship diagram:

Here is some rows from the country table:

SELECT *
FROM country
WHERE country_id = 23;

 country_id | country |     last_update
------------+---------+---------------------
         23 | China   | 2006-02-15 04:44:00
(1 row)

Here is some rows from the city table:

SELECT *
FROM city
WHERE country_id = 23;

city_id |     city      | country_id |     last_update
---------+---------------+------------+---------------------
     46 | Baicheng      |         23 | 2006-02-15 04:45:25
     47 | Baiyin        |         23 | 2006-02-15 04:45:25
     80 | Binzhou       |         23 | 2006-02-15 04:45:25
    109 | Changzhou     |         23 | 2006-02-15 04:45:25
    136 | Datong        |         23 | 2006-02-15 04:45:25
...
(53 rows)

From this we can see that the country table and the city table is a one-to-many relationship. A country can have multiple cities, and a city can only be located in one country.

If a country already has cities, you cannot easily delete countries from the country table, otherwise the corresponding city data will be incomplete. You also can’t set a non-existent country_id for a city, otherwise the city data will be wrong.

Foreign key constraints ensure that the data is complete and correct.

Usually, the table that has the foreign key is called the child table, and the table referenced by the foreign key is called the parent table.

PostgreSQL Foreign Key Syntax

Add foreign key when creating table

To add foreign keys when creating a table, use the following syntax:

CREATE TABLE table_name (
  column_defination_1,
  ...
  [CONSTRAINT foreign_key_name]
    FOREIGN KEY (column)
      REFERENCES parent_table_name (column)
      ON UPDATE ...
      ON DELETE ...
;
);

Explanation:

• The foreign_key_name is the name of the foreign key constraint. CONSTRAINT foreign_key_name is optional.

• Tge FOREIGN KEY (column) indicates that the column column is a foreign key.

• The REFERENCES parent_table_name (column) indicates that the foreign key refers to the column column in the parent_table_name table.

• The ON DELETE and ON UPDATE specifies the constraint strategy to take when deleting or updating rows in the parent table. You can use one of the following 5 strategies:

  • NO ACTION: This is the default policy.
  • RESTRICT: A PostgreSQL error is raised when attempting to delete or update a row in the parent table if a row in the parent table has a matching row in the child table.
  • CASCADE: If a row in the parent table is deleted or updated, the value of the matching row in the child table is automatically deleted or updated.
  • SET NULL: If a row in the parent table is deleted or updated, the value of the matching row in the child table is set to NULL.
  • SET DEFAULT: If a row in the parent table is deleted or updated, the value of the matching row in the child table is set to the default value.

Let’s look at the foreign key constraints defined by the city table:

                                          Table "public.city"
  Column    |            Type             | Collation | Nullable |                Default
-------------+-----------------------------+-----------+----------+---------------------------------------
city_id     | integer                     |           | not null | nextval('city_city_id_seq'::regclass)
city        | character varying(50)       |           | not null |
country_id  | smallint                    |           | not null |
last_update | timestamp without time zone |           | not null | now()
Indexes:
   "city_pkey" PRIMARY KEY, btree (city_id)
   "idx_fk_country_id" btree (country_id)
Foreign-key constraints:
   "city_country_id_fkey" FOREIGN KEY (country_id) REFERENCES country(country_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
   TABLE "address" CONSTRAINT "address_city_id_fkey" FOREIGN KEY (city_id) REFERENCES city(city_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
   last_updated BEFORE UPDATE ON city FOR EACH ROW EXECUTE FUNCTION last_updated()

Note the part of the foreign key:

Foreign-key constraints:
    "city_country_id_fkey" FOREIGN KEY (country_id) REFERENCES country(country_id) ON UPDATE CASCADE ON DELETE RESTRICT

Add foreign key when altering a table

If the foreign key is not defined when the table is creating, you can also add the foreign key later by using the following syntax:

ALTER TABLE child_table_name
ADD [CONSTRAINT foreign_key_name]
  FOREIGN KEY (column)
    REFERENCES parent_table_name (column)
    ON UPDATE ...
    ON DELETE ...
;

Explanation:

• Use the ALTER TABLE statement to modify the definition of the table.
• Use the ADD [CONSTRAINT foreign_key_name] to add a foreign_key_name constraint named. It is optional.
• The foreign key is defined using FOREIGN KEY (column)) REFERENCES parent_table_name (column).

Drop Foreign Keys Syntax

To drop a foreign key on a table, you can use the following syntax:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Explanation:

• Use the ALTER TABLE statement to modify the definition of the table.
• The DROP CONSTRAINT specifies the constraint name after it. It can remove any constraint by name, not just foreign keys.

PostgreSQL FOREIGN KEY instance

The following example will create two tables users and user_hobbies in the testdb database, where foreign keys are used in the user_hobbies table to reference the users table. Please follow the steps below:

1. Log in to the PostgreSQL database as postgres user:

   [~] psql -U postgres
   psql (14.4)
   Type "help" for help.
   

   Note: You can also log in as any other user with appropriate database privileges.

2. Connect to the testdb database:

   If you haven’t created the database yet, run the following statement first:

3. Create the users table:

   CREATE TABLE users (
     user_id INTEGER NOT NULL,
     name VARCHAR(45) NOT NULL,
     PRIMARY KEY (user_id)
   );
   

So far, we have created the users table.

CASCADE Policy Example

If the ON DELETE and ON UPDATE use the CASCADE strategy:

• If a row in the parent table is deleted, matching rows in the child table are also deleted.
• If the key value of the row of the parent table is updated, the columns of the matching row in the child table are also updated.

Use the following SQL to create a user_hobbies table with foreign keys using the CASCADE strategy.

DROP TABLE IF EXISTS user_hobbies;
CREATE TABLE user_hobbies (
  hobby_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  PRIMARY KEY (hobby_id),
  FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

The following statement inserts some rows into the two tables:

DELETE FROM users;
DELETE FROM user_hobbies;
INSERT INTO users (user_id, name)
VALUES (1, 'Tim');
INSERT INTO user_hobbies (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

At this point the rows in the user_hobbies table:

hobby_id | user_id |  hobby
----------+---------+----------
       1 |       1 | Football
       2 |       1 | Swimming
(2 rows)

Let’s take a look at the associated operation of the child table caused by the UPDATE and DELETE operation on the parent table:

• The UPDATE operate on the parent table

  We modify the value of the user_id in the parent table users from 1 to 100:

  UPDATE users
  SET user_id = 100
  WHERE user_id = 1;
  

  At this point the rows in the user_hobbies table:

  hobby_id | user_id |  hobby
  ----------+---------+----------
         1 |     100 | Football
         2 |     100 | Swimming
  (2 rows)

  We found that the value of user_id 1 in the user_hobbies table and users table was automatically modified to 100.

• The DELETE operate on the parent table

  DELETE FROM users
  WHERE user_id = 100;
  

  At this point the rows in the user_hobbies table:

  hobby_id | user_id | hobby
  ----------+---------+-------
  (0 rows)

  We found that those rows with user_id 100 in user_hobbies table and users table were deleted.

RESTRICT strategy

If the ON DELETE and ON UPDATE use the RESTRICT strategy:

• PostgreSQL prohibits deleting rows in parent tables that match child tables.
• PostgreSQL prohibits dropping the value of the key of the row in the parent table that matches the child table.

Use the following SQL to create a user_hobbies table with foreign keys using the RESTRICT strategy.

DROP TABLE IF EXISTS user_hobbies;
CREATE TABLE user_hobbies (
  hobby_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  hobby VARCHAR(45) NOT NULL,
  PRIMARY KEY (hobby_id),
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

Insert some rows into two tables:

DELETE FROM users;
DELETE FROM user_hobbies;
INSERT INTO users (user_id, name)
VALUES (1, 'Tim');
INSERT INTO user_hobbies (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

At this point the rows in the user_hobbies table:

hobby_id | user_id |  hobby
----------+---------+----------
       1 |       1 | Football
       2 |       1 | Swimming
(2 rows)

Let’s see the result of the UPDATE and DELETE operation on the parent table:

• The UPDATE operate on the parent table

  UPDATE users
  SET user_id = 100
  WHERE user_id = 1;
  

  The PostgreSQL server returned the following error:

  ERROR:  update or delete on table "users" violates foreign key constraint "user_hobbies_user_id_fkey" on table    "user_hobbies"
  DETAIL:  Key (user_id)=(1) is still referenced from table "user_hobbies".

• The DELETE operate on the parent table

  DELETE FROM users
  WHERE user_id = 1;
  

  The PostgreSQL server returned the following error:

  ERROR:  update or delete on table "users" violates foreign key constraint "user_hobbies_user_id_fkey" on table "user_hobbies"
  DETAIL:  Key (user_id)=(1) is still referenced from table "user_hobbies".

SET NULL policy

If the ON DELETE and ON UPDATE use the SET NULL strategy:

• If a row of the parent table is deleted, the value of the column of the matching row in the child table is set to NULL.
• If the key value of a row in the parent table is updated, the column value of the matching row in the child table is set to NULL.

Use the following SQL to create a user_hobbies table with foreign keys using the SET NULL strategy.

DROP TABLE IF EXISTS user_hobbies;
CREATE TABLE user_hobbies (
  hobby_id INTEGER NOT NULL,
  user_id INTEGER,
  hobby VARCHAR(45) NOT NULL,
  PRIMARY KEY (hobby_id),
  CONSTRAINT fk_user
    FOREIGN KEY (user_id)
    REFERENCES users (user_id)
    ON DELETE SET NULL
    ON UPDATE SET NULL
);

Insert some rows into two tables:

DELETE FROM users;
DELETE FROM user_hobbies;
INSERT INTO users (user_id, name)
VALUES (1, 'Tim');
INSERT INTO user_hobbies (hobby_id, user_id, hobby)
VALUES (1, 1, 'Football'), (2, 1, 'Swimming');

Let’s take a look at the associated operation of the child table caused by the UPDATE and DELETE operation on the parent table:

• The UPDATE operate on the parent table

  UPDATE users
  SET user_id = 100
  WHERE user_id = 1;
  

  At this point the rows in the user_hobbies table:

  hobby_id | user_id |  hobby
  ----------+---------+----------
         1 |  <null> | Football
         2 |  <null> | Swimming
  (2 rows)

  After updating the value of the user_id column in the parent table, the value of the column user_id in those corresponding rows in the user_hobbies table is set to NULL.

• The DELETE operate on the parent table

  Since the above example modifies the data of the table, we reinitialize the data of the two tables:

  DELETE FROM users;
  DELETE FROM user_hobbies;
  INSERT INTO users (user_id, name)
  VALUES (1, 'Tim');
  INSERT INTO user_hobbies (hobby_id, user_id, hobby)
  VALUES (1, 1, 'Football'), (2, 1, 'Swimming');
  

  DELETE FROM users
  WHERE user_id = 1;
  

  At this point the rows in the user_hobbies table:

  hobby_id | user_id |  hobby
  ----------+---------+----------
         1 |  <null> | Football
         2 |  <null> | Swimming
  (2 rows)

  After the rows with user_id 1 was deleted, the value of the column user_id in those corresponding rows in the user_hobbies table is set to NULL.

Self-referencing foreign keys

Sometimes, the child table and the parent table may be the same table. A foreign key in such a table is called a self-referential foreign key.

Typically, self-referential foreign keys are defined in tables that represent tree-like data structures. For example, the following is a table representing categories:

CREATE TABLE category (
  category_id INTEGER PRIMARY KEY,
  category_name VARCHAR(45),
  parent_category_id INTEGER,
  CONSTRAINT fk_category FOREIGN KEY (parent_category_id)
    REFERENCES category (category_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

In this table, the column parent_category_id is a foreign key. It references the category_id column of the category table.

This table implements an infinite hierarchy of classification trees. A category can have multiple subcategories, and a category can have 0 or 1 parent category;

Conclusion

In this article, we introduced what foreign keys are, their rules, and how to use them in PostgreSQL. Here are the main points of this article:

• Foreign keys are used to define constraints between two entities. Foreign keys are useful for ensuring data integrity.
• The table that defines the foreign key is called the child table, and the table referenced by the foreign key is called the parent table.
• The foreign key refers to the primary key or unique key column of the parent table.
• The ALTER TABLE ... ADD FOREIGN KEY ... statement can be used to add foreign keys.
• The ALTER TABLE ... DROP CONSTRAINT ... statement can be used to delete foreign keys.
• A self-referential foreign key refers to the current table itself. This implements tree-like data structures.

In this article, we will look into the PostgreSQL Foreign key constraints using SQL statements. A foreign key is a column or a group of columns used to identify a row uniquely of a different table. The table that comprises the foreign key is called the referencing table or child table. And the table to that the foreign key references is known as the referenced table or parent table. A table can possess multiple foreign keys according to its relationships with other tables. 

Syntax:  FOREIGN KEY (column) REFERENCES parent_table (table_name)

Let’s analyze the above syntax:

• First, specify the name for the foreign key constraint after the CONSTRAINT keyword. The CONSTRAINT clause is optional. If you omit it, PostgreSQL will assign an auto-generated name.
• Second, specify one or more foreign key columns in parentheses after the FOREIGN KEY keywords.
• Third, specify the parent table and parent key columns referenced by the foreign key columns in the REFERENCES clause.
• Finally, specify the delete and update actions in the ON DELETE and ON UPDATE clauses.

The delete and update actions determine the behaviors when the primary key in the parent table is deleted and updated. Since the primary key is rarely updated, the ON UPDATE action is not often used in practice. We’ll focus on the ON DELETE action.

PostgreSQL supports the following actions:

• SET NULL
• SET DEFAULT
• RESTRICT
• NO ACTION
• CASCADE

Example:

The following statements create the customers and contacts tables:

DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS contacts;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
);

In this example, the customer table is the parent table and the contacts table is the child table. Each customer has zero or many contacts and each contact belongs to zero or one customer. The customer_id column in the contacts table is the foreign key column that references the primary key column with the same name in the customer’s table. The following foreign key constraint fk_customer in the contacts table defines the customer_id as the foreign key:

CONSTRAINT fk_customer
   FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)

Because the foreign key constraint does not have the ON DELETE and ON UPDATE action, they default to NO ACTION.

NO ACTION

The following inserts data into the customers and contacts tables:

INSERT INTO customers(customer_name)
VALUES('GeeksforGeeks org'),
      ('Dolphin LLC');       
       
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1, 'Raju kumar', '(408)-111-1234', 'raju.kumar@geeksforgeeks.org'),
      (1, 'Raju kumar', '(408)-111-1235', 'raju.kumar@bluebird.dev'),
      (2, 'Nikhil Aggarwal', '(408)-222-1234', 'nikhil.aggarwalt@geeksforgeeks.org');

The following statement deletes the customer id 1 from the customers table:

DELETE FROM customers
WHERE customer_id = 1;

Because of the ON DELETE NO ACTION, PostgreSQL issues a constraint violation because the referencing rows of the customer id 1 still exist in the contacts table:

ERROR:  update or delete on table "customers" violates foreign key constraint
        "fk_customer" on table "contacts"
DETAIL:  Key (customer_id)=(1) is still referenced from table "contacts".
SQL state: 23503

The RESTRICT action is similar to the NO ACTION. The difference only arises when you define the foreign key constraint as DEFERRABLE with an INITIALLY DEFERRED or INITIALLY IMMEDIATE mode. 

SET NULL

The ON DELETE CASCADE automatically sets NULL to the foreign key columns in the referencing rows of the child table when the referenced rows in the parent table are deleted. The following statements drop the sample tables and re-create them with the foreign key that uses the SET NULL action in the ON DELETE clause:

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
      ON DELETE SET NULL
);

INSERT INTO customers(customer_name)
VALUES('GeeksforGeeks org'),
      ('Dolphin LLC');       
       
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1, 'Raju kumar', '(408)-111-1234', 'raju.kumar@geeksforgeeks.org'),
      (1, 'Raju kumar', '(408)-111-1235', 'raju.kumar@bluebird.dev'),
      (2, 'Nikhil Aggarwal', '(408)-222-1234', 'nikhil.aggarwalt@geeksforgeeks.org');

The following statements insert data into the customers and contacts tables:

INSERT INTO customers(customer_name)
VALUES('GeeksforGeeks org'),
      ('Dolphin LLC');       
       
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1, 'Raju kumar', '(408)-111-1234', 'raju.kumar@geeksforgeeks.org'),
      (1, 'Raju kumar', '(408)-111-1235', 'raju.kumar@bluebird.dev'),
      (2, 'Nikhil Aggarwal', '(408)-222-1234', 'nikhil.aggarwalt@geeksforgeeks.org');

To see how the SET NULL works, let’s delete the customer with id 1 from the customers table:

DELETE FROM customers
WHERE customer_id = 1;

Because of the ON DELETE SET NULL action, the referencing rows in the contacts table set to NULL. The following statement displays the data in the contacts table:

SELECT * FROM contacts;

It will result in the following:

As can be seen clearly from the output, the rows that have the customer_id 1 now have the customer_id sets to NULL

CASCADE

The ON DELETE CASCADE automatically deletes all the referencing rows in the child table when the referenced rows in the parent table are deleted. In practice, the ON DELETE CASCADE is the most commonly used option.  The following statements recreate the sample tables. However, the delete action of the fk_customer changes to CASCADE:

DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL,
   phone VARCHAR(15),
   email VARCHAR(100),
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
      ON DELETE CASCADE
);

INSERT INTO customers(customer_name)
VALUES('GeeksforGeeks org'),
      ('Dolphin LLC');       
       
INSERT INTO contacts(customer_id, contact_name, phone, email)
VALUES(1, 'Raju kumar', '(408)-111-1234', 'raju.kumar@geeksforgeeks.org'),
      (1, 'Raju kumar', '(408)-111-1235', 'raju.kumar@bluebird.dev'),
      (2, 'Nikhil Aggarwal', '(408)-222-1234', 'nikhil.aggarwalt@geeksforgeeks.org');

The following statement deletes the customer id 1:

DELETE FROM customers
WHERE customer_id = 1;

Because of the ON DELETE CASCADE action, all the referencing rows in the contacts table are automatically deleted:

SELECT * FROM contacts;

This will result in the following:

SET DEFAULT

The ON DELETE SET DEFAULT sets the default value to the foreign key column of the referencing rows in the child table when the referenced rows from the parent table are deleted. To add a foreign key constraint to the existing table, you use the following form of the ALTER TABLE statement:

ALTER TABLE child_table 
ADD CONSTRAINT constraint_name 
FOREIGN KEY (fk_columns) 
REFERENCES parent_table (parent_key_columns);

When you add a foreign key constraint with ON DELETE CASCADE option to an existing table, you need to follow these steps:

First, drop existing foreign key constraints:

ALTER TABLE child_table
DROP CONSTRAINT constraint_fkey;

Then, add a new foreign key constraint with ON DELETE CASCADE action:

ALTER TABLE child_table
ADD CONSTRAINT constraint_fk
FOREIGN KEY (fk_columns)
REFERENCES parent_table(parent_key_columns)
ON DELETE CASCADE;

Issue

I have two tables created with flask sqlalchemy below — it is a ONE TO ONE RELATIONSHIP:

class Logo(db.Model):
    __tablename__ = "logo"
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(100))
    data = db.Column(db.LargeBinary)
    username = db.Column(db.String(100), db.ForeignKey("users.username"))

    users = db.relationship("User", backref=backref("logo", uselist=False))

    def __init__(self, filename: str, data, username: str):
        self.filename = filename
        self.data = data
        self.username = username

    def __repr__(self) -> str:
        return "<Logo (filename='{}', username='{}')>".format(
            self.filename, self.username
        )


class User(UserMixin, db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(100), unique=True)
    password = db.Column(
        db.String(200), primary_key=False, unique=False, nullable=False
    )
    is_admin = db.Column(db.Boolean, default=False, nullable=True)

    def __init__(
        self,
        username: str,
        password: str,
        is_admin: bool = False,
    ):
        self.username = username
        self.password = self.set_password(password)
        self.is_admin = is_admin

    def get_id(self):
        return self.username

    def set_password(self, password: str) -> str:
        return generate_password_hash(password, method="sha256")

    def check_password(self, password: str):
        return check_password_hash(self.password, password)

    def __repr__(self) -> str:
        return "<User {}>".format(self.username)

I would like to update the user table in a case when the user would like to have a new username:

user01 = User.query.filter_by(username="user01").first()
logo = Logo.query.filter_by(username="user01").first()

new_username= "newusertest"
user01.username = new_username
logo.users = user01

logo.username = new_username

db.session.add(user01)
db.session.add(logo)


db.session.commit()

The db.session.commit throws the following error:

IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "users" violates foreign key constraint "logo_username_fkey" on table "logo"
DETAIL:  Key (username)=(user01) is still referenced from table "logo".

[SQL: UPDATE users SET username=%(username)s WHERE users.id = %(users_id)s]
[parameters: {'username': 'newusertest', 'users_id': 2}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

The error says the logo table still has the old username but I have updated it and I don’t know why that shows up again, I have spent the last 2 hours debugging and trying different stuff but nothing works please help out!

Solution

You could temporarily make the foreign key constraint deferrable and make the update in psql. Say we have these tables:

test# d parent
                              Table "public.parent"
 Column │       Type        │ Collation │ Nullable │           Default            
════════╪═══════════════════╪═══════════╪══════════╪══════════════════════════════
 id     │ integer           │           │ not null │ generated always as identity
 name   │ character varying │           │          │ 
Indexes:
    "parent_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "child" CONSTRAINT "child_pname_fkey" FOREIGN KEY (pname) REFERENCES parent(name)

test# d child
                               Table "public.child"
 Column │       Type        │ Collation │ Nullable │           Default            
════════╪═══════════════════╪═══════════╪══════════╪══════════════════════════════
 id     │ integer           │           │ not null │ generated always as identity
 pname  │ character varying │           │          │ 
Foreign-key constraints:
    "child_pname_fkey" FOREIGN KEY (pname) REFERENCES parent(name)

then the statements would be

test# alter table child alter constraint child_pname_fkey deferrable;
ALTER TABLE
SET CONSTRAINTS
test# begin;
BEGIN
test#* set constraints child_pname_fkey deferred;
SET CONSTRAINTS
test#* update child set pname = 'Alice' where id = 1;
UPDATE 1
test#* update parent set name = 'Alice' where id = 1;
UPDATE 1
test#* commit;
COMMIT
test# alter table child alter constraint child_pname_fkey not deferrable;
ALTER TABLE
test#

Deferring the constraint means updates are evaluated at the end of the transaction rather than immediately, so the the point of view of the database the columns are not out of sync.

The long term solution is to use users.id as the foreign key, as it is less likely to change.

Answered By — snakecharmerb