Constraints are not loaded in Makefile and multiple primary keys are missing

[clemensrieder]

This seems to be a very non-SQL-specific problem but since we are working with Django every table needs to have a primary key field. This is why I want to share my workflow to remedy this situation:

1. The primary key field for the PATIENT table is set twice. Once in the postgres_add_indices.sql and once in the postgres_add_constraints.sql, which was run manually after running the Makefile. FYI: The latter is not part of the Makefile.
2. Comment lines 13-16 in postgres_add_constraints.sql
3. Save this in postgres_add_missing_constraints.sql:

ALTER TABLE admissiondrug DROP CONSTRAINT IF EXISTS admissiondrug_pk;
ALTER TABLE admissiondrug ADD CONSTRAINT admissiondrug_pk PRIMARY KEY (admissiondrugid);

ALTER TABLE allergy DROP CONSTRAINT IF EXISTS allergy_pk;
ALTER TABLE allergy ADD CONSTRAINT allergy_pk PRIMARY KEY (allergyid);

ALTER TABLE customlab DROP CONSTRAINT IF EXISTS customlab_pk;
ALTER TABLE customlab ADD CONSTRAINT customlab_pk PRIMARY KEY (customlabid);

ALTER TABLE infusiondrug DROP CONSTRAINT IF EXISTS infusiondrug_pk;
ALTER TABLE infusiondrug ADD CONSTRAINT infusiondrug_pk PRIMARY KEY (infusiondrugid);

ALTER TABLE intakeoutput DROP CONSTRAINT IF EXISTS intakeoutput_pk;
ALTER TABLE intakeoutput ADD CONSTRAINT intakeoutput_pk PRIMARY KEY (intakeoutputid);

ALTER TABLE medication DROP CONSTRAINT IF EXISTS medication_pk;
ALTER TABLE medication ADD CONSTRAINT medication_pk PRIMARY KEY (medicationid);

ALTER TABLE microlab DROP CONSTRAINT IF EXISTS microlab_pk;
ALTER TABLE microlab ADD CONSTRAINT microlab_pk PRIMARY KEY (microlabid);

ALTER TABLE note DROP CONSTRAINT IF EXISTS note_pk;
ALTER TABLE note ADD CONSTRAINT note_pk PRIMARY KEY (noteid);

ALTER TABLE nurseassessment DROP CONSTRAINT IF EXISTS nurseassessment_pk;
ALTER TABLE nurseassessment ADD CONSTRAINT nurseassessment_pk PRIMARY KEY (nurseassessid);

ALTER TABLE nursecare DROP CONSTRAINT IF EXISTS nursecare_pk;
ALTER TABLE nursecare ADD CONSTRAINT nursecare_pk PRIMARY KEY (nursecareid);

ALTER TABLE nursecharting DROP CONSTRAINT IF EXISTS nursecharting_pk;
ALTER TABLE nursecharting ADD CONSTRAINT nursecharting_pk PRIMARY KEY (nursingchartid);

ALTER TABLE physicalexam DROP CONSTRAINT IF EXISTS physicalexam_pk;
ALTER TABLE physicalexam ADD CONSTRAINT physicalexam_pk PRIMARY KEY (physicalexamid);

ALTER TABLE respiratorycare DROP CONSTRAINT IF EXISTS respiratorycare_pk;
ALTER TABLE respiratorycare ADD CONSTRAINT respiratorycare_pk PRIMARY KEY (respcareid);

ALTER TABLE respiratorycharting DROP CONSTRAINT IF EXISTS respiratorycharting_pk;
ALTER TABLE respiratorycharting ADD CONSTRAINT respiratorycharting_pk PRIMARY KEY (respchartid);

ALTER TABLE treatment DROP CONSTRAINT IF EXISTS treatment_pk;
ALTER TABLE treatment ADD CONSTRAINT treatment_pk PRIMARY KEY (treatmentid);

via psql "dbname=eicu user=eicu options=--search_path=eicu_crd" -v ON_ERROR_STOP=1 -f postgres_add_missing_constraints.sql or load it in any other way suitable for your workflow.

Hope this helps. Feel free to integrate this into this repository.