> > > 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?

Of course.

Doing that, any record in the detail table with a specific account/username
combo would HAVE to exist in the master table. An FK always points
to a unique combination of values in the _master_ table.

How would you expect it to work otherwise?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com