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.

Status

Assignee

Lukas Rohner

Reporter

Alexander Bias

Severity

Incorrect Documentation

Tags (folksonomy)

Components

Fix versions

Affects versions

2.0.0

Priority

Blocker
Configure