Composite Constraints (Multi-column)
Hi folks,
I wanted to see if anyone else had run into the scenario of figuring out how to deal with composite (or multi-column) constraints. Some not-too-deep research shows that, for our RDBMS in question, there are four table constraints that can be applied to more than one column simultaneously. That is to say, PK, FK, Unique, and Check constraints can apply to columns A, B, and C individually - A must be unique, B must be unique, and C must be unique for all rows in the table (something that could be easily handled by Collibra OOTB True/False attributes with the exception of the Check constraint). However, they could also be constrained to be unique as a composed or composite set for columns A, B, and C (the combination of A, B, and C together must be unique for all rows in the table).
We do have the FK Asset and FK Complex Relationship Type OOTB, but this is for linking two or more columns in two or more tables with specific roles for the associated PK and FK relationship.
As such, we’ve created a Composite Constraint asset type with:
- A relation to the constrained table
- A relation to the columns identified by the constraint
- A select from values attribute which allows for PK, FK, Unique, and Check constraint types
- A few other attributes that are necessary, but not central to the discussion
I have two questions for any interested parties reading this post:
- What are your thoughts on this approach? Have others taken a similar approach or are we off base and/or making a mistake with this operational model addition?
- Would you recommend still marking the standard, OOTB unique attribute with a True in the event of a composite unique constraint? Providing a true or a false to the column-level attribute is misleading in either direction as far as I can tell as it could be interpreted as either this column must be unique (not true if only a composite constraint) or it has no requirements for uniqueness (true as an individual column, but not as part of the composite constraint). My gut is leaning toward setting this attribute to False and training users to look for the composite constraint relation if it appears. Thoughts?
Thank you,
Joe
