hmm, sorry my explanation was not clear

I have 2 table

Contact
Contact_archive

where i put in contact_archive all the contact that are "archived".

Because table contact we do some intensive select order by query and i don't want that the archived contact slow downs the process (because with them the table contact will be very very big)

table contact_archive is exactly the same structure as table contact

but the probleme you understand is the foreign key :( so i need a way out to make them working.

thanks you by advance
stephane


--- In firebird-support@yahoogroups.com, Fidel Viegas
<fidel.viegas@...> wrote:

>
> On Wed, Sep 23, 2009 at 1:59 PM, svanderclock <svanderclock@...> wrote:
> >
> >
> >
> > hello,
> >
> > in tableA i have some record with the field ID_contact that point to the table ContactA or to the table ContactB. so in this way i can not create a foreign key on my field ID_Contact.
> >
> > How to do ? i thing by stored procedure, but what is the best way and the most efficient way to do that in store procedure ?
>
> Hi Stephane,
>
> What exactly are you trying to achieve? Perhaps what you are trying to
> achieve is something like this:
>
> create table tableA {
> id bigint not null primary key,
> somedata int
> );
>
> create table contacts (
> id bigint not null primary key,
> contact_name varchar (30),
> telephone varchar (12),
> email varchar (60)
> );
>
> create table tableA_contacts (
> id bigint not null primary key, /* you may choose to omit this one*/
> tableA_id bigint not null references tableA (id),
> contact_id bigint not null references contacts (id)
> );
>
>
> This way you can have multiple contacts for your tableA, and avoid
> creating multiple tables for your contacts. Unless your two contact
> tables are different in some way.
>
> Regards,
>
> Fidel.
>