Adding a foreign key to a table,from two tables in mySQL

Question

I have two tables called lawyer & client.And they are the users of my site .so i have another table called users to store their username,password.But i have to add a foreign key which references clientID(from client table),lawyerId(from lawyer table) to User table. how can i do this?

possible duplicate of [Possible to do a MySQL foreign key to one of two possible tables?](http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables) — Bill Karwin, Jan 08 '12 at 02:48

score 3 · Accepted Answer · answered Jan 08 '12 at 02:35

This sounds backwards; as lawyers & clients are users, their IDs should be referencing the ID in the users table, not the other way around (not every user is a lawyer and client; in fact, some could presumably be neither.) Or am I misreading what you wrote?

score 1 · Answer 2 · answered Jan 08 '12 at 02:48

You cannot have multiple foreign key references for a single primary key column. Here is a simple example of how to re-design your schema to facilitate your requirements.

mysql> CREATE TABLE Users(
    ->    userID VARCHAR(20) NOT NULL PRIMARY KEY,
    ->    password VARCHAR(20) NOT NULL
    -> )
    -> ENGINE=INNODB;

mysql> CREATE TABLE Client(
    ->    userID VARCHAR(20) NOT NULL PRIMARY KEY,
    ->    FOREIGN KEY (userID) REFERENCES Users (userID)
    -> )
    -> ENGINE=INNODB;

mysql> CREATE TABLE Lawyer(
    ->    userID VARCHAR(20) NOT NULL PRIMARY KEY,
    ->    FOREIGN KEY (userID) REFERENCES Users (userID)
    -> )
    -> ENGINE=INNODB;

Why storing passwords in three places? – Jonathan Leffler Jan 08 '12 at 02:49 — Jonathan Leffler, Jan 08 '12 at 02:49

Adding a foreign key to a table,from two tables in mySQL

2 Answers2