Issue on using composite primary key as a foreign key

Question

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

that either are not related to Airlines,
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?

Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. PS Read the manual re the functionality you are using. — philipxy, Oct 07 '20 at 06:32

score 2 · Accepted Answer · answered Oct 07 '20 at 04:29

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
);

score 0 · Answer 2 · answered Oct 07 '20 at 12:31

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.

Issue on using composite primary key as a foreign key

2 Answers2