Database (MySQL): Missing constraints/foreign keys

[fgusinde]

I just looked at the data model and tables and I wondered why there are foreign keys missing, e.g.

CREATE TABLE oc_group_user (
gid varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
uid varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (gid,uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

I think there MUST be two foreign keys, one to oc_groups and one to oc_users.

But I see that there is NO foreign key in whole data model.
I want to tell you to change this.

[fgusinde]

Sample:
MariaDB [owncloud]> ALTER TABLE oc_group_user ADD FOREIGN KEY (gid) REFERENCES oc_groups(gid);
Query OK, 1 row affected (1.12 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [owncloud]> ALTER TABLE oc_group_user ADD FOREIGN KEY (uid) REFERENCES oc_users(uid);
Query OK, 1 row affected (0.64 sec)
Records: 1 Duplicates: 0 Warnings: 0

[fgusinde]

Another one:
MariaDB [owncloud]> ALTER TABLE oc_group_admin ADD FOREIGN KEY(gid) REFERENCES oc_groups(gid);
Query OK, 0 rows affected (0.69 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [owncloud]> ALTER TABLE oc_group_admin ADD FOREIGN KEY(uid) REFERENCES oc_users(uid);
Query OK, 0 rows affected (0.80 sec)
Records: 0 Duplicates: 0 Warnings: 0

[fgusinde]

It looks like these columns are candidats for foreign keys, is it possible to use always the same column-name?

+-------------------+------------------+-------------+
| TABLE_NAME | COLUMN_NAME | COLUMN_TYPE |
+-------------------+------------------+-------------+
| oc_activity | affecteduser | varchar(64) |
| oc_activity | user | varchar(64) |
| oc_activity_mq | amq_affecteduser | varchar(64) |
| oc_files_trash | user | varchar(64) |
| oc_locks | userid | varchar(64) |
| oc_preferences | userid | varchar(64) |
| oc_privatedata | user | varchar(64) |
| oc_properties | userid | varchar(64) |
| oc_share_external | user | varchar(64) |
+-------------------+------------------+-------------+

I think they all belong (reference) to oc_users::uid, right?

[DeepDiver1975]

Thanks a lot for looking into this.

ownCloud's database migration mechanism does not support foreign keys at the moment.
This has been widely discussed in other issues

[fgusinde]

Can you please gibt me the corresponding issue-numbers?

[VicDeo]

@fgusinde #5756 (comment)

[wshadow]

For now this can only be closed as 'Won't fix'.
Personally I don't think that is the right decision, but that's how it is for now.

@karlitschek please reconsider this. Foreign keys are important for DB integrity. Vic already linked to the other issue where we discussed this in more detail. If you want me to write a paper on 'why foreign keys are important', just tell me.