0

Is it possible to check a specific value of an existing constraint?

For example I want to check if a FOREIGN KEY Constraint is ON DELETE SET NULL or ON DELETE NO ACTION.

I only found solutions if a constraint is existing at all, but not for a specific value.

Maarkoize
  • 2,601
  • 2
  • 16
  • 34

2 Answers2

1

As dev-null-dweller suggests in their answer:

Simple way to get foreign keys for given table:

SELECT
    `column_name`, 
    `referenced_table_schema` AS foreign_db, 
    `referenced_table_name` AS foreign_table, 
    `referenced_column_name`  AS foreign_column 
FROM
    `information_schema`.`KEY_COLUMN_USAGE`
WHERE
    `constraint_schema` = SCHEMA()
AND
    `table_name` = 'your-table-name-here'
AND
    `referenced_column_name` IS NOT NULL
ORDER BY
    `column_name`;

Or another way, suggested by Lo Sauer, is:

SELECT * FROM information_schema.table_constraints 
         WHERE table_schema = 'dbname' AND table_name='mytable';
Community
  • 1
  • 1
sergio
  • 5,210
  • 7
  • 24
  • 46
  • @MarcelBalzer: no that won't work on Postgres due to the non-standard backticks, the non-existing `schema()` function and the fact that the `information_schema` tables are quite different between the two DBMS. –  Dec 10 '13 at 09:29
  • OK, but this works in Mysql and yours in PGSQL. Thanks both ;) – Maarkoize Dec 10 '13 at 09:33
1

For Postgres this would be something like:

select tc.table_schema||'.'||tc.table_name as referencing_table, 
       ctu.table_schema||'.'||ctu.table_name as referenced_table_name,
       rc.update_rule, 
       rc.delete_rule
from information_schema.table_constraints tc
  join information_schema.referential_constraints rc 
    on tc.constraint_catalog = rc.constraint_catalog 
   and tc.constraint_schema = rc.constraint_schema 
   and tc.constraint_name = rc.constraint_name
  join information_schema.constraint_table_usage ctu 
    on ctu.constraint_catalog = rc.unique_constraint_catalog
   and ctu.constraint_schema = rc.unique_constraint_schema
   and ctu.constraint_name = rc.unique_constraint_name
where tc.table_name = 'foobar'
  and tc.table_schema = 'public'
  and tc.constraint_type = 'FOREIGN KEY'

But that won't work on MySQL