SQL constraint minvalue / maxvalue?

SqlConstraints

Sql Problem Overview

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?

Sql Solutions

Solution 1 - Sql

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),
    ...
)

Solution 2 - Sql

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.

Solution 3 - Sql

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)
)

Solution 4 - Sql

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);

Solution 5 - Sql

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.

Categories
Recommended Sql Solutions
Recommended Constraints Solutions

Previous Solution:

Opening files in the same folder as the current file, in vim

UnixVimVi

Next Solution:

Multiple Where clauses in Lambda expressions

C#.NetLambda

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionShimmy WeitzhandlerView Question on Stackoverflow
Solution 1 - SqlyfeldblumView Answer on Stackoverflow
Solution 2 - SqlJonathan LefflerView Answer on Stackoverflow
Solution 3 - SqlsteveschoonView Answer on Stackoverflow
Solution 4 - SqlGabriel Borges OliveiraView Answer on Stackoverflow
Solution 5 - SqlFry SimpsonView Answer on Stackoverflow