0

The situation is I have a Tenant table, and a tenant based status lookup table. Each with their own autonumber Primary keys.

Now I want to add a column to the Tenant table for a default Status that references the lookup table, the tenant should only be able to default to a status that is in that tenant, and for some reason the exact structure of the FK is messing with my head so I figured I'd ask in case I'm going crazy haha.

ex Tables:

Tenant:

tenantID Name defaultStatusID
1 Adam 1
2 Matt 7

Status:

tenantID statusID Name
1 1 New
1 2 Closed
1 3 Canceled
1 4 Done
2 5 Future
2 6 Removed
2 7 Open

So I was going to add the foreign key CONSTRAINT [FK_Tenant_Status] FOREIGN KEY ([defaultStatusID]) REFERENCES [dbo].[Status] ([statusID]) but I realized I would love some way to enforce that the defaultStatusID is from a status in the correct tenant and I'm not quite sure what the best method for that is?

Adding tenantID to the FK seems wrong because tenantID is not Foreign to that table and it would become circular? Unless it is actually that easy? CONSTRAINT [FK_Tenant_Status] FOREIGN KEY ([tenantID], [defaultStatusID]) REFERENCES [dbo].[Status] ([tenantID],[statusID])

I imagine maybe some type of constraint would be the way to go but I'm not quite sure.

Thank you for any help/comments!

1
  • There is not much response because still question is not clear.TenantID is PK in Tenant table or not ?Technically or theortically suppose I create TenantID as FK in Status table,suppose for a minute.Then how it become circular dependency? Do you mean to say that TenantID 1 can never be status 5,6 or 7?
    – KumarHarsh
    Feb 13 at 6:35

1 Answer 1

1

When you add the constraints required for identification purposes and row uniqueness, your model is something like this: enter image description here

If you had not shot yourself in the foot by using surrogates as the first thing, you would have started the modelling by identifying and using the actual keys as primary keys, and get something like this: enter image description here

Then, if you thoughtfully made the decision to use surrogates, you would do so while maintaining the type of relationship between Tenant and Status as identifying, and get something like this: enter image description here

Then everything else is simple enough. For example, you could model the default and active statuses like this: enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.