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
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)
INSERT INTO auth_table (user_id, email, password)
SELECT user_id, '[email protected]', 'secret'
See (with links to more):
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.