SQL: Foreign key references a composite primary key

Question

I'm new to SQL and there are a lot of things going on that I still don't seem to quite understand. I have the following table

CREATE TABLE Person 
(
    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,

    PRIMARY KEY (First_Name, Name, Address)
);

I know want to create another table that has the primary key from the table Person as foreign key and also as primary key:

CREATE TABLE Purchase 
(
    No_Installments int,
    Rate int,
    Person varchar(50) NOT NULL PRIMARY KEY,

    CONSTRAINT PFK 
        FOREIGN KEY (Person) REFERENCES Person (First_Name, Name, Address)
);

For some reason this doesn't work and I get an error every time. I've already looked up the other threads here on stackoverflow, but they don't really seem to help me. What am I doing wrong?

Answer 1

If you have a compound PK made up from three columns, then any child table that wants to establish a foreign key relationship must ALSO have all those 3 columns and use all 3 columns to establish the FK relationship.

FK-PK relationship is an all or nothing proposal - you cannot reference only parts of a primary key - either you reference all columns - or you don't reference.

CREATE TABLE Purchase 
(
    No_Installments int,
    Rate int,

    Person varchar(50) NOT NULL PRIMARY KEY,

    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,

    CONSTRAINT PFK 
        FOREIGN KEY (First_Name, Name, Address) 
        REFERENCES Person (First_Name, Name, Address)
);

Answer 2

Have an integer primary key, using identity, auto_increment, serial or whatever for your database:

CREATE TABLE Person (
    PersonId int identity PRIMARY KEY
    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,
    CONSTRAINT unq_person_3 UNIQUE (First_Name, Name, Address)
);

Then use the identity column for the reference:

CREATE TABLE Purchase (
    PurchaseId int identity PRIMARY KEY,
    No_Installments int,
    Rate int,
    PersonId int,
    CONSTRAINT PFK 
        FOREIGN KEY (PersonId) REFERENCES Person (PersonId)
);

Notes:

• You really don't want to have to deal with a composite primary key. Have you thought about what the joins will look like?
• You don't want a primary key where the values are subject to change. What happens when someone changes his/her name? When someone moves?
• Person should not be the primary key in Purchases. Are you only allowing someone to make one purchase?
• As noted initially, how you generate such a column varies by database; identity happens to be the way that SQL Server does this.