• Status: Solved
• Priority: Medium
• Security: Public
• Views: 995

Updating a row in a table with a composite primary key composed of 3 primary keys and one foreign key

I have a table with a composite primary key composed of the fields  a,b and c and a foreign key 'd' and several other columns.
I hava a grid view for which I have dropdowns fora,b,c and d columns.
Now, my question is, when I update a row, can I update a, b, c or d individually while updating a row?

It's extremely urgent.

0
amukta
• 4
1 Solution

Billing EngineerCommented:
>Now, my question is, when I update a row, can I update a, b, c or d individually while updating a row?
yes, that is possible.
however, you should rethink your table design, and add a dedicated column as primary key (and eventually make your current pk a unique key).
this will make things, related to this table, a lot easier.

having composite primary keys is rarely a good design, with "1 exception":
a relation table with just the 2 foreign key field could very well be implemented with the primary key on just that 2 fields...
0

Commented:
yes you can still update parts of composite primary keys individually. however, if it's referenced as a foreign key from another table, then it may be blocked due to constraints.

create table tab (a int, b int, c int, d int, primary key(a,b,c))
insert into tab (1,2,3,4)

these are all legal:
update tab set b = 7 where a = 1
update tab set c = 8 where a = 1
update tab set d = 9, b = b + 1where a = 1
update tab set a = 2 where a = 1
0

Author Commented:
IMITCHIE,

THANK YOU!
I HAVE ONE MORE QUESTION.
IS IT FINE TO UPDATE THIS WAY?
UPDATE TAB SET C=8 WHERE A=1,B=2,C=3 AND D=4?
similarly update tab set b=3 where A=1,B=2,C=3 AND D=4?
update tab set c=4 where A=1,B=2,C=3 AND D=4?
AND SINCE D IS THE FOREIGN KEY, CAN I UPDATE IT SAYING
UPDATE TAB SET D=9 WHERE A=@A, B=@B, C=@C AND D=@D?

0

Author Commented:
ONE MORE QUESTION AND CN I ALSO UPDATE LIKE THIS?
UPDATE A=@A, B=@B,C=@C,D=@D WHERE A=@A,B=@B,C=@C,D=@D?
NOPE RIGHT? OR CAN I DO SO?

0

Author Commented:
ONE MORE QUESTION AND CN I ALSO UPDATE LIKE THIS?
UPDATE A=@A, B=@B,C=@C,D=@D WHERE A=@A,B=@B,C=@C,D=@D?
NO RIGHT? OR CAN I DO SO?
0

Author Commented:
ONE MORE QUESTION AND CN I ALSO UPDATE LIKE THIS?
UPDATE A=@A, B=@B,C=@C,D=@D WHERE A=@A,B=@B,C=@C,D=@D?
NO RIGHT? OR CAN I DO SO?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.