How to write Tables in SQL with a Disjoint Connect

Question

So I have three tables which I want to create with a disjoint Connection. These are person, Tenant and employee. So every person must either be a Tenant or an employee, never both. I create a Person table with the PersonID primary key, and tenant and Employee as two separate tables whose Primary Keys are both Foreign Keys linking back to the Person(PersonID). How do i create a Disjoint here where every Person can only be either a Tenant or an Employee? Thanks

Answer 1

You can try this, which will pick out every person who is EITHER a person OR an employee:

SELECT *
FROM (SELECT person.*,
             CASE WHEN employee.id IS NULL THEN 0 ELSE 1 END AS is_employee,
             CASE WHEN tenant.id   IS NULL THEN 0 ELSE 1 END AS is_tenant
      FROM person LEFT JOIN employee on person.id = employee.id
                  LEFT JOIN tenant on person.id = tenant.id) AS tA
WHERE tA.is_employee <> tA.is_person 

Make sure that the id columns are all indexed.