在多部分關係中接受父列上的空值

編輯：我想出了一個糟糕的解決方案（如下），它需要一些非規範化，但確實避免了 UDF 在CHECK約束中的風險。如果您有更好的答案，我將不勝感激。

1. 除任何“內置”類別外，每個類別僅對其各自的所有者可見。
2. 必須為每個項目（“OwnedItem”）分配一個對項目所有者可見的類別。

挑戰：由於所有者只能看到他們自己的類別和“內置”類別，因此如果一個所有者的項目被分配了另一個所有者的類別，則數據無效（例如），除非該類別不屬於任何人。

因此，我想在多列關係中的父列的一列上接受空值（或固定值，例如 -1）。

-- Categories have owners, or may be 'globally shared'
CREATE TABLE Category (
   Id int IDENTITY(1,1) NOT NULL,
   OwnerId int NULL,
   Title nvarchar(50) NOT NULL,
   CONSTRAINT PK_Category PRIMARY KEY (Id ASC)
)

SET IDENTITY_INSERT Category ON
GO
INSERT INTO Category 
   (Id, OwnerId, Title)
VALUES
   (-1, NULL, 'Hidden'),
   (0, NULL, 'Default')
GO
SET IDENTITY_INSERT Category OFF
GO

-- Items have owners,
-- and must be assigned a category from the same owner,
-- or be assigned a global category
-- i.e. prevent assignment of items to another Owner's private Category
CREATE TABLE OwnedItem (
   Id int IDENTITY(1,1) NOT NULL,
   CategoryId int NOT NULL,
   OwnerId int NOT NULL,
   Description varchar(50) NOT NULL
   CONSTRAINT PK_Ownedtem PRIMARY KEY (Id ASC)
)


ALTER TABLE OwnedItem
ADD CONSTRAINT FK_OwnedItemCategoryId
   FOREIGN KEY (CategoryId)
   REFERENCES Category (Id);

ALTER TABLE OwnedItem
ADD CONSTRAINT FK_CategoryOwnerOwnedItemOwner
   FOREIGN KEY (OwnerId)
   REFERENCES Category (OwnerId)

   -- How can I say:
   -- OR Category.OwnerId IS NULL

   ;

我怎樣才能允許OR Category.OwnerId IS NULL立即在上面？

INSERT INTO OwnedItem
   (CategoryId, OwnerId, Description)
VALUES
   (-1, 23, 'Person23s hidden item')
-- SHOULD SUCCEED

我剛剛想到了一種有點醜陋的方法來執行此操作。通過將鍵的附加副本複製到子表，我可以強制與父表建立這種關係，然後使用複制CHECK的資訊對子表執行更複雜的約束邏輯。

1. 將一CategoryOwnerId int NULL列添加到Item.
2. 建立關係，而不是Item.OwnerId。
3. 添加一個約束到Item：

如果使用 null OwnerId 來表示“內置”類別：

CategoryOwnerId NOT NULL OR CategoryId < 1

或者，具有相同的數據重複但非空的“內置”所有者值：

CategoryOwnerId = OwnerId OR CategoryOwnerId = -1

這是已實現的更改：

DROP CONSTRAINT FK_OwnedItemCategoryId
DROP CONSTRAINT FK_CategoryOwnerOwnedItemOwner
GO

ALTER TABLE OwnedItem
ADD CategoryOwnerId INT NULL
GO

-- Prior to this schema change, all categories were system-wide/built-in
UPDATE OwnedItem
SET CategoryOwnerId = -1
GO

ALTER TABLE OwnedItem
ALTER COLUMN CategoryOwnerId INT NOT NULL
GO

ALTER TABLE OwnedItem
ADD CONSTRAINT FK_OwnedItemCategory
   FOREIGN KEY (CategoryId, OwnerId)
   REFERENCES Bin (Id, OwnerId);
GO

ALTER TABLE OwnedItem
ADD CONSTRAINT CK_OwnedItemCategoryBuiltinOrOwnerMatch
CHECK (OwnerId = CategoryOwnerId OR CategoryOwnerId = -1)
GO

放

$$ OwnerId $$在 OwnerID 上允許空值。在您的類別表中放置一個空值記錄：

INSERT INTO Category (OwnerId,Title) SELECT NULL,'blah??'

我還添加到您的類別表中：

CONSTRAINT UC_OwnerID UNIQUE(OwnerID) 

這就是我在 SQL Fiddle http://sqlfiddle.com/#!3/ac720/2中為您工作的內容。

引用自：https://dba.stackexchange.com/questions/54488