Composite Primay Key and foreign Key

Hi all,

I get a big problem when I doing my assignment.
It told me to:

- Create a table with one composite primary key
- Create another table with one primary key and one foreign key refer to last table

Here is my SQL statement
CREATE TABLE table1
(
FirstName varchar(10)
LastName varchar(10)
PRIMARY KEY (FirstName, LastName)
)

CREATE TABLE table2
(
PlanID int,
PRIMARY KEY (PlanID)
FirstName varchar(10) FOREIGN KEY REFERENCES table1(FirstName)
LastName varchar(10) FOREIGN KEY REFERENCES table1(LastName)
)

It failed. I have searched for google long time but no result for that.
Hope you can save me. Thanks!!!Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

Another solution could be:

CREATE TABLE table1
(
keyField int not null
FirstName varchar(10)
LastName varchar(10)
PRIMARY KEY (keyField)
)

CREATE TABLE table2
(
PlanID int not null,
PRIMARY KEY (PlanID)
t1_keyField int FOREIGN KEY REFERENCES table1(keyField)
)

If you want the combination of the first and last name to be unique you can always create a unique constraint on the two columns.|||[QUOTE][SIZE=1]Originally posted by jora
Your problem is probably caused because you reference a column to a partial key. What exact error do you get?

The error said
"There are no primary or candidate keys in the referenced table 'table1'
that match the referencing column list in the foreign key 'FK_table2_FirstName'

Thanks for your suggestion but table1 should have a composite primary key instead of a single primary key.|||Oh! I get it.

Here is the code:
CREATE TABLE table1
(
FirstName varchar(10),
LastName varchar(10),
PRIMARY KEY (FirstName, LastName),
)

CREATE TABLE table2
(
PlanID int,
FirstName varchar(10),
LastName varchar(10),
PRIMARY KEY (PlanID),
FOREIGN KEY(FirstName,LastName) REFERENCES table1 FirstName,LastName),
)