0

I am attempting to copy some tables in my Postgres database using like_options (https://www.postgresql.org/docs/9.5/static/sql-createtable.html), which states that using INCLUDING ALL is shorthand for copying CONSTRAINTS as well (info for queries found here and looking up FKs here

INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.

However, I am not seeing the expected FK-constraint from the original table after I run the copy - assuming that because the new table is empty I cannot add a FK-constraint referencing a column in another table?

create table lobby (LIKE command_center INCLUDING ALL);


SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND (tc.table_name='command_center' OR tc.table_name='lobby');

Original FK Constraint after table copy no new FK Constraint

jordan.baucke
  • 4,308
  • 10
  • 54
  • 77
  • The manual explicitly states the things that **are** copied. The consequence of that is that anything not listed in the manual is **not** copied - which means FK constraints are excluded. –  Aug 31 '17 at 06:19
  • Your assumption is wrong btw. An empty table can absolutely have foreign keys. –  Aug 31 '17 at 06:20

0 Answers0