Changing Primary Key Values when foreign key constraints exist

Changing Primary Key Values when foreign key constraints exist

Post by Stephen Ah » Tue, 23 Jan 2001 13:34:36



Given the following tables :
--
create table table1 (a int not null primary key, b int null)
create table table2 (a int not null, c int not null, d int null primary
key(a,c)
   foreign key (a) references table1 (a))

insert table1 values (1, 1)
insert table2 values (1, 1, 11)
insert table2 values (1, 2, 12)
--
Now, I want to be able to do something like this :
update table1 set a = 2 where a = 1
update table2 set a = 2 where a = 1

, but the foreign key constraint does not allow it.
I can think of some ways around this :

a) disable the foreign key constraint, perform the updates, then re-enable
the constraint.

b) create a new table1 record, do the updates, then delete the original
table1 record. eg
begin tran
insert table1 values (2, 1)
update table2 set a = 2 where a = 1
delete table1 where a = 1
commit tran

I don't much like either solution.
Is there a better approach ?

TIA,
Stephen

 
 
 

Changing Primary Key Values when foreign key constraints exist

Post by Itzik Ben-Ga » Tue, 23 Jan 2001 16:42:45


First, what version of SQL Server are you talking about?
Note that in SQL Server 2000 the foreign key supports ON DELETE CASCADE and
ON UPDATE CASCADE, but I assume you are not running SQL Server 2000.

To answer your question, there's another approach. You can drop / disable
the foreign key and enforce RI + cascading updates and/or deletes using
triggers.
Even with triggers, if you want to cascade changes to the primary key, and
you want to support multi row actions, you'll have to add a non changing
surrogate key in order to relate the rows before and after the update in the
deleted and inserted tables.

To support only single row updates, you can use the following set of
triggers:

-- delete cascade trigger on Table1...

CREATE TRIGGER trg_d_Table1_on_delete_cascade ON Table1 FOR DELETE
AS

DELETE FROM Table2
FROM
    Table2 AS S
  JOIN
    deleted AS D ON S.a = D.a
GO

-- update cascade trigger on Table1...

CREATE TRIGGER trg_u_Table1_on_update_cascade ON Table1 FOR UPDATE
AS



IF UPDATE(a)

    UPDATE Table2
      SET a = (SELECT a FROM inserted)
    FROM
        Table2 AS S
      JOIN
        deleted AS D ON S.a = D.a

  BEGIN
    RAISERROR('Updates to more than one row in Table1 are not allowed.
TRANSACTION rolled back.', 10, 1)
    ROLLBACK TRANSACTION
  END
GO

-- prevent insert / update trigger on Table2...

CREATE TRIGGER trg_iu_Table2_prevent_insupd ON Table2 FOR INSERT, UPDATE
AS




                  COUNT(*)
                FROM
                    Table1 AS P
                  JOIN
                    inserted AS I ON P.a = I.a)
BEGIN
  RAISERROR('Result rows in Table2 are orphaned. TRANSACTION rolled back.',
10, 1)
  ROLLBACK TRANSACTION
END
GO

To support multi-row updates add a surrogate key and alter the trigger:

ALTER TABLE Table1
  ADD surrogate_key int NOT NULL IDENTITY(1,1)
      CONSTRAINT UNQ_Table1_surrogate_key UNIQUE
GO

ALTER TRIGGER trg_u_Table1_on_update_cascade ON Table1 FOR UPDATE
AS



IF UPDATE(surrogate_key)
BEGIN
  RAISERROR('Updates to surrogate_key are not allowed. TRANSACTION rolled
back.', 10, 1)
  ROLLBACK TRANSACTION
END
ELSE

    UPDATE Table2
      SET a = I.a
    FROM
        Table2 AS S
      JOIN
        deleted AS D ON S.a = D.a
      JOIN
        inserted AS I ON D.surrogate_key = I.surrogate_key
GO

--
BG

Hi-Tech College, Israel
http://sql.hi-tech.co.il


Quote:

> Given the following tables :
> --
> create table table1 (a int not null primary key, b int null)
> create table table2 (a int not null, c int not null, d int null primary
> key(a,c)
>    foreign key (a) references table1 (a))

> insert table1 values (1, 1)
> insert table2 values (1, 1, 11)
> insert table2 values (1, 2, 12)
> --
> Now, I want to be able to do something like this :
> update table1 set a = 2 where a = 1
> update table2 set a = 2 where a = 1

> , but the foreign key constraint does not allow it.
> I can think of some ways around this :

> a) disable the foreign key constraint, perform the updates, then re-enable
> the constraint.

> b) create a new table1 record, do the updates, then delete the original
> table1 record. eg
> begin tran
> insert table1 values (2, 1)
> update table2 set a = 2 where a = 1
> delete table1 where a = 1
> commit tran

> I don't much like either solution.
> Is there a better approach ?

> TIA,
> Stephen


 
 
 

Changing Primary Key Values when foreign key constraints exist

Post by Stephen Ah » Wed, 24 Jan 2001 08:15:52


Quote:> what version of SQL Server are you talking about?

SQL Server 7, sp 2.

From your post, and from other material I've found, it seems the best idea
is to try to
avoid changing the values of the primary key fields if at all possible, by
using a
surrogate key.

Thanks for your response.
Stephen.


Quote:> First, what version of SQL Server are you talking about?
> Note that in SQL Server 2000 the foreign key supports ON DELETE CASCADE
and
> ON UPDATE CASCADE, but I assume you are not running SQL Server 2000.

> To answer your question, there's another approach. You can drop / disable
> the foreign key and enforce RI + cascading updates and/or deletes using
> triggers.
> Even with triggers, if you want to cascade changes to the primary key, and
> you want to support multi row actions, you'll have to add a non changing
> surrogate key in order to relate the rows before and after the update in
the
> deleted and inserted tables.

 
 
 

1. BCP INTO A TABLE THAT CONTAINS PRIMARY KEY AND FOREIGN KEY CONSTRAINTS

HELLO FRIENDS .
                 THIS IS RAMU HERE. I HAVE A QUERY . I WANT TO BCP
LARGE AMOUNTS OF DATA INTO A TABLE THAT CONTAINS PRIMARY AND FOREIGN
KEY CONSTRAINTS .
I AM NOT ABLE TO FIND A POSSIBLE SOLUTION .

CAN ANYONE OF YOU HELP ME . IF SO SEND ME THE SYNTAX FOR THE WHOLE
PROCESS.
THIS IS VERY URGENT .

RAMU

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

2. NT Visual C++ Runtime Error

3. Q: foreign key constraint on partial primary key ?

4. Help creating .exe file

5. foreign key w/o foreign/primary key

6. SQL server ADO.command.execute stored procedure does not return a records

7. How to check if primary key constraint exist and drop it if it exist

8. Joining tables

9. Composite Primary key or 1 primary key column with all values

10. (Importing data into a table that has primary and foreign key constraints through bcp

11. 8i imp show=y not showing primary and foreign key constraints