在SQL Server 2008中检查约束 [英] Check constraint in SQL Server 2008

问题描述

请，我刚开始学习SQL并陷入困境.我正在尝试为我的测试项目建立数据库，我创建了一些表，建立了关系，定义了主键和外键.....所有这些都通过可视化界面在SQL Server 2008中进行(表设计/编辑) ，没有语句编码(尚未到达那里，但我会:)).

Please, I just starting to learn SQL and got stuck. I'm trying to build a database for my test project, I've created some tables, did the relations, defined primary and foreign keys.....all of this in SQL Server 2008 through Visual interface (table design/edit), no statement coding (didn't get there yet, but I will :) ).

我在名为Orders的表中有一列Tax，我做完作业后发现，最好将decimal数据类型(我使用十进制(5，2))与CHECK一起使用约束.

I have a column Tax in a table called Orders and I did my homework and found that it's best to use a decimal data type (I used decimal(5, 2)) with a CHECK constraint.

所以我右键单击列->约束，然后在表达式中输入

So I right clicked the column -> constraints and in expression I typed

([TAX] >= (0.00) AND [TAX] <= (100.00))

我的值超出了检查约束，我可以键入123456.0999并在表中得到1234560999，如果我键入2.5则得到25 ...所以 CHECK CONSTRAINT 不起作用它应该???

My values exceed the check constraint, I can type 123456.0999 and I get 1234560999 in the table, and if I type 2.5 I get 25..... so a CHECK CONSTRAINT is not working what it should???

请帮助

这是我的桌子旁边的创建脚本

    USE [MyCompany]
GO

/****** Object:  Table [dbo].[Orders]    Script Date: 03/22/2013 11:33:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderDateTime] [smalldatetime] NOT NULL,
    [CustomerID] [int] NOT NULL,
    [Tax] [decimal](5, 2) NULL,
    [Shipping] [decimal](7, 3) NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [OrderID] 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].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [CK_Orders_Tax] CHECK  (([Tax]>=(0.0) AND [Tax]<=(100.0)))
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [CK_Orders_Tax]
GO

推荐答案

检查约束是否有效-尝试以下操作:

Check constraints just work - try this:

CREATE TABLE Orders (OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
                     TotalAmount DECIMAL(18,2),
                     Tax DECIMAL(5,2) CHECK (Tax >= 0.0 AND Tax <= 100.0)
                    )

现在，当您尝试插入数据时:

Now when you try inserting data:

INSERT INTO dbo.Orders(TotalAmount, Tax)
VALUES (100.0, 2.75)     --> works just fine

INSERT INTO dbo.Orders(TotalAmount, Tax)
VALUES (200.0, 15.75)   --> works just fine

INSERT INTO dbo.Orders(TotalAmount, Tax)
VALUES (300.0, -2.0)

消息547，级别16，状态0，第1行
INSERT语句与CHECK约束"CK__Orders__Tax__164452B1"冲突.在数据库"test"的表"dbo.Orders"的"Tax"列中发生了冲突.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__Orders__Tax__164452B1". The conflict occurred in database "test", table "dbo.Orders", column 'Tax'.

INSERT INTO dbo.Orders(TotalAmount, Tax)
VALUES (400.0, 200.75)

消息547，级别16，状态0，第1行
INSERT语句与CHECK约束"CK__Orders__Tax__164452B1"冲突.在数据库"test"的表"dbo.Orders"的"Tax"列中发生了冲突.

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__Orders__Tax__164452B1". The conflict occurred in database "test", table "dbo.Orders", column 'Tax'.

所以我想说-检查约束 IS 工作得很好...

So I'd say - that check constraint IS working just fine ...

更新:

如果您坚持使用困难的方法-使用(而不是笨拙的)视觉设​​计器-那么您需要在此处定义检查约束:

if you insist on doing this the hard way - using the (rather crappy) visual designer - then you need to define the check constraint here:

一旦执行了此操作，然后转到SQL Server Management Studio中的Edit top 200 rows输入数据，然后输入违反检查约束的内容，就会得到:

Once I do that, and then I go to Edit top 200 rows in SQL Server Management Studio to enter data, and I enter something that violates the check constraint, I get:

如果这不适用于您的客户端应用程序-那么您很可能会遇到客户端应用程序问题-而不是SQL Server中的CHECK约束问题！

If that doesn't work for you from your client application - then you most likely have a problem with the client app - and not with the CHECK constraint in SQL Server!

这篇关于在SQL Server 2008中检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！