1

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.

2
  • What if you decide to add another user_id in user_table, should it be inserted on the auth_table? What if you delete user_id 2 in user_table? Jul 23 at 19:38
  • @ErgestBasha you should insert into two and delete from two tables at the same time.
    – ZiiMakc
    Jul 24 at 9:54

2 Answers 2

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:

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):

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.

1
  • Thanks, is there any downside of this approach except requirement of insert/delete in one transaction(with deferred fk)? It seems some people frown upon this as bad design, but I don't see possible issues with such approach.
    – ZiiMakc
    Jul 25 at 20:12
0

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

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.