one to one relationship between three tables

Question

Hi I want to achieve one ot one relation ship between three tables such that rtTicketId (1) can only be used vmID(1) and cannot be used by dbId(1).

create table IF NOT EXISTS RequestTable (
  rtTicketId bigserial unique not null AUTO_INCREMENT, 
  primary key (rtTicketId));

create table IF NOT EXISTS Vm (
  vmId bigserial unique not null AUTO_INCREMENT, 
  primary key (vmId),
  foreign key (rtTicketId) references RequestTable(rtTicketId));

create table IF NOT EXISTS Db (
  dbId bigserial unique not null AUTO_INCREMENT, 
  rtTicketId bigserial unique not null,
  primary key (dbId),
  foreign key (rtTicketId) references RequestTable(rtTicketId));

I have Done this but this create one to one relation between only two separately. I want rt id to be unique . right now both vm and db table are able to use same rtTicketId to connect to request table. I dont want that

To be totally clear - a request can be either for a DB or a VM, but not both? — Neville Kuyt, Oct 21 '16 at 11:24
http://stackoverflow.com/questions/1493229/multiple-yet-mutually-exclusive-foreign-keys-is-this-the-way-to-go - does this answer your question? — Neville Kuyt, Oct 21 '16 at 13:19

score 0 · Answer 1 · edited May 23 '17 at 12:23

0

Maybe polymorphic association will help; e.g. Instead of VM as DB has a column to store rtTicketId, RT will have 2 columns, fkId and fkType for example, fkId stores the primary key in VM or DB and fkType defines where to look at when querying (DB or VM).

See also here: Possible to do a MySQL foreign key to one of two possible tables?

Another option I can think about is doing this programmatically such as:

SELECT * FROM RequestTable WHERE rtTicketId NOT IN (SELECT DISTINCT rtTicketId FROM your_other_table_vm_or_db)

edited May 23 '17 at 12:23

Community

1
1

answered Oct 21 '16 at 11:17

hakany

7,134
2
19
22

doing it programatically is probbaly not the efficient way as later on database in going to grow to have other types like desktop and some other tables (other than vm and db) – Pritbh Oct 21 '16 at 12:50

one to one relationship between three tables

1 Answers1