I'm looking for some feedback on this one.
I've used this technique a few times in order to add some integrity to join tables. Specifically, I am using a unique constraint that uses a table PK as a prefix. Why do I do this? When each row of the table has unique information that necessarily constrains rows in a dependent table.
I augment the PK with the columns that embody the constraint information. Then, when I create a dependent table, I create it with the reference to the augmented unique constraint. Using the additional fields, I apply a check constraint to enforce the integrity of each row.
Here is a simple example: A list of containerized ships. (Can anyone tell I've been working with Docker?) Each ship has a different capacity for containers. I have containers to put on ships. The loading is stored in a manifest. Here's my model:
Unique constraint SHIP_PROJECTED_CONSTRAINT is the augmented key. MANIFEST has a reference to it to enforce the capacity limit of each ship.
The capacity limit is enforced by a check constraint - not visible here - with the condition
CONTAINER_ORDER BETWEEN 1 AND MAX_CONTAINERS
So this technique hasn't bit me... yet. Maybe it's something brand new... but StackExchange posts rather convince me otherwise. I don't remember seeing any discussions of an approach like this. That's why I'm posting it. Please comment via LinkedIn and help me to assess and critique this.