It has been a long time since I have setup SQL Server databases, but I thought I could do what I wanted to do, which is have multiple Foreign Keys, but one of those keys referencing a CONSTRAINT primary key.
Here is what I want to do, but am getting an error, (error message follows the SQL statements)
// Create tables
Create table Regions (
RegionID int NOT NULL PRIMARY KEY,
RegionName varchar (100) NOT NULL
Create table StoreManagers (
StoreMGR int NOT NULL,
EmpID NOT NULL IDENTITY (1,1),
LastName varchar(255) NOT NULL,
CONSTRAINT PK_StoreManager PRIMARY KEY (StoreMGR, EmpID)
Create table Stores (
StoreID int NOT NULL PRIMARY KEY,
StoreName varchar(255) NOT NULL,
FOREIGN KEY (RegionID) REFERENCES Regions(RegionID),
FOREIGN KEY (StoreMGR) REFERENCES StoreManagers(PK_StoreManager)
The error I am getting is on the very last line. It is saying I am referencing an "invalid column in referenced table StoreManagers".
Is there a way to do this? Is so, what is the correct syntax?
Thank you in advance,