Question 21492 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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 );
    Login or Signup to reply.
  2. 0

    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));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search