How do I automatically detect and delete duplicate Foreign-key constraints in Postgresql

postgres on delete
postgres on delete set null
postgres alter table on delete cascade
postgres foreign key
postgres cannot delete foreign key constraint
violates foreign key constraint postgres
postgres add foreign key to existing column
postgres delete row ignore foreign key

My Postgresql database has a large number of duplicate foreign key constraints on tables for example

"fkb43bb0b712b68565" FOREIGN KEY (owner_id) REFERENCES usr(id)
"fkb43bb0b71b63ed43" FOREIGN KEY (owner_id) REFERENCES usr(id)

They're doing the same thing but have unique names.

How can I automatically detect and delete such duplicates?

Thanks

J

SELECT
    pc.conname as constraint_name, 
    --conrelid as child_table_id,   
    pclsc.relname as child_table,
    --pc.conkey as child_column_id,
    pac.attname as child_column,
    --confrelid as parent_table_id,
    pclsp.relname as parent_table,
    --pc.confkey as parent_column_id,
    pap.attname as parent_column,   
    nspname as schema_name
FROM 
    (
    SELECT
         connamespace,conname, unnest(conkey) as "conkey", unnest(confkey)
          as "confkey" , conrelid, confrelid, contype
     FROM
        pg_constraint
    ) pc
    JOIN pg_namespace pn ON pc.connamespace = pn.oid
    -- and pn.nspname = 'panmydesk4400'
    JOIN pg_class pclsc ON pc.conrelid = pclsc.oid
    JOIN pg_class pclsp ON      pc.confrelid = pclsp.oid
    JOIN pg_attribute pac ON pc.conkey = pac.attnum    and pac.attrelid =       pclsc.oid
    JOIN pg_attribute pap ON pc.confkey = pap.attnum and pap.attrelid = pclsp.oid

ORDER BY pclsc.relname

above query will return all the foreign key constraint from that u simply delete the duplicate entries.

Note : if you remove the comments from above query you can view the relID and ColID and the query should work for all the schema in the database

Documentation: 9.5: Constraints, To that end, SQL allows you to define constraints on columns and tables. If possible, use UNIQUE, EXCLUDE, or FOREIGN KEY constraints to express of a unique constraint it is possible to store duplicate rows that contain a null value in at Adding a primary key will automatically create a unique B-tree index on the  My Postgresql database has a large number of duplicate foreign key constraints on tables for example "fkb43bb0b712b68565" FOREIGN KEY (owner_id) REFERENCES usr(id) "fkb43bb0b71b63ed43" FOREIGN KEY (owner_id) REFERENCES usr(id) They're doing the same thing but have unique names. How can I automatically detect and delete such duplicates? Thanks. J

Constraints are stored in pg_constraint, just query this view to find double constraints.

Documentation: 9.2: Constraints, To that end, SQL allows you to define constraints on columns and tables. So, to specify a named constraint, use the key word CONSTRAINT followed by an in the presence of a unique constraint it is possible to store duplicate rows that declaration of a foreign key constraint does not automatically create an index on​  I have a foreign key constraint that I'd like to alter. I'd rather not drop and re-create it due to the size of the table involved. All I need to do is add an ON UPDATE CASCADE. Is it ok to set confupdtype to 'c' in pg_constraint (and will this be all that's needed) or is it safer to drop and recreate the constraint? PG Version 8.0.3. Thanks a lot

SELECT
    array_agg(pc.conname) as duplicated_constraints, 
    pclsc.relname as child_table,
    pac.attname as child_column,
    pclsp.relname as parent_table,
    pap.attname as parent_column,   
    nspname as schema_name
FROM 
    (
    SELECT
     connamespace,conname, unnest(conkey) as "conkey", unnest(confkey)
      as "confkey" , conrelid, confrelid, contype
     FROM
        pg_constraint
    ) pc
    JOIN pg_namespace pn ON pc.connamespace = pn.oid
    JOIN pg_class pclsc ON pc.conrelid = pclsc.oid
    JOIN pg_class pclsp ON pc.confrelid = pclsp.oid
    JOIN pg_attribute pac ON pc.conkey = pac.attnum and pac.attrelid = pclsc.oid
    JOIN pg_attribute pap ON pc.confkey = pap.attnum and pap.attrelid = pclsp.oid
GROUP BY child_table, child_column, parent_table, parent_column, schema_name HAVING COUNT(*)>1
ORDER BY child_table, child_column

will list only duplicated foreign key constraints in Postgresql

How To Delete Duplicate Rows in PostgreSQL, CREATE TABLE basket( id SERIAL PRIMARY KEY, fruit VARCHAR(50) NOT NULL ); The find the duplicate rows, you use the following statement: if two different rows (a.id < b.id) have the same value in the fruit column. Primary Key · Foreign Key · CHECK Constraint · UNIQUE Constraint · NOT NULL Constraint  Summary: in this tutorial, you will learn about PostgreSQL foreign key and how to add foreign keys to tables using foreign key constraints. A foreign key is a field or group of fields in a table that uniquely identifies a row in another table. In other words, a foreign key is defined in a table that references to the primary key of the other table.

Unique Constraints and Check Constraints, Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce The Database Engine automatically creates a UNIQUE index to Therefore, if an attempt to insert a duplicate row is made, the The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain  Now, the name of the foreign key is $1 and this is what I have to delete: ALTER TABLE PARENTS DROP CONSTRAINT "$1"; /* the double quote is important */ On Friday 20 Feb 2004 16:56, you wrote:

Setting Up Referential Integrity, A foreign key is the column or columns that are compared against a primary find and delete all rows throughout the database that have foreign key values as a primary key, the database engine automatically creates an index on the designated columns that does not allow duplicate values or null values in the columns. Deleting duplicate rows using an immediate table. To delete rows using an immediate table, you use the following steps: Create a new table with the same structure as the one whose duplicate rows should be removed. Insert distinct rows from the source table to the immediate table. Drop the source table.

Inserting data into tables with referential constraints, Duplicate values for the parent key; If the parent key is a primary key, a null value for any Each non-null value you insert into a foreign key column must be equal to some DEPARTMENT ON DELETE RESTRICT ALTER TABLE CORPDATA. Open a ticket and download fixes at the IBM Support Portal · Find a technical  A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.