Is it possible to use a composite foreign key as a piece of a table's composite primary key?
For instance, let's say I have two tables:
CREATE TABLE DB.dbo.Partners
(
CONSTRAINT pk_Partners_Id
PRIMARY KEY (Name, City, State, Country, PostalCode),
Name VARCHAR(100) NOT NULL,
Address1 VARCHAR(100),
Address2 VARCHAR(100),
Address3 VARCHAR(100),
City VARCHAR(150) NOT NULL,
State CHAR(2) NOT NULL,
Country CHAR(2) NOT NULL,
PostalCode VARCHAR(16) NOT NULL,
Phone VARCHAR(20),
Fax VARCHAR(20),
Email VARCHAR(256)
)
... and then in a second table, I would like to reference the foreign key in the second table's primary key:
CREATE TABLE DB.dbo.PartnerContacts
(
CONSTRAINT pk_PartnerContacts_Id
PRIMARY KEY (fk_PartnerContacts_PartnerId, FirstName, LastName, PhoneNumber, Email),
CONSTRAINT fk_PartnerContacts_PartnerId
FOREIGN KEY REFERENCES Partners(Name, City, State, Country, PostalCode),
FirstName VARCHAR(75) NOT NULL,
MiddleName VARCHAR(75),
LastName VARCHAR(75) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
MobileNumber VARCHAR(20),
FaxNumber VARCHAR(20),
Email VARCHAR(256) NOT NULL,
MailTo VARCHAR(100),
Address1 VARCHAR(100),
Address2 VARCHAR(100),
Address3 VARCHAR(100),
City VARCHAR(150),
State CHAR(2),
Country CHAR(2),
PostalCode VARCHAR(16)
)
Is there any way that I can do that? Yes, it might be easier to just simply use IDENTITY columns in these tables but if I can define an actual relationship without an IDENTITY I would like to do that.
EDIT:
I wanted to provide the final, working SQL. Thanks to everyone who answered!
CREATE TABLE DB.dbo.Partners
(
CONSTRAINT pk_Partners_Id
PRIMARY KEY (Name, City, State, Country, PostalCode),
Id INT NOT NULL UNIQUE IDENTITY(1, 1),
Name VARCHAR(100) NOT NULL,
Address1 VARCHAR(100),
Address2 VARCHAR(100),
Address3 VARCHAR(100),
City VARCHAR(150) NOT NULL,
State CHAR(2) NOT NULL,
Country CHAR(2) NOT NULL,
PostalCode VARCHAR(16) NOT NULL,
Phone VARCHAR(20),
Fax VARCHAR(20),
Email VARCHAR(256)
)
CREATE TABLE DB.dbo.PartnerContacts
(
CONSTRAINT pk_PartnerContacts_Id
PRIMARY KEY
(PartnerId, FirstName, LastName, PhoneNumber, Email),
PartnerId INT NOT NULL CONSTRAINT fk_PartnerContacts_PartnerId FOREIGN KEY REFERENCES Partners(Id),
FirstName VARCHAR(75) NOT NULL,
MiddleName VARCHAR(75),
LastName VARCHAR(75) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
MobileNumber VARCHAR(20),
FaxNumber VARCHAR(20),
Email VARCHAR(256) NOT NULL,
MailTo VARCHAR(100),
Address1 VARCHAR(100),
Address2 VARCHAR(100),
Address3 VARCHAR(100),
City VARCHAR(150),
State CHAR(2),
Country CHAR(2),
PostalCode VARCHAR(16)
)
You probably need to specify the columns that are supposed to match.
CONSTRAINT fk_PartnerContacts_PartnerId
FOREIGN KEY (columns that correspond to referenced columns)
REFERENCES Partners (Name, City, State, Country, PostalCode),
So you need to provide the five column names whose values are supposed to match the values of {Name, City, State, Country, PostalCode} in the table "Partners". i'm pretty sure youcan't do that with your current structure. You won't be able to match "Name". I think you're looking for something along these lines.
CREATE TABLE DB.dbo.PartnerContacts (
-- Start with columns that identify "Partner".
partner_name VARCHAR(100) NOT NULL,
partner_city VARCHAR(150) NOT NULL,
partner_state CHAR(2) NOT NULL,
partner_country CHAR(2) NOT NULL,
partner_postcode VARCHAR(16) NOT NULL,
CONSTRAINT fk_PartnerContacts_PartnerId
FOREIGN KEY (partner_name, partner_city, partner_state, partner_country, partner_postcode)
REFERENCES Partners (Name, City, State, Country, PostalCode),
FirstName VARCHAR(75) NOT NULL,
MiddleName VARCHAR(75),
LastName VARCHAR(75) NOT NULL,
PhoneNumber VARCHAR(20) NOT NULL,
MobileNumber VARCHAR(20),
FaxNumber VARCHAR(20),
Email VARCHAR(256) NOT NULL,
MailTo VARCHAR(100),
Address1 VARCHAR(100),
Address2 VARCHAR(100),
Address3 VARCHAR(100),
City VARCHAR(150),
State CHAR(2),
Country CHAR(2),
PostalCode VARCHAR(16),
CONSTRAINT pk_PartnerContacts_Id
PRIMARY KEY (partner_name, partner_city, partner_state, partner_country, partner_postcode,
FirstName, LastName, PhoneNumber, Email)
);