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.