thanks for the advice Stephen. I'll admit though I am somewhat loathe to
adding an artifical row in the other tables, but it may not be a bad way
to go. In the past, I've written triggers to do this kind of check, but
mysql doesn't yet support triggers.
what I ended up doing is carefully rethinking the schema. It turns out
we came up with a better design that does not require the table_name,
table_id linking mechanism. We just link into one table, which of course
presents no problems in creating a foreign key constraint.
jeff
Stephen Giese wrote:
>
> Jeff,
>
> 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
> >
--
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]
