How to "inherit" UNIQUE property?

Question

So, my problem is that I have a table, called "USER", in my database, and another table "STUDENT" inheriting from it, plus some more specific fields.

Creating "USER" looks like:

CREATE TABLE IF NOT EXISTS "USER"
(
  id       BIGSERIAL PRIMARY KEY,
  login    CHARACTER VARYING(64) UNIQUE NOT NULL,
  password CHARACTER VARYING(32)        NOT NULL DEFAULT 'pass',
  name     TEXT                         NOT NULL,
  email    TEXT,
  role     CHARACTER VARYING(32)        NOT NULL
);

Creating "STUDENT" looks, for example, like:

CREATE TABLE IF NOT EXISTS "STUDENT"
(
  state TEXT,
  hire_date DATE,
  english_level TEXT,
  CONSTRAINT student_pkey PRIMARY KEY (id),
  CONSTRAINT student_login_key UNIQUE(login)
) INHERITS ("USER");
ALTER TABLE "STUDENT" ALTER COLUMN role SET DEFAULT 'stud';

The problem is that column login, which should be unique, is actually unique in both of these tables separately, but not unique "together". I mean, if I add a row into table "USER" with login 'temp', and then add a row into "STUDENT" with the same login, I'll get two rows in my table "USER" with the same logins "temp" and no error.

On the other hand, column id, which is autoincrement, works great: in has a single 'sequence' for both of the tables.

What should I do to save the unique property in 'union' of tables "USER" & "STUDENT"?

Answer 1

You can't, unfortunately. The documentation even mentions this as a significant limitation with table inheritance.

You might want to just have two independent tables, and give student a unique foreign key to user.