• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 988
  • Last Modified:

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?

Please let me KNOW asap.
It's extremely urgent.

Thanks in advance.


0
amukta
Asked:
amukta
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]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
 
imitchieCommented:
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
 
amuktaAuthor 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?

PLEASE LET ME know ASAP.
THANKS IN ADVANCE
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
amuktaAuthor 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
 
amuktaAuthor 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
 
amuktaAuthor 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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now