SQL Server - self referencing constraint

Using SQL Server 2008

I have a table that contains stock/share/security information. This table holds the stocks that could be been owne开发者_高级运维d.

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?

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?