CODEWITHSUNDEEP
sqlsql-server
Lance Leroy
Lance Leroy

Reputation: 399

How to reference the composited PRIMARY KEY in table1 to table 2 as foreign key in SQL?

I having a problem with creating foreign key that pointing to a composite primary key in other table.

CREATE TABLE CityCountry(
    city_code VARCHAR(5) NOT NULL,
    city VARCHAR(100),
    country_code VARCHAR(5) NOT NULL,
    country VARCHAR(100),
    citycountry_id VARCHAR(105) PRIMARY KEY (city_code, country_code) 
)

CREATE TABLE FlightRoute(
    flightroute_id INT IDENTITY(1, 1) PRIMARY KEY,
    flight_departureCountryCity VARCHAR(105),
    flight_id INT FOREIGN KEY 
        REFERENCES Flight(flight_id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    flight_departureDateTime DATETIME,
    flight_arrivalDateTime DATETIME,
    aircraft_code VARCHAR(15) FOREIGN KEY 
        REFERENCES Aircraft(aircraft_code)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    flight_order SMALLINT
)

I tried the solution in stackoverflow but didnt work out

alter table flightroute add constraint FK_FlightRoute_CityCountry foreign key FlightRoute(flight_departureCountryCity) references CityCountry (city_code, country_code)

Upvotes: 0

Views: 57

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

In order to create a foreign key referenced columns shoud be PK or unique index. Since you already have a PK you can make your column a unique index:

create unique index UI_CityCountry
   on CityCountry(citycountry_id);

Now create FK:

alter table flightroute
add constraint FK_FlightRoute_CityCountry
foreign key (flight_departureCountryCity)
references CityCountry (citycountry_id)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Try defining your tables like this:

CREATE TABLE CityCountry(
    citycountry_id VARCHAR(105) NOT NULL PRIMARY KEY,
    city_code VARCHAR(5) NOT NULL,
    city VARCHAR(100),
    country_code VARCHAR(5) NOT NULL,
    country VARCHAR(100)
);

CREATE TABLE FlightRoute(
    flightroute_id INT IDENTITY(1, 1) PRIMARY KEY,
    flight_departureCountryCity VARCHAR(105) FOREIGN KEY
        REFERENCES CityCountry(citycountry_id),
    flight_id INT FOREIGN KEY 
        REFERENCES Flight(flight_id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    flight_departureDateTime DATETIME,
    flight_arrivalDateTime DATETIME,
    aircraft_code VARCHAR(15) FOREIGN KEY 
        REFERENCES Aircraft(aircraft_code)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    flight_order SMALLINT
);

That is, declare the primary key correctly in the first table and thenuse it in the second.

Personally, I prefer primary keys that are incremented numeric values, but I'm leaving your original types.

Upvotes: 0

Related Questions