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]
