How to enforce entity existence across N-tables - postgres

Question

Let's say we decided to split user table in two, one will have data related to authentication, another basic user description:

   user_table
user_id | name
1       | Max
2       | Alex
3       | Should not be possible

   auth_table
user_id | email   | password
1       | [email protected] | 123
2       | [email protected] | 321
4       | [email protected] | Should not be possible

Is it possible to enforce existence of the same user_id? Basically both tables should have exactly same number of rows and each row should have a match in both tables.

Answer 1

You can enforce your design at all times with PRIMARY KEY and mutual FOREIGN KEY constraints.

CREATE TABLE user_table (
  user_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, name text
);

CREATE TABLE auth_table (
  user_id int PRIMARY KEY
, email text
, password text
, CONSTRAINT auth_table_user_id_fk FOREIGN KEY (user_id)
  REFERENCES user_table ON DELETE CASCADE
);

-- finally add 2nd FK:
ALTER TABLE user_table
  ADD CONSTRAINT user_table_user_id_fk FOREIGN KEY (user_id)
  REFERENCES auth_table ON DELETE CASCADE;

The PK enforces UNIQUE NOT NULL, the FK enforces referential integrity. All done. I made user_table.user_id an IDENTITY column, and reuse the generated ID in the other table. But that's optional. See:

• Auto increment table column

Manipulating rows becomes rather restricted. Inserting rows might seem like a chicken-egg problem, but you just have to insert in both tables in the same statement (using a CTE):

WITH ins_user AS (
   INSERT INTO user_table (name)
   VALUES ('foo')
   RETURNING user_id
   )
INSERT INTO auth_table (user_id, email, password)
SELECT user_id, '[email protected]', 'secret'
FROM   ins_user
RETURNING user_id;

fiddle

See (with links to more):

• Chicken-and-egg problem in with foreign keys

I added ON DELETE CASCADE as optional convenience feature, so deleting from one table deletes from both. You might instead delete from both tables in the same statement.

One might add ON UPDATE CASCADE as well, but I would not allow updating those IDs to begin with.

Answer 2

Yes its possible

I would put unique indexes in user_id, so it wont repeat.

Then use a trigger on the main table, before insert, to create a record in the 2nd table