Search code examples
postgresqlmusicbrainz

How to apply the foreign key constraints to the musicbrainz database?

I’m having difficulty applying the foreign keys to the postgres musicbrainz database.
I used these excellent instructions to get me this far:
https://bitbucket.org/lalinsky/mbslave
I have all of the data loaded in postgres.
I have primary keys and indexes created.

However, when I try to apply the foreign keys, I get this strange error: 

musicbrainz@ip-10-217-5-18:/home/ubuntu/mbslave$ psql < sql/CreateFKConstraints.sql 
ERROR:  insert or update on table "annotation" violates foreign key constraint "annotation_fk_editor"
DETAIL:  Key (editor)=(51298) is not present in table "editor".

It seems to be erroring on the very first line of the SQL script:

ALTER TABLE annotation
   ADD CONSTRAINT annotation_fk_editor
   FOREIGN KEY (editor)
   REFERENCES editor(id);

I’m new to postgres and am having difficulty understanding this error message.

The first line leads me to believe that the foreign key constraint has already been created. But I don’t think it has.

musicbrainz=> SELECT
musicbrainz->     tc.constraint_name, tc.table_name, kcu.column_name, 
musicbrainz->     ccu.table_name AS foreign_table_name,
musicbrainz->     ccu.column_name AS foreign_column_name 
musicbrainz-> FROM 
musicbrainz->     information_schema.table_constraints AS tc 
musicbrainz->     JOIN information_schema.key_column_usage AS kcu
musicbrainz->       ON tc.constraint_name = kcu.constraint_name
musicbrainz->     JOIN information_schema.constraint_column_usage AS ccu
musicbrainz->       ON ccu.constraint_name = tc.constraint_name
musicbrainz-> WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='annotation';
 constraint_name | table_name | column_name | foreign_table_name | foreign_column_name 
-----------------+------------+-------------+--------------------+---------------------
(0 rows)

The second line in the error message is confusing. It sounds like it can’t find the id column in the editor table. But it is there.

musicbrainz=> \d editor
                                      Table "musicbrainz.editor"
       Column        |           Type           |                      Modifiers                      
---------------------+--------------------------+-----------------------------------------------------
 id                  | integer                  | not null default nextval('editor_id_seq'::regclass)
 name                | character varying(64)    | not null
 privs               | integer                  | default 0
 email               | character varying(64)    | default NULL::character varying
 website             | character varying(255)   | default NULL::character varying
 bio                 | text                     | 
 member_since        | timestamp with time zone | default now()
 email_confirm_date  | timestamp with time zone | 
 last_login_date     | timestamp with time zone | default now()
 edits_accepted      | integer                  | default 0
 edits_rejected      | integer                  | default 0
 auto_edits_accepted | integer                  | default 0
 edits_failed        | integer                  | default 0
 last_updated        | timestamp with time zone | default now()
 birth_date          | date                     | 
 gender              | integer                  | 
 area                | integer                  | 
 password            | character varying(128)   | not null
 ha1                 | character(32)            | not null
 deleted             | boolean                  | not null default false
Indexes:
    "editor_pkey" PRIMARY KEY, btree (id)
    "editor_idx_name" UNIQUE, btree (lower(name::text))

Suggestions?

Solution

  • The constraint:

    ALTER TABLE annotation
   ADD CONSTRAINT annotation_fk_editor
   FOREIGN KEY (editor)
   REFERENCES editor(id);

    means that in the table annotation, the column editor in that table must also exist in the table editor under the column id.

    Essentially, it's ensuring that all editor id's that are used in annotation are present in the database.

    This helps maintain referential integrity. Without the Foreign Key constraint, anything could be there, regardless of whether it was actually in the editor table or not, which could lead to a whole bunch of data-related issues.

    The error message:

    ERROR: insert or update on table "annotation" violates foreign key constraint "annotation_fk_editor" DETAIL: Key (editor)=(51298) is not present in table "editor".

    means that a row with the id of 51298 is not present in the table editor.

    Is any data present in that table?

    You can check in the FK constraint is enabled on annotations by doing a \d on that table like you did for editor. (You may need to do \d+ to get all the info.)

    Also, while I've not used MusicBrainz before, the BitBucket link contains two things which stand out to me which may be leading to your issue:

    1. The setup instructions on the main page do not seem to be applying the FK constraints
    2. The setup instructions also are making use of a Python wrapper called mbslave-remap-schema.py, which is doing some sed-like replacement of the SQL text to change schemas around and such, and piping the output of that into a wrapper of psql named mbslave-psql.py instead of psql directly
    3. The sql files all appear to be schema-related, and the data itself appears to be imported from a dump using a different wrapper: ./mbslave-import.py mbdump.tar.bz2 mbdump-derived.tar.bz2 (apparently there is not a whole-database dump, or at least not one that is readily apparent)