CODEWITHSUNDEEP
sqlsql-server
Aviel Fedida
Aviel Fedida

Reputation: 4102

Sql, Create a foreign key to table with a primary key composed of multiple columns

I have the following table:

CREATE TABLE Appartment
(
    SizeSquareMeter INT,
    Type VARCHAR(30) NOT NULL,
    StreetName VARCHAR(30) NOT NULL,
    Number INT NOT NULL,
    Door INT  NOT NULL,

    CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
)

And I need to create the following table as well

CREATE TABLE Resident
(
    RID INT PRIMARY KEY NOT NULL,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    BirthDate DATE NOT NULL,
    StreetName VARCHAR(30) NOT NULL,
    Number INT NOT NULL,
    Door INT NOT NULL,

    CONSTRAINT Resident_Address 
        FOREIGN KEY (StreetName, Number, Door) REFERENCES Appartment(StreetName, Number, Door)
)

Now the above works but it duplicates the address and I don't want that, is there a way I can create a foreign key from Resident to Appartment without duplicating the address and without creating a new PRIMARY KEY for Appartment?

Note: whether it's important or not it basically should be valid Microsoft DDL.

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

A foreign key reference can be to any unique key(s) in the table. So you can do:

CREATE TABLE Appartment (
    AppartmentId int identity unique not null,
    SizeSquareMeter INT,
    Type VARCHAR(30) NOT NULL,
    StreetName VARCHAR(30) NOT NULL,
    Number INT NOT NULL,
    Door INT  NOT NULL,

    CONSTRAINT App_Address PRIMARY KEY(StreetName, Number, Door)
);

CREATE TABLE Resident (
    RID INT PRIMARY KEY NOT NULL,
    FirstName VARCHAR(30) NOT NULL,
    LastName VARCHAR(30) NOT NULL,
    BirthDate DATE NOT NULL,
    AppartmentId int not null,

    CONSTRAINT Resident_Address 
        FOREIGN KEY (AppartmentId) REFERENCES Appartment(AppartmentId)
);

That said, I strongly think you should change the data structure so the primary key for the apartment table is an identity column.

Upvotes: 1

Zeki Gumus
Zeki Gumus

Reputation: 1484

You can use only specific Id to do the foreign key. It is breaking the normalization rule(when you using same detail in both tables). Basicly you can create a UniqueId on the Appartment table. Remove StreetName, Number and Door from the Resident table which is already registered in Appartment table. Add AppartmentId to the Resident with the foreign key. Here is the updated create sample : 

CREATE TABLE Appartment(
    ApartmentId INT NOT NULL ,
    SizeSquareMeter int,
    Type varchar(30) NOT NULL,
    StreetName varchar(30) NOT NULL,
    Number int NOT NULL,
    Door int NOT NULL,
    CONSTRAINT PK_Appartment PRIMARY KEY(ApartmentId)
)


CREATE TABLE Resident(
    RID int PRIMARY KEY NOT NULL,
    AppartmentId INT NOT NULL,
    FirstName varchar(30) NOT NULL,
    LastName varchar(30) NOT NULL,
    BirthDate Date NOT NULL,
    CONSTRAINT FK_Resident_Address FOREIGN KEY (AppartmentId) REFERENCES Appartment
)

Upvotes: 1

Related Questions