如何修复“alter table语句与check约束冲突”

xdyibdwo  于 2021-07-24  发布在  Java
关注(0)|答案(0)|浏览(364)
CREATE FUNCTION ufn_CheckOrderItem(@OrderID INT)
RETURNS BIT
AS
    BEGIN 
         DECLARE @Add BIT;
         IF((SELECT COUNT(ProductId) FROM OrderItem WHERE OrderId = @OrderID) < 5)
             SET @Add = 1
         ELSE
             SET @Add = 0
         RETURN @Add
    END
GO

ALTER TABLE [OrderItem]
ADD CONSTRAINT CheckOrderItem
   CHECK(dbo.ufn_CheckOrderItem(OrderId) = 1)

这是一个错误:
alter table语句与check约束“checkorderitem”冲突。冲突发生在数据库“northwind”、表“dbo.orderitem”、列“orderid”中。
这是表格>。

CREATE TABLE [dbo].[OrderItem](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OrderId] [int] NOT NULL,
    [ProductId] [int] NOT NULL,
    [UnitPrice] [decimal](12, 2) NOT NULL DEFAULT ((0)),
    [Quantity] [int] NOT NULL DEFAULT ((1)), 

    CONSTRAINT [PK_ORDERITEM] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]    
GO

ALTER TABLE [dbo].[OrderItem]  WITH CHECK ADD  CONSTRAINT [FK_ORDERITE_REFERENCE_ORDER] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Order] ([Id])
GO

ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_ORDERITE_REFERENCE_ORDER]
GO

ALTER TABLE [dbo].[OrderItem]  WITH CHECK ADD  CONSTRAINT [FK_ORDERITE_REFERENCE_PRODUCT] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([Id])
GO

ALTER TABLE [dbo].[OrderItem] CHECK CONSTRAINT [FK_ORDERITE_REFERENCE_PRODUCT]
GO

表orderitem

Id OrderId ProductId UnitPrice Quantity
1   1      11        14.00     12
2   1      42        9.80      10
3   1      72        34.80     5
4   2      14        18.60     9
5   2      51        42.40     40
6   3      41        7.70      10
7   3      51        42.40     35
8   3      65        16.80     15
9   4      22        16.80     6
10  4      57        15.60     15

我想检查一个订单不能有5个以上的产品。如果订单有5种以上的产品,则无法插入。

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题