CODEWITHSUNDEEP
sqloracle-databasesql-update
james007
james007

Reputation: 741

SQL: How to update a Composite Key?

In the UI, i am showing account_id and account_name.
The user is able to update the account_id as well as account_name and the account_id is unique for the user. Since I am allowing the user to update the account_id (composite key) how can I create a where clause while doing update?

Below is my table design. 

CREATE TABLE accounts (
  user_id      VARCHAR2(20)  NOT NULL,
  account_id   VARCHAR2(20)  NOT NULL, 
  account_name VARCHAR2(20)  NOT NULL
)

ALTER TABLE accounts ADD CONSTRAINT
uk_myTable_1 UNIQUE (User_id, account_id)

Upvotes: 0

Views: 9579

Answers (4)

xQbert
xQbert

Reputation: 35353

The account_id and user_id values remain until you change them.

Thus. if it starts out as user_id 10 with account_id 20 and you change account_id to 30 e.g.

 UPDATE accounts 
    SET account_id = '30' 
  WHERE account_id = '20'
        AND user_id = '10';

While the above works for situations where you retain all values of your composite key, I'm all for an immutable key behind the scenes that is never shared or displayed. It eliminates problems with foreign key relationships and updates to parts of composite keys. To implement this, add a field (AccountID) which is the PK, and used in FK relationships.

Alternatively, Oracle can do something similar to this by using ROWSCN and in stateless environments rowdependencies on all tables. The problem you can run into with use if just AccountID is that its possible, in a multi user environment, to have person a make update to record 1 and user 2 to be editing record 1 and then save; overwriting user 1's updates. For this is the reason I mentioned ROWSCN and ROWDEPENDENCIES. However, if you are in a state aware environment, this would be overkill.

Lastly, you could change the order of operations to execute the update to the database BEFORE you update your class. This way you have both values.

Upvotes: 3

onedaywhen
onedaywhen

Reputation: 57093

The unit of work in SQL is the row, not the column.

Now don't actually run this but consider that:

UPDATE accounts 
   SET user_id = account_id, 
       account_id = user_id ;

would successfully swap user_id and account_id for all users. This is because all rows affected by the update (as determined by the WHERE clause) are updated all at once.

Upvotes: 0

mjwills
mjwills

Reputation: 23984

UPDATE accounts
SET whatevercolumnsyouwantasnormal
WHERE USER_ID = 'whatever' and ACCOUNT_ID = 'whatever2'

Obviously this will require you to track the original USER_ID and ACCOUNT_ID (separately from the new ones) until you issue the UPDATE.

If this is a web app (in particular), be sure to consider the case where the user triggers this process twice in quick succession.

Alternatively, look into Oracle's RowID. You could use it in the WHERE clause instead of the PK columns.

Upvotes: 0

Sparky
Sparky

Reputation: 15125

Try this:

UPDATE accounts SET account_id = New_account,account_name = new_name
WHERE user_id = Old_id and account_id = old_account_id

Upvotes: 0

Related Questions