How to fix Integrity constraint violation in PostgreSQL

-- Find all constraints on a table:
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'your_table';

-- Find columns involved in a specific constraint:
SELECT column_name
FROM information_schema.key_column_usage
WHERE table_name = 'your_table' AND constraint_name = 'constraint_name';

-- Find foreign key relationships:
SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.referential_constraints
WHERE table_name = 'your_table';

-- Check if the value exists in the referenced table:
SELECT * FROM parent_table WHERE id = <referenced_value>;

-- For the operation you're trying:
-- If it doesn't exist, you have two options:

-- Option 1: Insert the missing parent row first
INSERT INTO parent_table (id, name) VALUES (<value>, 'Parent Name');

-- Then retry your original INSERT:
INSERT INTO child_table (parent_id, data) VALUES (<value>, 'Child Data');

-- Option 2: Insert with a valid parent ID that exists:
SELECT id FROM parent_table LIMIT 5; -- Find a valid parent ID
-- Use one of these valid IDs in your INSERT

-- Find duplicates in the target column:
SELECT column_name, COUNT(*) as count
FROM your_table
GROUP BY column_name
HAVING COUNT(*) > 1;

-- Check if the specific value you're inserting exists:
SELECT * FROM your_table WHERE unique_column = 'value';

-- If it exists:
-- Option 1: Use a different value that is unique
-- Option 2: Update the existing row instead of inserting
UPDATE your_table SET column = 'new_value' WHERE unique_column = 'value';

-- Option 3: Use ON CONFLICT to skip or update:
INSERT INTO your_table (unique_column, data)
VALUES ('value', 'data')
ON CONFLICT (unique_column) DO NOTHING;

-- Check which columns are NOT NULL:
SELECT column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 'your_table' AND is_nullable = 'NO';

-- Ensure your INSERT provides values for all NOT NULL columns:
INSERT INTO your_table (id, required_column, optional_column)
VALUES (1, 'value', NULL); -- required_column must have a value

-- If you're setting a column to NULL in UPDATE:
UPDATE your_table SET required_column = 'value' -- NOT NULL, so provide a value
WHERE id = 1;

-- If a column has a default, you can omit it from INSERT:
INSERT INTO your_table (id, name) -- status may have a DEFAULT
VALUES (1, 'name');

-- Find CHECK constraints on the table:
SELECT constraint_name, check_clause
FROM information_schema.check_constraints
WHERE constraint_name LIKE '%your_table%';

-- Or query the definition:
SELECT pg_get_constraintdef(oid)
FROM pg_constraint
WHERE relname = 'your_table' AND contype = 'c';

-- Verify your value meets the constraint:
-- Example: age >= 0 AND age <= 150
INSERT INTO users (name, age)
VALUES ('John', 25); -- Valid: 0 <= 25 <= 150

-- This would fail:
INSERT INTO users (name, age)
VALUES ('Jane', -5); -- Invalid: -5 < 0

-- For unique/primary key conflicts, skip the insert:
INSERT INTO users (id, email, name)
VALUES (1, '[email protected]', 'John')
ON CONFLICT (email) DO NOTHING;

-- Or update the existing row (UPSERT):
INSERT INTO users (id, email, name)
VALUES (1, '[email protected]', 'John Doe')
ON CONFLICT (email) DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = NOW();

-- For composite constraints:
INSERT INTO likes (user_id, post_id, created_at)
VALUES (5, 10, NOW())
ON CONFLICT (user_id, post_id) DO UPDATE SET
  created_at = NOW();

-- For primary key:
INSERT INTO users (id, email)
VALUES (1, '[email protected]')
ON CONFLICT (id) DO NOTHING;

-- Find which child tables reference this row:
SELECT constraint_name, table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.referential_constraints
WHERE referenced_table_name = 'parent_table';

-- Check if there are child records:
SELECT * FROM child_table WHERE parent_id = <id>;

-- Option 1: Delete the child records first:
DELETE FROM child_table WHERE parent_id = <id>;
DELETE FROM parent_table WHERE id = <id>;

-- Option 2: If the constraint has ON DELETE CASCADE, it should auto-delete:
-- Verify the constraint definition:
SELECT pg_get_constraintdef(oid) FROM pg_constraint
WHERE conname = 'constraint_name';

-- If not CASCADE, the parent row cannot be deleted while children exist.
-- Either keep the parent or delete children first.