FOREIGN KEY referencing of a CONSTRAINT PRIMARY KEY error

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,
      FirstName varchar(255),
      EmailAddress varchar(255),
      CONSTRAINT PK_StoreManager PRIMARY KEY (StoreMGR, EmpID)
);

Create table Stores (
       StoreID int NOT NULL PRIMARY KEY,
       StoreName varchar(255) NOT NULL,
       RegionID int,
       StoreMGR int,
       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,
jgrammer42Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
If the PK is defined with two columns, the FK has to have two columns, too. So no, this doesn't work out.

Technically your Stores table would also need an EmpID, but if I interpret this whole concept correctly, the outset of store managers as subset of employees should even not have the EmpID as IDENTITY(1,1) column, EmpIDs should be a foreign key referencing Employees(EmpID) and then StoreMGR would be NOT NULL IDENTITY (1,1), you wouldn't need a compound PK in StoreManagers and let Stores reference to StoreManagers via single field only.

Bye, Olaf.
0
jgrammer42Author Commented:
Ah, I think I have it.  So, actually, I need to write the Stores FOREIGN KEY definition like this?

Create table Stores (
       StoreID int NOT NULL PRIMARY KEY,
       StoreName varchar(255) NOT NULL,
       RegionID int,
       StoreMGR int,
       StoreMGRID int,
       FOREIGN KEY (RegionID) REFERENCES Regions(RegionID),
       FOREIGN KEY (StoreMGR, StoreMGRID) REFERENCES StoreManagers(PK_StoreManager)
);

Thank you,

BTW, the EmpID is for future use as this is a "proof of concept" development.
0
Olaf DoschkeSoftware DeveloperCommented:
No, you never reference a constraint. You can have a compound key, but this needs a tuple of columns on both tables, as in

ALTER TABLE [dbo].[Table2]  WITH CHECK ADD  CONSTRAINT [FK_Table2] 
FOREIGN KEY([ID1], [ID2]) REFERENCES [dbo].[Table1] ([ID1], [ID2])

Open in new window


Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jgrammer42Author Commented:
Got it.  Thank you!
0
jgrammer42Author Commented:
Thank you very much.  It has just been a while since I have done this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.