We faced a similar challenge in an application: Each child record must have a parent in one of two tables, TabA or TabB, but not both. We "solved" it by adding a foreign-key field for each possible parent in the child table. Each column can have the FK constraint. We were using Sybase, but I translate the DDL to MySQL below.
create table Example (
id int not null auto_increment primary key,
tableA_id int not null,
tableB_id int not null
) type = InnoDB;
alter table Example add index (tableA_id);
alter table Example add index (tableB_id);
alter table Example add constraint foreign key (tableA_id) references TabA(id);
alter table Example add constraint foreign key (tableB_id) references TabB(id);However, you will notice that each child record now must have a parent record in BOTH parent tables. We used our front end to enforce a rule that the one of the two foreign key fields is always -1 (or some other default value). Then we insert a record into each parent with a key value that matches our default (-1).
This method is not as easily extensible as your model, but perhaps that's OK. In SQL to join the parent and child you must decide which parent to join based on which FK column has the non-default value.
You might be able to come up with a DB rule to ensure that exactly one of the FK values is non-default.
Stephe
At 09:26 AM 4/2/2003 -0700, Jeff Mathis wrote:
Thanks, but I think the lik you provided won't help. I know how to create pk/fk contraints, and do in our schema, when the foreign key is completely specified. for example, if my original table was instead:
create table Example ( id int not null auto_increment primary key, fk_id int not null ) type = InnoDB;
then I create an index in fk_id, and issue the alter table statement: alter table Example add constraint foreign key (fk_id) references Fk(id);
for an InnoDB table called Fk.
What I need to do is somehow put an "if" statement in there. If table_name = 'TabA', then verify that TabA.id exists. If table_name = 'TabB', then verify that TabB.id exists. TabA and TabB, for the present purposes, could simply be
create table TabA { id int not null auto_increment primary key ) type = InnoDB;
create table TabB { id int not null auto_increment primary key ) type = InnoDB;
Its as though I could do the following:
create table Example ( id int not null auto_increment primary key, table_name enum('TabA','TabB') not null, table_id int not null ) type = InnoDB; alter table Example add index (table_id); alter table Example add constraint foreign key (table_id) references (if table_name = 'TabA' then TabA(id) else TabB(id);
but I don't think this works.
jeff
Stefan Hinz wrote: > > Jeff, > > > I'm wondering if its somehow possible to create a pk/fk constraint for > > the table below > > > create table Example ( > > id int not null auto_increment primary key, > > table_name enum('TabA','TabB') not null, > > table_id int not null > > ) type = InnoDB; > > > if table_name is 'TabA', then I want to make sure the row exists in > > TabA. Likewise if table_name is 'TabB' > > You can find the syntax for MySQL / InnoDB and a good example here: > > http://www.mysql.com/doc/en/SEC463.html > > To avoid trouble, consider this sentence from that page: > > Both tables have to be InnoDB type and there must be an index where > the foreign key and the referenced key are listed as the FIRST > columns. InnoDB does not auto-create indexes on foreign keys or > referenced keys: you have to create them explicitly. > > The example on that page, however, shows exactly how you'd do that. > > Regards, > -- > Stefan Hinz <[EMAIL PROTECTED]> > iConnect GmbH <http://iConnect.de> > Heesestr. 6, 12169 Berlin (Germany) > Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 > > [filter fodder: sql, mysql, query] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
