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

Viewed 785

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?

2 Answers
Related