on update cascade does not update foreign key columns 1

[nishasp]

Hi all,

I am using an identity for a primary key. I have a foreign key in another table which references this primary key and for which the on update cascade is enabled. Does this mean that when i enter in a row for the table with the primary key, the table with the foreign key should also have the same value as the primary key?

For some reason, this is not working, and i don't know how to get around it. I won't know what the primary key value is, as i'm using the identity, so i need the database the automatically enter in the corresponding value for the foreign key, each time i insert rows into all the tables.

Thanks,
nisha

 

[gradley]

Using IDENTITY primary keys can be tricky especially when you have FK references from other tables. The cascading only works for updates and deletes.

There are a couple of options you can try:

1. Retrieve the IDENTITY value from the record just inserted

-- From BOL...
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

2. Create an insert trigger on the first table that will create the second table record.

 

[SQLSister]

Gradley, you are close to the proper procedure, but you should NEVER use @@identity as it it will not always give you the correct value creating data integrity problems. USe scope_identity instead. Also you shouldset the value to a variable. And then use the variable in further processing.

Declare @ID as int
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @ID = scope_indentity()
Insert into table2 (FKField, field1)
VAlues (@ID, 'test')

Questions about posting. See faq183-874

 

[gradley]

Thanks SQLSister.

I read something about SCOPE_IDENTITY before but have yet to used it. @@IDENTITY has worked for me but i've used it in a very limited capacity.

I do agree after reading BOL that SCOPE_IDENTITY is much safer to use.

 

[nishasp]

Do i create this procedure as a trigger that occurs each time i insert values into the primary table?

 

[SQLSister]

Depends on what information you are putting into the related table. Usually you have information for other fields that would not be available in a trigger, so you include inthe insert to the first table and then the insert into the related table inthe same Stored porcedure.

Questions about posting. See faq183-874