How to restrict values in a column based on values from another column in PostgreSQL?

Question

I would like to prevent a mismatch between course_code and course_namewhen inserting values to the table below.

CREATE TABLE course (
    course_id    INT4 NOT NULL PRIMARY KEY,
    course_code  CHAR(4) NOT NULL,
    course_name  VARCHAR(30) NOT NULL
);

For both I created an enumeration (see below), now I want to link 'C101' to 'Computer Science' etc.

CREATE TYPE e_course_code AS ENUM (
    'C101',
    'B102',
    'E103',
    'V104',
    'A105',
    'E104'
);

CREATE TYPE e_course_name AS ENUM (
    'Computer Science',
    'Business Information Management',
    'Electronics',
    'Visual Programming',
    'Audio Technology',
    'Engineering'
);

Is it possible to link specified (enumerated) values for two (or even more) columns? Something that returns an error message when inserting a course_code and course_name that do not match?

Answer 1

A fast and reliable way to implement what you ask in the title is a FOREIGN KEY constraint with MATCH FULL:

CREATE TABLE course (
  course_code varchar(4) PRIMARY KEY
, course_name text NOT NULL
);

CREATE TABLE some_other_table (
  some_other_id serial PRIMARY KEY
, course_code   varchar(4)
, course_name   text
, -- more columns
, CONSTRAINT course_fk FOREIGN KEY (course_code, course_name)
                  REFERENCES course(course_code, course_name) MATCH FULL
);

See:

• MATCH FULL vs MATCH SIMPLE in foreign key constraints

However, some_other_table.course_name would be completely redundant, and the clean implementation would be the normalized form instead:

CREATE TABLE some_other_table (
  some_other_id serial PRIMARY KEY
, course_code   varchar(4)
, -- more columns?
, CONSTRAINT course_fk FOREIGN KEY (course_code) REFERENCES course(course_code)
);

Or you add course_id as PK to the course table and use that as FK column.

You can always add a VIEW to display course_name additionally.

Aside: Never use the type char or char(n). See:

• Any downsides of using data type "text" for storing strings?

Answer 2

The simplest way to solve this (as i see it) would make two separate tables- one with id and code, another with code and name. See this question - Difference between 3NF and BCNF in simple terms (must be able to explain to an 8-year old) - the example in the answer is similar to your problem.