sql server programming: Hello All,



I have two tables, parent and child. The parent table includes 5 primary
keys and the child table contains a composite key. When I try to update
some primary keys or foreign keys in both tables, I got following error
massages:



"UPDATE statement conflicted with TABLE FOREIGN KEY constraint"

"UPDATE statement conflicted with TABLE REFERENCE KEY constraint"



Is it possible to update the primary keys in the parent table and the
corresponding foreign keys in the child table?



Thank you very much!

Jean

[quoted text, click to view]
primary keys and the child table contains a composite key. When I try to
update some primary keys or foreign keys in both tables, I got following
error massages: <<

It is impossible to have more than one PRIMARY KEY in SQL.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Based on this vague description, I think you want something
like this:

CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
...);

CREATE TABLE OrderDetails
(order_nbr INTEGER NOT NULL
REFERENCES Orders(order_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);

The syntax for multi-column constraints are

PRIMARY KEY (a,b,c,..)
and
FOREIGN KEY (a,b,c,..) REFERENCES Table2(x,y,z,..)

--CELKO--


*** Sent via Developersdex http://www.developersdex.com ***

Jean,

If you use "on update cascade" in defining the FKs its automatic when you update the parent
for the child to be updated.

Otherwise you need to temporarily point the child rows to some other row, or delete them,
whilst you update the parent. ( Much messier ).

Regards
AJ