CODEWITHSUNDEEP
sqlpostgresqlforeign-keyssql-insert
jginso7
jginso7

Reputation: 105

Issue on using composite primary key as a foreign key

I have made a composite key as a primary key for table Airlines. And I want to use that primary key as a foreign key for table Flights. With that what I want is to have Flights that

  1. that either are not related to Airlines,
  2. or if they are, they need to have both columns of the composite key.

Below are tables:

CREATE TABLE Airlines (
  name char(32),
  country char(32),
  PRIMARY KEY (name, country)
);

CREATE TABLE Flights_operate (
  num integer PRIMARY KEY,
  dept timestamp,
  arr timestamp,
  name_Airlines char(32),
  country_Airlines char(32),
  CONSTRAINT FK
  FOREIGN KEY (name_Airlines, country_Airlines) REFERENCES Airlines (name, country)
);

I tried using CONSTRAINT but it doesn't seem to do the trick.

I can still insert rows with a name_Airlines and without a country_Airlines like so: image

What should I do?

Upvotes: 0

Views: 480

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can also do this using a check constraint:

CREATE TABLE Flights_operate (
  num integer PRIMARY KEY,
  dept timestamp,
  arr timestamp,
  name_Airlines char(32),
  country_Airlines char(32),
  CONSTRAINT FK
  FOREIGN KEY (name_Airlines, country_Airlines) REFERENCES Airlines (name, country),
  CHECK (name_Airlines IS NOT NULL AND country_Airlines IS NOT NULL OR
         name_Airlines IS NULL AND country_Airlines IS NULL
        )
);

Some additional notes.

First, do not use char as a data type for names. It pads the strings with spaces, which is generally undesirable.

Second, consider having an identity/serial primary key for all your tables. This is more efficient than strings -- consider 4 bytes for an integer versus 64 bytes for your composite key.

Upvotes: 0

Blue Star
Blue Star

Reputation: 1952

To disallow just one of the columns in the foreign key from being null, you can use MATCH FULL (see docs).

CREATE TABLE Flights_operate (
  num integer PRIMARY KEY,
  dept timestamp,
  arr timestamp,
  name_Airlines char(32),
  country_Airlines char(32),
  CONSTRAINT FK
  FOREIGN KEY (name_Airlines, country_Airlines) 
  REFERENCES Airlines (name, country) MATCH FULL
);

Upvotes: 2

Related Questions