I have 3 tables, all of which are related by Social Security number.  In 

the event a user messes up a number, I have a way for them to edit it.  I'm 

getting an error when I run 3 update statements in a stored procedure to do 

the updating in the three tables... but it's returning an error.



update statement conflicted with COLUMN FOREIGN KEY constraint...



I know there must be a way around this but I'm baffled...  I thought I did 

this before this way and it worked fine!  Is there a special SQL statement 

I am blanking out?



Thanks in advance for help!  Regards, -AR

If you try to update the child table first you get an error because there is

no matching value in the parent table



If you try to update the parent table first, you get an error because there

are fields in the child table that depend on the value in the parent table.



However, the fact you are facing this problem is a reminder that you

shouldn't use semantic keys in a database.



The following is a quote from Daniel, who's the moderator of these lists -

he puts it quite well:



<quote>

By now, Allan, you sould b grasping the disadvantages of one of our

authors, Dave Liske's, pet hates: "semantic keys".



Let me explain:

A semantic key is a value from the real world which appears to identfy a

record in a unique way - a National Insurance number in an employee

database, for instance. Everyone has an NI value, and no one can share it,

right? Right?



Reality always throws up exceptions. Say you employ a foreign worker on a

temporary visa, for example - no NI No., suddenly no primary key. Even in

the best of worlds, you now find yourself _inventing_ imanginary-real

values for these records - which is only cool until someone forgets that

it's an imaginary value and fills out an NI return for the guy. In the

worst of worlds, your DB screws up and starts applying the same NI value to

all the overseas workers, or you end up hiring someone who _actually_has_

the NI no. you just applied to Mr Temp.



Semantic keys are a fundamental bad idea in database design and should

never be used. Always use an autonumber field as the primary key.

</quote>



HTH



Cheers

Ken



----- Original Message -----

From: "Arbon Reimer" 

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, August 17, 2000 8:54 PM

Subject: [asp_databases] Changing primary key value... update error





> I have 3 tables, all of which are related by Social Security number.  In

> the event a user messes up a number, I have a way for them to edit it.

I'm

> getting an error when I run 3 update statements in a stored procedure to

do

> the updating in the three tables... but it's returning an error.

>

> update statement conflicted with COLUMN FOREIGN KEY constraint...

>

> I know there must be a way around this but I'm baffled...  I thought I did

> this before this way and it worked fine!  Is there a special SQL statement

> I am blanking out?

>

> Thanks in advance for help!  Regards, -AR

>