CODEWITHSUNDEEP
postgresqlforeign-keyspg-dump
Park JongBum
Park JongBum

Reputation: 1403

Awkward/wrong PostgreSQL foreign-key definition

As a database developer, I experienced this notice when I tried to make a data-only dump a PostgreSQL(10.1) database 'tlesson'.

Notice =>

  pg_dump: NOTICE: there are circular foreign-key constraints on this table:
  pg_dump: members

Dump command =>

  $ pg_dump -U postgres -d translesson -a

A 'tlesson' table 'members' constraint =>

  ALTER TABLE ONLY members
    ADD CONSTRAINT friend_fk FOREIGN KEY (friend_id) REFERENCES members(member_id);

Should I drop the 'friend_fk' constraint to remove the notice I'm having?

Upvotes: 4

Views: 2588

Answers (1)

user330315
user330315

Reputation:

If you always drop the entire database then this isn't a problem, because the generated SQL (or pg_restore) will enable (create) foreign keys only after all the data was loaded, so there is no problem in that case.

However if you only dump a single table without the FKs then, importing is only going to work if you manually drop the FK before restoring, then re-create it afterwards.

The reason is that it's nearly impossible to generate INSERT statements in the correct order if you have circular references

Upvotes: 5

Related Questions