Create a CHECK Constraint to allow specific values
This is a quick example for CHECK constraints. Let's say you've got an Orders table with a Side column ('side' is trading speak, it just means Buy or Sell). The column is CHAR(1), and you want to allow ONLY 'B' or 'S' to be inserted. This means CHECK constraint. CHECK constraints are used to limit the values that can be written to a column. In this example, we'll create the constraint on the Side column, and prevent anything other than 'B' or 'S' from going into the table.
/* Create the table */
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[TraderID] [int] NOT NULL,
[Side] [char](1) NOT NULL,
[Quantity] [bigint] NOT NULL,
CONSTRAINT [upkcl_Orders_OrderID] 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 [MyDatabase_Data]
) ON [MyDatabase_Data]
GO
/* Add your CHECK constraint */
ALTER TABLE [dbo].[Orders] WITH CHECK
ADD CONSTRAINT [ck_Orders_Side] CHECK (([Side]=('B') OR [Side]=('S')))
GOALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [ck_Orders_Side]
GO
/* Test it out */
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'B',25000)
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'S',250)
/* See everything looks good */
SELECT * FROM dbo.Orders
/* Write something to violate the constraint */
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'X',25000)
!!!ERROR!!!!
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "ck_Orders_Side". The conflict occurred in database "MyDatabase", table "dbo.Orders", column 'Side'.
The statement has been terminated.
As you can see, the X got caught by our CHECK constraint... exactly as we intended. Again, just a quick example for CHECK constraints. See this for more information:
http://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx
/* Create the table */
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOCREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[TraderID] [int] NOT NULL,
[Side] [char](1) NOT NULL,
[Quantity] [bigint] NOT NULL,
CONSTRAINT [upkcl_Orders_OrderID] 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 [MyDatabase_Data]
) ON [MyDatabase_Data]
GO
/* Add your CHECK constraint */
ALTER TABLE [dbo].[Orders] WITH CHECK
ADD CONSTRAINT [ck_Orders_Side] CHECK (([Side]=('B') OR [Side]=('S')))
GOALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [ck_Orders_Side]
GO
/* Test it out */
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'B',25000)
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'S',250)
/* See everything looks good */
SELECT * FROM dbo.Orders
/* Write something to violate the constraint */
INSERT dbo.Orders (TraderID,Side,Quantity)
VALUES (1,'X',25000)
!!!ERROR!!!!
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "ck_Orders_Side". The conflict occurred in database "MyDatabase", table "dbo.Orders", column 'Side'.
The statement has been terminated.
As you can see, the X got caught by our CHECK constraint... exactly as we intended. Again, just a quick example for CHECK constraints. See this for more information:
http://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx