Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
protocol
Starting Member
3 Posts |
 Posted - 2011-09-01 : 07:19:59
|
| Hello everyone,i am struck at 1 point. I have a main table named employee and I need to update its employeeID which is primary key also. I am unable to do it as there are few tables which has this employeeid as foreign key so even if i try to update them first it wont allow me to do it, its kind of dead lock condition.I tried droping the foreign keys before updating the tables but when i again go for adding the foreign keys again it gives error and it made my database unstable.I even cannot modify my employee table and make it cascade on delete, so is there anyone who can help me out of this damn chaotic condition.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-01 : 07:35:40
|
| why should you change the pk value that you've already generated?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
protocol
Starting Member
3 Posts |
Posted - 2011-09-01 : 08:08:15
|
| I know its generally not done but there is a perticular condition when i have to delete it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-02 : 00:29:05
|
| actually when you create fk relation if you specify ON DELETE CASCADE clause it will take care of deletion of child records by itself. what you can do is to capture these values using trigger onto an audit table and then use the other values to join to your parent table to retrieve new pk generated for them and insert them with new value into your child table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
protocol
Starting Member
3 Posts |
Posted - 2011-09-02 : 03:50:50
|
| Thanks a lot Sir for replying, I came up with another approach Its like Inserting a new record in the main table and then updating the records in the associated tables with the new ID on the basis of old ID and then delete the old record with the old id in the main table.If my approach din't worked for any reason i will surely use your approach which is also nice.I will post the result soon.Thanks a lot. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-02 : 04:21:28
|
| ok no probs...glad that you sorted it out------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Update table with foreign key constraints