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 :) ).
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))
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???
Please help
EDIT: Here is the create script off my table
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)
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)
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'.
So I'd say - that check constraint IS working just fine ...
Update:
if you insist on doing this the hard way - using the (rather crappy) visual designer - then you need to define the check constraint here:
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:
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!