How to enforce that a new entry can only be added to a table if one of its column values exists in a-CodePudding

I have two tables.

A: id, email, ... (email can have duplicates)
B: id, email, ... (email is unique)

I want to only allow new entries in B where the email already exists in A.

Is there some way to do this via FKs or would I need some kind of trigger?

CodePudding user response：

You can add foreign key constraint on the Table B on this Email column, so that it will maintain the integrity.

ALTER TABLE B ADD CONSTRAINT fk_email
FOREIGN KEY ( EMAIL ) REFERENCES A ( EMAIL );

CodePudding user response：

The unique emails should be stored in a separate table X:

CREATE TABLE X (email VARCHAR(255) PRIMARY KEY);

or, better:

CREATE TABLE X (id INTEGER PRIMARY KEY, email VARCHAR(255) UNIQUE);

Then, both tables A and B should have a column email or email_id referencing either the email or the id of the table X:

CREATE TABLE A (id INTEGER PRIMARY KEY, email VARCHAR(255) REFERENCES X(email));
CREATE TABLE B (id INTEGER PRIMARY KEY, email VARCHAR(255) REFERENCES X(email));

or:

CREATE TABLE A (id INTEGER PRIMARY KEY, email_id INTEGER REFERENCES X(id));
CREATE TABLE B (id INTEGER PRIMARY KEY, email_id INTEGER REFERENCES X(id));