sqlserver2005约束和验证

Question

我有一个表，它用以下列存储付款详细信息：（paymentmethod-支票、现金或信用卡）、（Checknumber）、（Cardholdersname）和（creditcardnumber）。

问题是：当paymentmethod是cash时，我如何验证Cardholdersname、creditcardnumber应为空，当paymentmethod是creditcard时，Checknumber应为空。

Answer 1

您实际上有两个不同的表：

PaymentDetails(ID, PaymentMethod) and
CreditCardDetails (PaymentDetailsID, CardholderName, CreditCardNumber)

两个表中的所有列都不可为null。

---

可以将所有三个案例放在同一张表中。在这种情况下，请尝试以下操作：

CREATE TABLE [dbo].[PaymentDetails]
(
    ID INT IDENTITY(1,1) NOT NULL,
    PaymentMethod INT NOT NULL CHECK (PaymentMethod IN (1,2,3)), -- Cash, Check, Credit
    CheckNumber int NULL,
    CardHoldersName nvarchar(100) NULL,
    CreditCardNumber varchar(20) NULL
)
ALTER TABLE [dbo].[PaymentDetails]
    ADD CONSTRAINT [PaymentTypeConstraint] 
    CHECK  ((PaymentMethod = 1 AND CheckNumber IS NULL AND 
                                   CardholdersName IS NULL AND
                                   CreditCardNumber IS NULL) OR
            (PaymentMethod = 2 AND CheckNumber IS NOT NULL AND 
                                   CardholdersName IS NULL AND
                                   CreditCardNumber IS NULL) OR
            (PaymentMethod = 3 AND CheckNumber IS NULL AND 
                                   CardholdersName IS NOT NULL AND
                                   CreditCardNumber IS NOT NULL));