Adding a database-wide UNIQUE constraint in PostgreSQL

Question

Would like to find out if there is a way to add a UNIQUE constraint across all tables in a PostgreSQL database for a common specific column?

For example, consider a PostgreSQL database named, "School". It has 2 tables named, "teachers" and "students" respectively.

"teachers" table has, teacher_id, first_name, last_name, homeroom_number, department, email, and phone columns.

"students" has, student_id, first_name, last_name, homeroom_number, phone, email, graduation_year columns.

I want to ensure that when data is inserted into the respective tables, the email and phone columns in both "teachers" and "students" are unique.

Typically when the UNIQUE constraint is added, it is added as only either a table constraint (table-wide) or column constraint (applies only to the specific column in a specific table). So if the UNIQUE constraint is added to the email and phone columns in the "teachers" table, the values are only unique within the "teachers" table. If the same thing is done to the "students" table, the emails and phone numbers will also be unique only within the "students" table. How can I add a UNIQUE constraint that applies to phone and email columns across both "teachers" and "students" tables such that each phone number and email address is truly unique to each individual regardless of whether that individual is a teacher or a student? For example, if a teacher's phone number is inserted as 123-456-789 in the "teachers" table, inserting the same phone number, 123-456-789 for a student in the "students" table will elicit an error.

You need to move the phone numbers into a separate table and create a foreign key from the "student" and "teacher" table to the "phone_number" table — user330315
– user330315, Commented Jul 20, 2020 at 7:16

Jason Chia Kim Leng · Accepted Answer · 2020-07-20 09:53:34Z

Thanks to some Googling and contributions of ideas from those who responded to this post, I could do the following:

Create a separate table that collates all phone numbers added into "teachers" and/or "students" tables and implement the UNIQUE constraint on the phone column in this table:

CREATE TABLE phone_numbers (phone_id SERIAL PRIMARY KEY, phone VARCHAR(250) UNIQUE NOT NULL);

Create a FUNCTION that inserts the phone number into the newly created "phone_numbers" table every time a phone number has been inserted into "teachers" and/or "students" table(s):

CREATE OR REPLACE FUNCTION phone_insert_trigger_func() RETURNS trigger AS $$ BEGIN INSERT INTO phone_numbers (phone) VALUES (NEW.phone); RETURN NEW; END; $$ LANGUAGE 'plpgsql';

Finally, create a TRIGGER that automatically executes the above-mentioned FUNCTION every time a phone number has been inserted into the "teachers" and/or "students" table(s):

CREATE TRIGGER teachers_insert_trigger AFTER INSERT ON teachers FOR EACH ROW EXECUTE PROCEDURE phone_insert_trigger_func();

CREATE TRIGGER students_insert_trigger AFTER INSERT ON students FOR EACH ROW EXECUTE PROCEDURE phone_insert_trigger_func();

When the above is done correctly, inserting a duplicate phone number (eg. one that is not present in the table inserted (such as "students") but is present in the other table (eg. "teachers")) will elicit an error feedback as shown below and prevent duplicate phone numbers (whether that belonging to students' or teachers') from being inserted into the "School" database:

ERROR: duplicate key value violates unique constraint "phone_numbers_phone_key" DETAIL: Key (phone)=(777-555-6519) already exists. CONTEXT: SQL statement "INSERT INTO phone_numbers (phone) VALUES (NEW.phone)" PL/pgSQL function phone_insert_trigger_func() line 3 at SQL statement SQL state: 23505

Collectives™ on Stack Overflow

Adding a database-wide UNIQUE constraint in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related