Schema: name all constraints, not just PKs (before v1.0)

[Al2Klimov]

Advantages

• Schema upgrades affecting constraints can be crafted by just reading the current schema
• Schema upgrades don't have to rely on auto-generated constraint names don't change(?) across RDBMS versions
• Schema upgrades don't have to rely on auto-generated constraint names don't differ(?) in MySQL/MariaDB

See also

• The Pitfalls of Using System-Named Constraints in SQL Server https://www.madeiradata.com/post/the-pitfalls-of-using-system-named-constraints-in-sql-server
• Beware of the System Generated Constraint Name https://www.databasejournal.com/ms-sql/beware-of-the-system-generated-constraint-name/
• The Importance of Naming Constraints https://alembic.sqlalchemy.org/en/latest/naming.html

[Al2Klimov]

• Context: Currently I'm adding a MySQL Schema? #154 and some constraints explicitly say "constraint may be relaxed in the future" while others are complicated enough CHECKs to assume the same possibility.

[Al2Klimov]

Fun fact: some are already named, especially in incident_contact.

[Al2Klimov]

Obviously, the auto generated constraint names differ across RDBMS:

MariaDB [idb]> show create table rotation_member\G
*************************** 1. row ***************************
       Table: rotation_member
Create Table: CREATE TABLE `rotation_member` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rotation_id` bigint(20) NOT NULL,
  `contact_id` bigint(20) DEFAULT NULL,
  `contactgroup_id` bigint(20) DEFAULT NULL,
  `position` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rotation_id` (`rotation_id`,`position`),
  UNIQUE KEY `rotation_id_2` (`rotation_id`,`contact_id`),
  UNIQUE KEY `rotation_id_3` (`rotation_id`,`contactgroup_id`),
  CONSTRAINT `CONSTRAINT_1` CHECK (if(`contact_id` is null,0,1) + if(`contactgroup_id` is null,0,1) = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0,001 sec)

MariaDB [idb]>

Column      |  Type   | Collation | Nullable |                   Default
-----------------+---------+-----------+----------+---------------------------------------------
 id              | bigint  |           | not null | nextval('rotation_member_id_seq'::regclass)
 rotation_id     | bigint  |           | not null |
 contact_id      | bigint  |           |          |
 contactgroup_id | bigint  |           |          |
 position        | integer |           | not null |
Indexes:
    "pk_rotation_member" PRIMARY KEY, btree (id)
    "rotation_member_rotation_id_contact_id_key" UNIQUE CONSTRAINT, btree (rotation_id, contact_id)
    "rotation_member_rotation_id_contactgroup_id_key" UNIQUE CONSTRAINT, btree (rotation_id, contactgroup_id)
    "rotation_member_rotation_id_position_key" UNIQUE CONSTRAINT, btree (rotation_id, "position")
Check constraints:
    "rotation_member_check" CHECK (num_nonnulls(contact_id, contactgroup_id) = 1)
Foreign-key constraints:
    "rotation_member_contact_id_fkey" FOREIGN KEY (contact_id) REFERENCES contact(id)
    "rotation_member_contactgroup_id_fkey" FOREIGN KEY (contactgroup_id) REFERENCES contactgroup(id)
    "rotation_member_rotation_id_fkey" FOREIGN KEY (rotation_id) REFERENCES rotation(id)
Referenced by:
    TABLE "timeperiod_entry" CONSTRAINT "timeperiod_entry_rotation_member_id_fkey" FOREIGN KEY (rotation_member_id) REFERENCES rotation_member(id)

But that violates this:

Same schemes are a must.

– @lippserd Icinga/icingadb#221 (comment)

Also, at least MariaDB numbers them. I guess, a future difference between initially imported and upgraded schema is pre-programmed.

[Al2Klimov]

Postgres enumerates them too (incident_history_incident_id_fkey1), but not that often:

Indexes:
    "pk_incident_history" PRIMARY KEY, btree (id)
    "idx_incident_history_time_type" btree ("time", type)
Foreign-key constraints:
    "incident_history_channel_id_fkey" FOREIGN KEY (channel_id) REFERENCES channel(id)
    "incident_history_contact_id_fkey" FOREIGN KEY (contact_id) REFERENCES contact(id)
    "incident_history_contactgroup_id_fkey" FOREIGN KEY (contactgroup_id) REFERENCES contactgroup(id)
    "incident_history_event_id_fkey" FOREIGN KEY (event_id) REFERENCES event(id)
    "incident_history_incident_id_fkey" FOREIGN KEY (incident_id) REFERENCES incident(id)
    "incident_history_incident_id_fkey1" FOREIGN KEY (incident_id, rule_escalation_id) REFERENCES incident_rule_escalation_state(incident_id, rule_escalation_id)
    "incident_history_rule_escalation_id_fkey" FOREIGN KEY (rule_escalation_id) REFERENCES rule_escalation(id)
    "incident_history_rule_id_fkey" FOREIGN KEY (rule_id) REFERENCES rule(id)
    "incident_history_schedule_id_fkey" FOREIGN KEY (schedule_id) REFERENCES schedule(id)