List:General Discussion« Previous MessageNext Message »
From:Jeff Mathis Date:April 2 2003 7:32pm
Subject:Re: primary key/foreign key constraints with InnoDB
View as plain text  
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			jmathis@stripped
525 Camino de los Marquez, Ste 6	http://www.predict.com
Santa Fe, NM 87505
Thread
primary key/foreign key constraints with InnoDBJeff Mathis1 Apr
  • Re: primary key/foreign key constraints with InnoDBStefan Hinz2 Apr
  • Re: primary key/foreign key constraints with InnoDBJeff Mathis2 Apr
    • Re: primary key/foreign key constraints with InnoDBStephen Giese2 Apr
  • Re: primary key/foreign key constraints with InnoDBJeff Mathis2 Apr