--- In firebird-support@yahoogroups.com, "Martijn Tonies"
<m.tonies@u...> wrote:

>
> > No, the mastuser table is the parent, basket is the child. It's a one
> > to many relationship.
>
> What do you mean by "No".

I meant that I do not want the relationship to be FROM the basket
table, the direction is from mastuser table to basket. basket is the
child.

>
> > I want the constraint in so that if I delete a mastuser record, all
> > corresponding basket records will be deleted as well.
>
> Yes well, that's what I want to do too :-)
>
> C'mon, you can do better than this...
>

I don't understand what I can do better. I have explained it.

I want to have a classic master detail foreign key constraint

mastuser is the master table
basket is the detail table.

I have a primary key on the master table set to be the fields ACCOUNT
& USER_NAME.

My primary key on the basket table is an autoincrement field called ID .

All I want to do is set up a foreign key contstraint so that when I
delete a record in the master table, it deletes all the corresponding
records in the basket table.

However I cannot make ACCOUNT & USER_NAME a primary key on the detail
table because they may be more than one record.

I have set up a foreign key constraint on the mastuser (master) table
which links to the basket (detail table) based on ACCOUNT & USERNAME,
but if I insert a record into the mastuser (master) table I get the
error message appear.

Are you saying that master detail constraints should be set up on the
detail table?


Mike

> With regards,
>
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL &

MS SQL

> Server
> Upscene Productions
> http://www.upscene.com
>
> >
> > Mike
> >
> >
> >
> > --- In firebird-support@yahoogroups.com, "Martijn Tonies"
> > <m.tonies@u...> wrote:
> > > Hello Mike,
> > >
> > > > I have a table called mastuser which has a primary key set to be
> > > > ACCOUNT and USER_NAME.
> > > >
> > > > I have a child table called basket which includes those same two
> > > > fields (with a normal non unique index), but the primary key

is on an

> > > > autoincrement field called ID.
> > > >
> > > > I set up a foreign key based on ACCOUNT and USER_NAME for

mastuser and

> > > > ACCOUNT and USER_NAME on basket even though it defaults to ID.

When I

> > > > try and save the alterations I get the following error:
> > > >
> > > > This operation is not defined for system tables.
> > > > unsuccessful metadata update.
> > > > could not find UNIQUE INDEX with specified columns.
> > > >
> > > > Is this saying that I need a unique index on the basket table
> > > > consisting of ACCOUNT + USER_NAME?
> > >
> > > No, this error message should be changed to
> > > "cound not find an unique or primary key constraint".
> > >
> > > > I cannot do this because there may be multiple records in the

basket

> > > > table. There will however only be one in the mastuser table.
> > >
> > > The FK should point FROM the "basket" to the "mastuser"
> > > table, right?
> > >
> > > Now, if account+username is the PK of "mastuser", this shouldn't
> > > be a problem.
> > >
> > > if not, then explain your question in a better way.