📋

Description

Error 23503 signifies a foreign key constraint violation. This typically occurs when a database operation attempts to reference a non-existent primary key in a parent table, or when a parent record is deleted while child records still reference it, depending on the constraint's ON DELETE/UPDATE action.

💬

Error Message

foreign key violation

🔍

Known Causes

4 known causes

⚠️

Referencing Non-Existent Parent Record

You are attempting to insert or update a row in a child table with a foreign key value that does not exist in the referenced primary key column of the parent table.

⚠️

Deleting Referenced Parent Record

An attempt was made to delete a row from the parent table that is still referenced by one or more rows in a child table, and the foreign key constraint is set to RESTRICT or NO ACTION.

⚠️

Mismatched Data Types or Values

The data type of the foreign key column does not exactly match the primary key, or there are subtle differences in values (e.g., case sensitivity, leading/trailing spaces) preventing a proper match.

⚠️

Incorrect Foreign Key Definition

The foreign key constraint itself might be incorrectly defined, referencing the wrong columns or tables, leading to unexpected violation errors.

🛠️

Solutions

5 solutions available

1. Insert Parent Record First easy

Create the referenced row before the child

1

Check if parent exists

SELECT * FROM users WHERE id = 123;

2

Insert parent first

INSERT INTO users (id, name) VALUES (123, 'John');
INSERT INTO orders (user_id, total) VALUES (123, 99.99);

2. Use Valid Foreign Key Value easy

Reference an existing parent record

1

Find valid parent IDs

SELECT id FROM users ORDER BY id;

2

Use existing ID in insert

INSERT INTO orders (user_id, total) VALUES (1, 99.99);

3. Allow NULL for Optional Relationship easy

Use NULL when parent is optional

1

If FK allows NULL, insert without parent

INSERT INTO orders (user_id, total) VALUES (NULL, 99.99);

2

Make FK nullable if needed

ALTER TABLE orders ALTER COLUMN user_id DROP NOT NULL;

4. Delete Child Records First (for DELETE errors) easy

Remove dependent records before parent

1

Delete children first

DELETE FROM orders WHERE user_id = 123;
DELETE FROM users WHERE id = 123;

2

Or use CASCADE on FK

ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

5. Defer Constraint Checking advanced

Check FK at commit instead of statement

1

Create deferrable constraint

ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users(id)
  DEFERRABLE INITIALLY DEFERRED;

2

Insert in any order within transaction

BEGIN;
INSERT INTO orders (user_id, total) VALUES (123, 99.99);
INSERT INTO users (id, name) VALUES (123, 'John');
COMMIT;  -- FK checked here

🔗

Related Errors

5 related errors

PostgreSQL Error 23503: Foreign Key Constraint Violation