I am beginner trying to design a database using SQL Server 2005. I have three tables:

AdminMember
AMem_AdminId (uniqueidentifier, PK)
AMem_UserName
AMem_Password

CompAcct
Comp_CompId (uniqueidentifier, PK)
Comp_Website
Comp_Name

AdminCompActt
AdCA_AdminId
AdCA_CompID

The AdminMember table holds all Admins. The CompAcct table holds all companies.

I'm trying to use the AdminCompAcct table to link the above referenced tables. This way I can keep track of each Admin, each Company, and which Company each Admin belongs to.

The issue I'm running into is how to link them according to best practices. Because both Amem_AdminID and Comp_CompID are uniqueidentifiers and PK, it seems I cannot create both AdCA_AdminID and AdCA_CompId as a FK. The first one works fine but the second one generates an error. (The columns in table 'AdminCompAcct' do not match an existing primary key or UNIQUE constraint).

I understand the error message but what is the best way resolve or design this? Thanks for your help.

Brian

There is no reason you could not create that foreign key to CompAcct.Comp_CompId

There is something you are missing. Post the DDL of the tables for us.

Got it figured out on another forum. I had to create a composite key for (AdCA_AdminId, AdCA_CompID). I knew nothing about composite keys. Definitely a newbie to all of this.