SQL：如何更新复合键？

发布于 2024-12-16 17:22:51 字数 446 浏览 4 评论 0原文

在用户界面中，我显示 account_id 和 account_name。
用户能够更新 account_id 以及 account_name，并且 account_id 对于用户来说是唯一的。由于我允许用户更新 account_id （复合键），如何在更新时创建 where 子句？

下面是我的表格设计。

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)

原文

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)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

煞人兵器 2024-12-23 17:22:51

account_id 和 user_id 值将一直保留，直至您更改它们。

因此。如果它以 user_id 10 和 account_id 20 开头，并且您将 account_id 更改为 30 例如，

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

虽然上述适用于您保留所有值的情况你的复合密钥，我完全支持幕后的不可变密钥，永远不会共享或显示。它消除了外键关系的问题并更新了部分复合键。要实现此目的，请添加一个字段 (AccountID)，它是 PK，并在 FK 关系中使用。

或者，Oracle 可以通过使用 ROWSCN 以及在无状态环境中所有表上的行依赖性来执行类似的操作。如果仅使用 AccountID，您可能会遇到的问题是，在多用户环境中，可以让用户更新记录 1，让用户 2 编辑记录 1，然后保存；覆盖用户 1 的更新。这就是我提到 ROWSCN 和 ROWDEPENDENCIES 的原因。但是，如果您处于状态感知环境中，这就有点矫枉过正了。

最后，您可以在更新类之前更改操作顺序以执行数据库更新。这样你就拥有了这两种价值观。

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.

回复收藏 0 原文

痴情换悲伤 2024-12-23 17:22:51

试试这个：

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

Try this:

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

回复收藏 0 原文

怪我太投入 2024-12-23 17:22:51

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

显然，这将要求您跟踪原始的 USER_ID 和 ACCOUNT_ID（与新的分开），直到您发出 UPDATE。

如果这是一个网络应用程序（特别是），请务必考虑用户快速连续触发此过程两次的情况。

或者，查看 Oracle 的 RowID 。您可以在 WHERE 子句中使用它而不是 PK 列。

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.

回复收藏 0 原文

枉心 2024-12-23 17:22:51

SQL 中的工作单元是行，而不是列。

现在不要实际运行它，但考虑：

UPDATE accounts 
   SET user_id = account_id, 
       account_id = user_id ;

将成功地为所有用户交换user_id和account_id。这是因为受更新影响的所有行（由 WHERE 子句确定）都会立即更新。

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.

回复收藏 0 原文

~没有更多了~