Is there a way to set a SQL constraint for a numeric field that min value should be 1234 and max value should be 4523?
asked Nov 15, 2009 at 5:02
Shimmy Weitzhandler
105k126 gold badges437 silver badges645 bronze badges
Add a comment
|
5 Answers
SQL Server syntax for the check constraint:
create table numbers (
number int not null
check(number >= 1234 and number <= 4523),
...
)
create table numbers (
number int not null,
check(number >= 1234 and number <= 4523),
...
)
create table numbers (
number int not null,
constraint number_range_check
check(number >= 1234 and number <= 4523),
...
)
3 Comments
Shimmy Weitzhandler
The link bellow really helped me: technet.microsoft.com/en-us/library/ms179491.aspx.
madCode
Why would you check for Not Null, when there is a constraint that indirectly takes care of it? Am I missing something?
CREATE TABLE WhatEver
(
...
NumericField INTEGER NOT NULL CHECK(NumericField BETWEEN 1234 AND 4523),
...
);
Note that 'BETWEEN AND' provides a range inclusive of the quoted limit values.
answered Nov 15, 2009 at 5:08
Jonathan Leffler
759k145 gold badges961 silver badges1.3k bronze badges
Comments
If you are using SQL Server, you want to use a CHECK constraint like this:
CREATE TABLE foo (
someint INT NOT NULL CHECK (someint >= 1234 AND someint <= 4523)
)
Comments
FYI
When you need a constraint for a range of values:
ALTER TABLE package_subscription ADD CONSTRAINT check_discount_amount CHECK (discount_amount BETWEEN 0.0000 AND 1.0000);
1 Comment
Jak
Are those constraints inclusive or exclusive?
If you are using SQL Server by means of SQL Server Management Studio, the most convenient way to add a Check Constraint is to right click the Constraints folder in the tree view (Object Explorer) and then, from the popup menu, select New Constraint.
A Check Constraint windows pops up with a new empty constraint named CK_tableName*
You can edit such a proposed name, and insert the code of the check constraint in the Expression field.
Then the new constraint appears in the Constraint folder (after you select the folder and hit the refresh icon) in Object Explorer and you can edit it right clicking it and selecting Modify from the popup menu.
