Board index » delphi » How to update primary and foreign keys in referenced tables

J.Peder...@techotel.dk (Jacob Pedersen)

Delphi Developer
Mon, 25 Aug 2003 23:43:02 GMT

How to update primary and foreign keys in referenced tables

I have 2 tables which is has a primary key called
"ReferenceNumber" in both of them. In one table this
is part of a combi-key containing also "GuestNumber"

These 2 tables have a foreignkey between the 2
"ReferenceNumber" fields.

If I try to do this:

UPDATE Reservations SET ReferenceNumber=17625 WHERE ReferenceNumber=7625
UPDATE ReservationMain SET ReferenceNumber=17625 WHERE ReferenceNumber=7625

or this (other order)

UPDATE ReservationMain SET ReferenceNumber=17625 WHERE ReferenceNumber=7625
UPDATE Reservations SET ReferenceNumber=17625 WHERE ReferenceNumber=7625

I get this error:

UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
'FK_RESERVATIONS_RESERVATIONMAIN'

How do I get my keys updated? (I have alot more tables than the 2 table
example here).

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

 
Alphonsu

Delphi Developer
Tue, 26 Aug 2003 00:52:42 GMT

Re:How to update primary and foreign keys in referenced tables


Quote

> UPDATE Reservations SET ReferenceNumber=17625 WHERE ReferenceNumber=7625
> UPDATE ReservationMain SET ReferenceNumber=17625 WHERE

ReferenceNumber=7625
Quote

> or this (other order)

> UPDATE ReservationMain SET ReferenceNumber=17625 WHERE

ReferenceNumber=7625
Quote
> UPDATE Reservations SET ReferenceNumber=17625 WHERE ReferenceNumber=7625

> I get this error:

> UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_RESERVATIONS_RESERVATIONMAIN'

    Do these tables reference each other??? If your application needs to do
things like these, in my modest opinion, you should seriously think about
using surrogate keys.

    Regards,

    Alphonsus.

Jacob Peders

Delphi Developer
Tue, 26 Aug 2003 00:06:55 GMT

Re:How to update primary and foreign keys in referenced tables


"Alphonsus" <no_spam@no_spam> wrote in <3aa7ac59_2@dnews>:

Quote

>> UPDATE Reservations SET ReferenceNumber=17625 WHERE
>> ReferenceNumber=7625 UPDATE ReservationMain SET ReferenceNumber=17625
>> WHERE
>ReferenceNumber=7625

>> or this (other order)

>> UPDATE ReservationMain SET ReferenceNumber=17625 WHERE
>ReferenceNumber=7625
>> UPDATE Reservations SET ReferenceNumber=17625 WHERE
>> ReferenceNumber=7625

>> I get this error:

>> UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
>> 'FK_RESERVATIONS_RESERVATIONMAIN'

>    Do these tables reference each other??? If your application needs to
>    do
>things like these, in my modest opinion, you should seriously think
>about using surrogate keys.

>    Regards,

>    Alphonsus.

Well, it's easier to catch the exception when trying to delete in one
table. I guess I could turn off the check when inserting/updating but
I do not think this is optimal.

The funny thing is that there is only one relation between them, but it's
like they are eachothers masters. Weird or maybe I'm just too tired to
see the problem.

What are surrogate keys?

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

John Pierc

Delphi Developer
Tue, 26 Aug 2003 00:27:35 GMT

Re:How to update primary and foreign keys in referenced tables


Never, under any circumstances, use a primary key that must be changed.
You can drop and reset the constraint, but this is just a bandaid over
poor design.

John Pierce

Don

Delphi Developer
Tue, 26 Aug 2003 01:29:02 GMT

Re:How to update primary and foreign keys in referenced tables


Quote
"Jacob Pedersen" <J.Peder...@techotel.dk> wrote in message

news:Xns905EAE5461A65jpedersentechotel@207.105.83.62...

Quote
> What are surrogate keys?

A surrogate key is an artificial key introduced to uniquely identify an
entity.  It is not entity attribute data, and will not change during the
lifetime of the entity.  Think of it as an Identity (SQL Server), Sequence
(Oracle), or Generator (InterBase).
Sunil Furtad

Delphi Developer
Tue, 26 Aug 2003 13:30:45 GMT

Re:How to update primary and foreign keys in referenced tables


    You are probably looking to implement cascading updates. This is
supported in SQL Server 2000 but not in SQL Server 7. You will have to use
triggers to do this in SQL Server 7 which also means that you will have to
take care of RI through triggers too and not through DRI.
    Has its disadvantages but can be implemented.
--
Sunil Furtado
New Mangalore Port Trust

Quote
Jacob Pedersen <J.Peder...@techotel.dk> wrote in message

news:Xns905EAA473A98Bjpedersentechotel@207.105.83.62...
Quote
> I have 2 tables which is has a primary key called
> "ReferenceNumber" in both of them. In one table this
> is part of a combi-key containing also "GuestNumber"

> These 2 tables have a foreignkey between the 2
> "ReferenceNumber" fields.

> If I try to do this:

> UPDATE Reservations SET ReferenceNumber=17625 WHERE ReferenceNumber=7625
> UPDATE ReservationMain SET ReferenceNumber=17625 WHERE

ReferenceNumber=7625
Quote

> or this (other order)

> UPDATE ReservationMain SET ReferenceNumber=17625 WHERE

ReferenceNumber=7625
Quote
> UPDATE Reservations SET ReferenceNumber=17625 WHERE ReferenceNumber=7625

> I get this error:

> UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK_RESERVATIONS_RESERVATIONMAIN'

> How do I get my keys updated? (I have alot more tables than the 2 table
> example here).

> --
> Jacob Pedersen
> AK Techotel
> J.Peder...@nospamtechotel.dk
> (Remove nospam when replying!)

Jacob Peders

Delphi Developer
Tue, 26 Aug 2003 16:30:51 GMT

Re:How to update primary and foreign keys in referenced tables


John Pierce <jpie...@healthplanning.com> wrote in
<3AA7B2F6.422AF...@healthplanning.com>:

Quote
>Never, under any circumstances, use a primary key that must be changed.
>You can drop and reset the constraint, but this is just a bandaid over
>poor design.

>John Pierce

It never was intended to be updated. Actually there was a surogate key at
the beginning, but since the other two fields made up a unique key that
never changed, we dropped the surrogate key.

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

Jacob Peders

Delphi Developer
Tue, 26 Aug 2003 16:32:15 GMT

Re:How to update primary and foreign keys in referenced tables


"Sunil Furtado" <su...@furtado.de> wrote in <3aa86d42_1@dnews>:

Quote
>    You are probably looking to implement cascading updates. This is
>supported in SQL Server 2000 but not in SQL Server 7. You will have to use
>triggers to do this in SQL Server 7 which also means that you will have to
>take care of RI through triggers too and not through DRI.

>    Has its disadvantages but can be implemented.
>--
>Sunil Furtado
>New Mangalore Port Trust

Cascading updates are bad in my world. I think the problem is in the design
of the tables since it never was intended to be updated like this.
I think the right way to do it is

INSERT copy
DELETE old

--
Jacob Pedersen
AK Techotel
J.Peder...@nospamtechotel.dk
(Remove nospam when replying!)

Other Threads

1. MSSQL: Cascade Updating a Primary Key on Foreign Tables

2. Cascade Updating a Primary Key on Foreign Tables

3. Tables with circular references in foreign key

4. Table reference of foreign key

5. Error $2eb9, update on primary key of master table

6. primary and foreign keys

7. MSSQL, D3C/S: Primary --> Foreign Keys

8. sql triggers primary and foreign keys HELP

9. Field as both Primary and Foreign Key

10. Interbase Indexes on Primary and Foreign keys.