Foreign key constraints on mh_user_settings and mh_series_property have no unique counterpiece

Steps to reproduce

While transfering the MySQL DDL (https://bitbucket.org/opencast-community/matterhorn/src/564c1f251d525b67f600e7fe244edd422bf170f2/docs/scripts/ddl/mysql5.sql?at=r%2F2.0.x) and Upgrade Script (https://bitbucket.org/opencast-community/matterhorn/src/564c1f251d525b67f600e7fe244edd422bf170f2/docs/upgrade/1.6_to_2.0/mysql5.sql?at=r/2.0.x) to Postgres, I noticed two problems with foreign keys:

The create table statement for table mh_user_settings contains contraints with foreign keys. 1:1 transferred to Postgres, this create table statement says:

CREATE TABLE "mh_user_settings" (
"id" bigint NOT NULL,
"setting_key" character varying(255) NOT NULL,
"setting_value" text NOT NULL,
"username" character varying(128) NOT NULL,
"organization" character varying(128) NOT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "FK_mh_user_setting_username" FOREIGN KEY ("username") REFERENCES "mh_user" ("username"),
CONSTRAINT "FK_mh_user_setting_org" FOREIGN KEY ("organization") REFERENCES "mh_user" ("organization")
);

When I run this comment, Postgres tells me:

ERROR: there is no unique constraint matching given keys for referenced table "mh_user"

This is because the mh_user table has a unique contraint on (username, organization), but not on username.

A similar problem happens with the mh_series_property table:

The create statement, 1:1 transferred to Postgres, is:

CREATE TABLE "mh_series_property" (
"organization" character varying(128) NOT NULL,
"series" character varying(128) NOT NULL,
"name" character varying(255) NOT NULL,
"value" text,
PRIMARY KEY ("organization", "series", "name")
– PRIMARY KEY ("organization", "series", "name"),
– CONSTRAINT "FK_mh_series_property_series" FOREIGN KEY ("series") REFERENCES "mh_series" ("id") ON DELETE CASCADE
);

Postgres gives me again an error for the same reason:

ERROR: there is no unique constraint matching given keys for referenced table "mh_series"

---------------------------------------------------

As no-one else had this problem before with MySQL, I assume that MySQL does not care about the fact of uniqueness. However, I am bringing this up as release blocker so that anyone with more insight has the chance to check if this might produce data inconsistencies.

---------------------------------------------------

As both tables are also created in the upgrade script, the change should also be applied there.

Fixed and reviewed
Your pinned fields
Click on the next to a field label to start pinning.

Assignee

Lukas Rohner

Reporter

Alexander Bias

Severity

Incorrect Documentation

Tags (folksonomy)