Adding constraints on MS SQL

bambera

Hi,
Hoping someone can help me with the following MS SQL problem as I’m fairly new to it:
I have two tables (Apartments and Tenants).
In the Apartments table, there is a field called OccupierID which has a Foreign Key connection to TenantID in the Tenants table.



The rule is that each TenantID can only be entered into the Apartments table OccupierID once so I used a UNIQUE constraint on OccupierID but this is causing a problem now as I need to allow for NULL to be entered if the Appartment is not occupied.



Is there another check I can use instead of a UNIQUE constraint that will allow for NULL entries but not a repeat of a TenantID?
(I haven’t learnt about triggers yet but would this be what I need?)



Thanks in advance.

Graham

Take a look at this:

https://www.mssqltips.com/sqlservertip/2380/sql-server-database-design-with-a-one-to-one-relationship/

Are you sure that's what you want though? What happens when an apartment has more than one tenant?

seamus

You need a reference table here. So you have your Apartments and Tenants tables, and then a third table - ApartmentTenants.

In this table are just two columns - an ApartmentID and the TenantID associated with it. Both are foreign keys, and you have UNIQUE constraint - the TenantID.

This means that a tenant can only be an occupier of one apartment. If you want each apartment to only have one occupier, you have a second UNIQUE constraint on the ApartmentID.

shakedown

Or remodel your tables - have ApartmentID on the Tenants table and get rid of OccupierID on Apartments. Then you don't even need any unique constraints (assuming you have a primary key on Tenants).

amen

all great ideas of which the third table ApartmentTenants is the best idea.

but what about the case of having a tenant move through multiple apartments over several years ?

and what about rental periods ?

Each apartment may be rented multiple times over multiple tenants or even the same time on multiple leases.

You model needs to cater for all of these cases

don;t forget landlords who may own multiple properties

beauf

And tenants who leave and come back to the same property repeatedly. Maybe a "lease" table which would have properties like a start and end. The closer you model it to real life the easier it will be to model.