How to add “on delete cascade” constraints?

by

I’m pretty sure you can’t simply add on delete cascade to an existing foreign key constraint. You have to drop the constraint first, then add the correct version. In standard SQL, I believe the easiest way to do this is to

  • start a transaction,
  • drop the foreign key,
  • add a foreign key with on delete cascade, and finally
  • commit the transaction

Repeat for each foreign key you want to change.

But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. For example

alter table public.scores
drop constraint scores_gid_fkey,
add constraint scores_gid_fkey
   foreign key (gid)
   references games(gid)
   on delete cascade;

If you don’t know the name of the foreign key constraint you want to drop, you can either look it up in pgAdminIII (just click the table name and look at the DDL, or expand the hierarchy until you see “Constraints”), or you can query the information schema.

select *
from information_schema.key_column_usage
where position_in_unique_constraint is not null

More Related Contents:

  1. Postgresql: Conditionally unique constraint
  2. Generating a UUID in Postgres for Insert statement?
  3. CONSTRAINT to check values from a remotely related table (via join etc.)
  4. Why can I create a table with PRIMARY KEY on a nullable column?
  5. Does a Postgres UNIQUE constraint imply an index?
  6. PostgreSQL – disabling constraints
  7. Hashing a String to a Numeric Value in PostgreSQL
  8. What is PostgreSQL equivalent to autoincrement?
  9. How do I specify a password to ‘psql’ non-interactively?
  10. INSERT with dynamic table name in trigger function
  11. How does JPA orphanRemoval=true differ from the ON DELETE CASCADE DML clause
  12. Use multiple conflict_target in ON CONFLICT clause
  13. PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
  14. How to find table creation time?
  15. What is the format for the PostgreSQL connection string / URL?
  16. Where does PostgreSQL store the database?
  17. PostgreSQL function returning multiple result sets
  18. How to add a new Column in a table after the 2nd or 3rd column in the Table using postgres?
  19. Test for null in function with varying parameters
  20. Postgres error on insert – ERROR: invalid byte sequence for encoding “UTF8”: 0x00
  21. How do you find the disk size of a Postgres / PostgreSQL table and its indexes
  22. no pg_hba.conf entry for host
  23. Heroku database restore issue
  24. user postgres launches process that takes all CPUs 100% usage
  25. PostgreSQL disable more output
  26. PostgreSQL array_agg order
  27. How do I get min, median and max from my query in postgresql?
  28. PostgreSQL jsonb, `?` and JDBC
  29. Syntax error at end of input in PostgreSQL
  30. Error when pushing data to Heroku: time zone displacement out of range

Leave a Comment