SQL Server - 自引用约束

发布于 2024-10-16 22:55:42 字数 437 浏览 2 评论 0原文

使用 SQL Server 2008，

我有一个包含股票/股票/证券信息的表。该表保存了可以拥有的股票。

每只股票都有与其相关的货币。问题是货币也是一种股票，也可以被拥有。即当持有现金时

create table Stock
(
StockId int identity(1,1) not null CONSTRAINT StockPK PRIMARY KEY,
stockName varchar(100),
...
CurrencyId CONSTRAINT StockCurrencyIDFK FOREIGN KEY REFERENCES Stock(StockID),
)

对于现金行，CurrencyId 将等于 StockId

我的问题是将货币数据放入表中。在插入时，如何使用 stockID 的标识值填充CurrencyID 列？

原文

Using SQL Server 2008

I have a table that contains stock/share/security information. This table holds the stocks that could be been owned.

Each stock has a currency associated with it. The problem is that a currency is also a stock in that is also can be owned. ie when cash is held

create table Stock
(
StockId int identity(1,1) not null CONSTRAINT StockPK PRIMARY KEY,
stockName varchar(100),
...
CurrencyId CONSTRAINT StockCurrencyIDFK FOREIGN KEY REFERENCES Stock(StockID),
)

For a cash row the CurrencyId will equal the StockId

My problem is getting currency data into the table. On an insert how does one populate the CurrencyID column with the identity value of stockID?

分享到QQ

分享到微博

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

发布评论

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

执手闯天涯 2024-10-23 22:55:42

首先，我认为 CurrencyId 之后必须有一个类型说明符（可能是 int）。

继续你的问题，如果你坚持这样的设计，我认为插入自引用行可以在触发器的帮助下完成。只有 CurrencyId 应该允许 NULL，即不要将其定义为 NOT NULL （您的示例中没有这种情况，多么幸运）。这里有一个问题：技术上应该允许 NULL，但逻辑上不允许，也就是说你必须始终有一个值，否则触发器会为你填充它。

顺便说一下，当我们讨论触发器时，这里有一个可能的实现：

CREATE TRIGGER Stock_UpdateCurrencyId
ON Stock
FOR INSERT, UPDATE
AS
UPDATE Stock
SET CurrencyId = StockId
FROM inserted
WHERE Stock.StockId = inserted.StockId
  AND inserted.CurrencyId IS NULL

所以，这个想法基本上是这样的：如果您插入带有空 CurrencyId 的行（或更新 CurrencyId< /code> 为 NULL），这意味着您希望该行引用自身（至少这是触发器认为您想要的），否则您为 CurrencyId 指定正确的引用值，并且触发器会绕过此类行。

还记得我说过在逻辑设计中不应允许 NULL 吗？好吧，我这么说可能有点太仓促了。实际上，如果您仅为 INSERT 定义触发器，您将能够存储 NULL，但只能在插入之后通过后续的 UPDATE 存储。但我宁愿没有 NULL。

不管怎样，你还喜欢你的设计吗？

First of all, I think there must be a type specifier after CurrencyId (probably int).

Proceeding with your question, if you insist on such a design, I think inserting self-referenced rows can be done with the help of a trigger. Only CurrencyId should allow NULLs, i.e. do not define it as NOT NULL (which you are not in your example, how fortunate). And here's a catch: NULLs should be allowed technically, but not logically, that is you must always have a value there, otherwise the trigger will fill it for you.

By the way, while we are talking about the trigger, here's a possible implementation:

CREATE TRIGGER Stock_UpdateCurrencyId
ON Stock
FOR INSERT, UPDATE
AS
UPDATE Stock
SET CurrencyId = StockId
FROM inserted
WHERE Stock.StockId = inserted.StockId
  AND inserted.CurrencyId IS NULL

So, the idea is basically like this: if you are inserting a row with empty CurrencyId (or update CurrencyId with NULL), that means you want the row to reference itself (at least that's what the trigger would think you wanted), otherwise you specify the correct reference value for CurrencyId and the trigger bypasses such rows.

Remember when I said that you should not allow NULLs in your logical design? Well, I might have been a bit too hasty in saying so. Actually if you define the trigger for INSERT only, you will be able to store NULLs, but only after the insert, by way of the sebsequent UPDATE. But I would rather not have NULLs.

Anyway, do you still like your design?

回复收藏 0 原文

~没有更多了~