Search code examples
sql-servercompound-index

multiple constraint keys

if i have tabled:

  • Resource (id (PK), name)
  • Manager (id(PK), resource_id (FK), manager_resource_ID(FK))

Should resource_id and manager_id both be foreign keys into the Resource table.

i obviously dont want to enter any values in each of those columns that are not proper resources

when i add the first relationship (resource_id <-> id) it works fine but

when i add the second one (manager_resource_id <-> id) it fails with the error:

Unable to create relationship [ . . .] The ALTER TABLE statement conflicted with the FOREIGN KEY constraint [... ]. The conflict occured in table Resource, column id

or do i need to break this out into 3 tables?

Resource(id, first, last)
Resource_manager(id, resource_id, manager_ID)
Manager(id)

Solution

  • Just a hint:

    resourcemngr_model_01

    UPDATE:

    If your model has employee-manager as many-to-many (bit unusual) then you could do:

    resourcemngr_model_02

    CREATE TABLE Employee
  ( 
   EmployeeID int NOT NULL
  ,[Name] varchar(50)
  )
go  
ALTER TABLE Employee ADD
CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED (EmployeeID ASC)
go   

CREATE TABLE Manager
  ( 
   EmployeeID int NOT NULL
  ,ManagerID int NOT NULL
  )
go  
ALTER TABLE Manager ADD
 CONSTRAINT PK_Manager PRIMARY KEY CLUSTERED (EmployeeID ASC, ManagerID ASC)
,CONSTRAINT FK1_Manager FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
,CONSTRAINT FK2_Manager FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID)
,CONSTRAINT chk_Manager CHECK (EmployeeID <> ManagerID) 
go