At 08:05 PM 17/07/2004 -0400, you wrote:

>""Florian Hector"" <FHector@...> wrote in message
>news:<002101c46c1b$819db650$027ba8c0@minitower>...
> > Don,
> >
> > In a script you would first create your two tables:
> >
> > Create Table Persons (
> > PersonID Integer not null,
> > PersonName VarChar(30)
> > );
> >
> > Create Table Attendance (
> > AttID Integer not null,
> > PersonID Integer not null,
> > AttDate Date
> > );
> >
>
>The tables aready exist and have been in use for a year so have plenty of
>data in them. Is there a problem with adding RI to tables with existing
>data?
>
> > After that you would create the primary keys for both tables:
> >
> > Alter Table Persons add constraint PK_Persons Primary Key (PersonID);
> > Alter Table Attendance add constraint PK_Attendance Primary Key
> > (AttID);
> >
>
>My Attendance table has a 2-field primary key: AttDate and PersonID.
>
> > After that you would create the RI keys for the second table:
> >
> > Alter Table Attendance add Constraint FK_Persons FOREIGN KEY
> > (PersonID) REFERENCES Persons (PersonID) ON UPDATE CASCADE ON DELETE
> > CASCADE;
> >
> >
>
>This is almost exactly what my 2nd post has. But it didn't work. Is it
>because data already exists in the table? If so then what do I have to do
>to add RI to existing tables? Delete my customer's data to get the tables
>right?

No, just complete any transaction that is looking at the tables, i.e. close
any table viewer or query window that has one or both tables occupied and
complete the existing transaction. You will get an Object In Use exception
if you don't.

Once the tables are clear of all outstanding transactions, you will be able
to create the foreign key relationship.

However, you also reported this error:

This operation is not defined for system tables.
unsuccessful metadata update.
STORE RDB$INDICES failed.
attempt to store duplicate value (visible to active transactions) in unique
index "RDB$INDEX_5".

This indicates that the key has already been created, but has been deferred
because one or both tables were in use. So you could try just closing
IBExpert, shutting down the server and restarting it, and going back into
IBExpert to see whether you now have that constraint in place.

Remember, these admin tools do their stuff by running queries inside
transactions, just like any client application. You need to be aware of
what's already going on in the database before attempting to change metadata...

/heLen