Menu
This is probably a simple answer but I can't find it. I have a table with a column of integers and I want to ensure that when a row is inserted that the value in this column is greater than zero. I could do this on the code side but thought it would be best to enforce it on the table.
Thanks!
I was in error with my last comment all is good now.
758
If you want to limit the range of an integer column you can use a check constraint: create table some_table ( phone_number integer not null check (phone_number between 0 and 9999999999) );
You can use the > operator in SQL to test for an expression greater than. In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than 6000. A customer_id equal to 6000 would not be included in the result set.
SQL Greater than or equal to ( >= ) operator The greater than equal to operator is used to test whether an expression (or number) is either greater than or equal to another one.
You can use a check constraint on the column. IIRC the syntax for this looks like:
create table foo (
[...]
,Foobar int not null check (Foobar > 0)
[...]
)
As the poster below says (thanks Constantin), you should create the check constraint outside the table definition and give it a meaningful name so it is obvious which column it applies to.
alter table foo
add constraint Foobar_NonNegative
check (Foobar > 0)
You can get out the text of check constraints from the system data dictionary in sys.check_constraints:
select name
,description
from sys.check_constraints
where name = 'Foobar_NonNegative'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
