CODEWITHSUNDEEP
sqldatabasedatabase-designconstraints
shadeglare
shadeglare

Reputation: 7536

SQL complex contraints

I'm working on a little database project and have a problem with organizing complex constraints for this data schema.

Schema

Here I use BodyPartTag to describe accepted LayerTags in an instance of BodyPart. The problem is if I'm going to build a BodyPart entity I have no direct constraints from the associated BodyPartTag. I can create a BodyPart called 'Red Eyes' which BodyPartTag is 'Eyes' but I can attach to BodyPart any Layer even if 'Eyes' BodyPartTag accepts only 'Left Eye', 'Right Eye' LayerTags.

How could I reorganize tables to add these constraints? At the moment I use a 'programatic' solution but it requires much coding that seems not correct for this kind of tasks.

Details: the white dot is ONE and the black dot is MANY.

Upvotes: 2

Views: 75

Answers (1)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28728

You can do this with composite keys rather than surrogate keys. It's not necessary a great way to do it, but if you are working with a schema like this it will provide a data constraint as specified.

Firstly, add the 'tag' keys to the primary keys of the Layer and BodyPart tables, so that the primary key is now composed of two values, i.e.

Layer table:    PK_Layer (Id, LayerTagId)
BodyPart table: PK_BodyPart (Id, BodyPartTagId)

Then create your BodyPartLayer table using those combined keys (2 values each), rather than the current key (single value). The BodyPartLayer table becomes a 4 value table with columns

-- The two 'bodypart' values have foreign key back to the `BodyPart` table, 
BodyPartId   
BodyPartTagId

-- and the two 'layer' values have foreign key back to the `Layer` table.
LayerId      
LayerTagId

Finally, create a foreign key constraint from the BodyPartLayer table to the BodyPartSchema table. The key will be like

BodyPartLayer table: FK_BodyPartLayer_BodyPartSchema (BodyPartTagId, LayerTagId)

This will ensure that BodyPart/Layer combinations can only be linked when their respective Tags are also linked. Not particularly pretty, but will achieve your requirement.

Upvotes: 1

Related Questions