How to enforce a unique constraint across multiple tables?

user_12383362 0
Viewed 767

I have created the following two tables to map out students and teachers :

CREATE TABLE students(
    student_id SERIAL PRIMARY KEY,
    first_name NOT NULL VARCHAR(50),
    last_name NOT NULL VARCHAR(50),
    phone VARCHAR(15) UNIQUE NOT NULL CHECK (phone NOT LIKE '%[^0-9]%'),
    email VARCHAR(30) UNIQUE NOT NULL CHECK (email NOT LIKE '%@%'),
    graduationYear SMALLINT CHECK (graduationYear > 1900)
);

CREATE TABLE teachers(
    teacher_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    departament VARCHAR(40) NOT NULL,
    email VARCHAR(30) UNIQUE NOT NULL CHECK (email NOT LIKE '%@%'),
    phone VARCHAR(15) UNIQUE NOT NULL CHECK (phone NOT LIKE '%[^0-9]%')
);

As you can see, both tables have a column for phone and email. I want these two to be unique to each individual.

How can I introduce a constraint that will check if the phone number/email introduced, for example, in the students table doesn't already exist in the teachers table? Is there any kind of keyword that works like UNIQUE but on multiple tables or should I take another approach?

Edit: as @a_horse_with_no_name pointed out, LIKE doesn't support regular expressions. I should have used SIMILAR TO.

unique-constraint constraints database-design postgresql sql
1 Answers

I would create a single table person that contains all attributes that are common to both including a type column that identifies teachers and students. Then you can create unique constraints (or indexes) on the phone and email columns.

To store the "type specific" attributes (graduation year, department) you can either have nullable columns in the person table and only put in values depending on the type. If you do not expect to have more "type specific" attributes apart from those two, this is probably the easiest solution

If you expect more "type specific" attributes, additional tables (student and teacher) with containing those can also be used. This is the traditional way of modelling inheritance in a relational database. As Postgres supports table inheritance, you could also create the teacher and student tables to inherit from the person table.

user_3303150
Related