Search code examples
sqlsql-servert-sqltable-relationships

one to one relationship between 3 tables (back to back - 1 to 1 relationship)

There are 3 tables namely FN, ADM, USR representing a function, admin for the function, users who attend the function. Admin will be a user and ADM has to be inherited from the USR.
FN_I,USR_I should be the prim key for FN and USR.(This is referenced by many other tables)
ADMN is the new able added

enter image description here

I need to implement the following constraints.
  1. One Function can have only one admin.(1-1)
  2. One Admin can administer only one Function.(1-0/1)

I tried achieving 1 using by setting FN_ADM_I as foreign key to FN_I.
Could some one help me with constraints/relationships that can achieve both the requirement?

Solution

  • You may want to create a link table:

    FN_ADMIN_REL

FN_I, -- PK
ADMIN_I --PK

    where both fields are primary key with UNIQUE constraint:

    ALTER TABLE FN_ADMIN_REL
ADD CONSTRAINT uc_Func_Admin UNIQUE (FN_I, ADMIN_I)