1

There are many similar question but this is bit different.

I have one table which has one foreign key that will reference to two tables. I used below query for testing.

CREATE TABLE users
(
    id int NOT NULL,
    username varchar(255) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE admins
(
    id int NOT NULL,
    username varchar(255) NOT NULL,
    PRIMARY KEY (id)
);


CREATE TABLE info
(
    id int NOT NULL,
    fullname int NOT NULL,
    user_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (user_id) REFERENCES admins(id)
);

enter image description here

Above queries works fine. but when I try to draw model in mysql workbench it create on new field in info table that I don't want. I want user_id should work and show relation as foreign key for users and admins table.

One more thing, am I trying to do that is not well standard? Also suggests a correct way to do it.

Table names used only for example purpose. There is no logic here. I am trying to find solution for one key as foreign key for multiple table and faced issue with mysql work bench.

  • The reason you are having trouble is because this is not a practicle thing to do in database terms. A foreign key constraint always references exactly one parent table. See this post for more detail: http://stackoverflow.com/questions/3383300/foreign-key-to-one-of-many-tables – ModulusJoe Nov 07 '13 at 12:48

1 Answers1

1

Try this:

  1. Save your DDL in a file.
  2. Create new model in MySQL Workbench
  3. File > Import > Reverse Engineer MySQL Create Script
  4. Browse to file created in step 1. Ensure that 'Place imported objects on diagram' is selected.
  5. Click 'Execute'

From a data modelling point of view you might be better off specifying a user as an admin by including an extra column on the users table. Hence:

CREATE TABLE users
(
    id int NOT NULL,
    username varchar(255) NOT NULL,
    isAdmin boolean not null default false,
    PRIMARY KEY (id)
);

CREATE TABLE info
(
    id int NOT NULL,
    fullname int NOT NULL,
    user_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
Tom Mac
  • 9,693
  • 3
  • 25
  • 35
  • yes i tried this and it works in this way but if i remove relation from work bench and recreate then it created new field name in info tale that i don't want. You missed admin table here. problem is when three tables are there. –  Nov 07 '13 at 12:44
  • I believe he missed the Admin table intentionally. Your issue is not with mysql workbench, it is with what you are trying to achieve with mysql itself. – ModulusJoe Nov 07 '13 at 12:50
  • @AcyclicTau - correct. The idea is to remove the `admins` table and replace it with the `isAdmin` column on the `users` table. – Tom Mac Nov 07 '13 at 12:56
  • @Tom Mac - Have upvoted your answer and deleted mine for clarity. – ModulusJoe Nov 07 '13 at 12:57
  • Sql worked file and imported same to workbench it created relation with the same field. but when i removed relation and recreated relation it created new fiel in work bench. so issue is with mysql work bench. –  Nov 07 '13 at 12:58
  • Please can you detail exactly what you are trying to achieve with your underlying data model? Do you want to have a constraint so that for every info row a User or Admin must exist? – ModulusJoe Nov 07 '13 at 13:12