Skip to content

Instantly share code, notes, and snippets.

@mlibrodo
Created February 14, 2017 12:38
Show Gist options
  • Save mlibrodo/6f246c483e650dc716eba752a9d3c79a to your computer and use it in GitHub Desktop.
Save mlibrodo/6f246c483e650dc716eba752a9d3c79a to your computer and use it in GitHub Desktop.
makes sure that the FK constraints are pointing to the tables in the cloned schema
-- add FK constraint
-- set the search path to the dest schema so that FK contraints are defined with the source_schema qualifier
-- i.e. source_schema = public, desc_schema = schema_copy
-- then the FOREIGN KEY (company_id) REFERENCES companies(id) will be qualified as
-- FOREIGN KEY (company_id) REFERENCES public.companies(id)
SET SEARCH_PATH TO dest_schema;
FOR qry IN
SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname)
|| ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' '
|| replace(pg_get_constraintdef(ct.oid), source_schema, dest_schema) || ';'
FROM pg_constraint ct
JOIN pg_class rn ON rn.oid = ct.conrelid
WHERE connamespace = src_oid
AND rn.relkind = 'r'
AND ct.contype = 'f'
LOOP
EXECUTE qry;
END LOOP;
SET SEARCH_PATH TO source_schema;
@mlibrodo
Copy link
Author

Without the modification above the cloned schema will create the constraint against the table in the source schema.. notice confrelid are all the same.

                conname                | connamespace | conrelid | confrelid | confrelid |               pg_get_constraintdef
---------------------------------------+--------------+----------+-----------+-----------+---------------------------------------------------
 company_fkey |         2200 |  1176746 |   1176046 |   1176046 | FOREIGN KEY (company_id) REFERENCES companies(id)
 company_fkey |      1177136 |  1177400 |   1176046 |   1176046 | FOREIGN KEY (company_id) REFERENCES companies(id)
 company_fkey |      1177847 |  1178129 |   1176046 |   1176046 | FOREIGN KEY (company_id) REFERENCES companies(id)
 company_fkey |      1178596 |  1178878 |   1176046 |   1176046 | FOREIGN KEY (company_id) REFERENCES companies(id)

Compared to this which points the relation to another relid

                  conname                   | connamespace | conrelid | confrelid | confrelid |                               pg_get_constraintdef
--------------------------------------------+--------------+----------+-----------+-----------+-----------------------------------------------------------------------------------
 company_fkey      |         2200 |  1165331 |   1164631 |   1164631 | FOREIGN KEY (company_id) REFERENCES public.companies(id)
 company_fkey |      1165722 |  1165986 |   1165940 |   1165940 | FOREIGN KEY (company_id) REFERENCES test_fixtures_using_parameters.companies(id)
 company_fkey |      1166438 |  1166702 |   1166656 |   1166656 | FOREIGN KEY (company_id) REFERENCES test_fixtures_using_annotations.companies(id)
 company_fkey |      1167154 |  1167418 |   1167372 |   1167372 | FOREIGN KEY (company_id) REFERENCES companies(id)
select version();
                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.5 on x86_64-apple-darwin13.4.0, compiled by Apple LLVM version 5.1 (clang-503.0.38) (based on LLVM 3.4svn), 64-bit
(1 row)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment