multiple constraint keys

Question

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)

Damir Sudarevic · Accepted Answer · 2016-08-13T17:07:52.167

1

Just a hint:

UPDATE:

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

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

edited Aug 13 '16 at 17:07

answered Dec 27 '09 at 14:26

Damir Sudarevic

21,891
3
47
71

if i am foreign keying back to the same table for both the resource id and manager id do i really need 3 tables ? – leora Dec 27 '09 at 14:45
`Resource` is for resources; `Employee` for employees, `ManagerID (BossID)` is for corporate hierarchy (who reports to whom). `ResourceManager` describes who is in charge of what -- I assume that resource is not an employee, but a thing or an "external object". – Damir Sudarevic Dec 27 '09 at 21:25
sorry . . bad names perhaps . . i am using Resource as an employee table. i dont want a resource to have a manager id as some resources have multiple managers (thus the initial 2 tables). – leora Dec 27 '09 at 23:45

score 0 · Answer 2 · answered Dec 27 '09 at 13:53

0

You have to create the foreign keys in the Manager table.

answered Dec 27 '09 at 13:53

Frederik Gheysels

56,135
11
101
154

I was going to ask the author to post their code, but I think you're right about what their problem is. – easeout Dec 27 '09 at 13:59

multiple constraint keys

2 Answers2