Foreign Key Constraint Issue

[mjjks]

Hi All,
Maybe I don't see something obvious here but I'm getting constraint error and don't see other way to solve it as to just drop constraints and then create them back later.

So, I have these tables, Highway,Route, Hwy_Rte junction table for many to many :

Hwy
------------
Hwy_ID (PK)
More Columns

Rte
------------
Route_No(PK)
More Columns

Hwy_Rte
------------
Hwy_ID (PK)
Route_No (PK)

I'm trying to update Rte_No in other tables that have Rte_No as FK, so I need to update Rte table first.
It's done in a loop, reading values from a template table but here's a sample of SQL statement:
[highlight]update RTE set Rte_No = 'WA69' where Rte_No = 'HWY69'[/highlight]
And I get an error:
[highlight]
UPDATE statement conflicted with COLUMN REFERENCE constraint 'FK_RTE__HWY_RTE'. The conflict occurred in database 'SWIRL', table 'HWY_RTE', column 'RTE_NO'.
The statement has been terminated.
[/highlight]

I think it happens because Rte_no is part of PK in Hwy_Rte table, so I ended up dropping constraint on Hwy_rte table and do update. Am I doing anything wrong here, any suggestion?
Thanks

~Steve

 

[gmmastros]

Well... Here's my 2 cents worth.

Your problem is that you are using actual data for your primary key. I always used a derived integer value for primary keys because there will always be someone that says, "I don't like that name, let's change it".

Basically, what I am suggesting is that you stop using the name of the route as the primary key and use integer values instead. Then, if you ever want to change the name of a route, you simply update the data in 1 table. The Route_No column wouldn't be a foreign key for anything, so updating it would be super simple and trivial.

I realize that what I am suggesting won't be easy to implement, but the effort will be well worth it in the end.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

my two cents worth, never drop those constraints, you have a possibility of introducing data integrity errors. You can have the constraint do cascading update or delete if you want.


Questions about posting. See faq183-874

 

[mjjks]

Thanks George and SQLSister.
Database wasn't designed by me, I'm just a contract developer adding enhancements to a front-end app and doing some back-end work as sprocs, triggers.

I'll suggest both of your solutions, but don't expect any changes as DB is being used by numerios apps for many years and client code and sprocs (around 200) will need to be changed as well as other apps that use XML extractions from it. Wish somebody thought about it when designing database.


Placing cascading updates might work in this situation. Have to see if DBA will agree to that.

 

[Catadmin]

The DB may be used by multiple applications, but you can still implement George's solution by adding an identity column to the RTE table, switching the PK to that field and then adding a non-clustered index to the Route_No column. This way, you are able to update that table with no problem plus it doesn't affect the apps that significantly.

CAVEAT: before you do any changes to that nature, you need to verify why Route_No was used as a Primary Key. It could be the original designers of the database did not want multiple Route_No's in the table, and only wanted one value for each. If this is still the case, you don't want to implement any such change PLUS you need to go verify why you're doing an Update on this table to add in another WA69 value where there was previously 1 WA69 and 1 HWY69.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"