Bug #63254 Shared Primary/Unique indexes across tables
Submitted: 15 Nov 2011 0:47 Modified: 15 Nov 2011 0:54
Reporter: Andrew Foad Email Updates:
Status: Open Impact on me:
None 
Category:MySQL Server: DDL Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: distributed index, INDEX, primary index, sub-type, sub-typing, unique inex

[15 Nov 2011 0:47] Andrew Foad
Description:
This is a feature I am surprised hasn't been implemented (to my knowledge) into any of the mainstream DBMS products. As a data modeller I often build data models which include sub-typed table structures. However, there is no way of directly implementing enforced refential integrity.

There are workarounds that can be inplemented but none of them enforces a mutually exclusive distribution of primary/unique keys.

I suspect this feature would be of more use in those database engines that support FK contraints such as InnoDB.

How to repeat:
n/a

Suggested fix:
The solution seems clear. Is it possible to implement a primary or unique index structure that can be shared across a given set of tables. So if a primary key value is used in one table of the set the DBMS would automatically prevent that same value being recorded into any other table in the set. 

It seems such a simple concept I'm surprised it doesn't already exist as a standard SQL feature.
[15 Nov 2011 0:54] Andrew Foad
expanded tags