How to limit the value range that can be placed in a column?

user_10804280 0
Viewed 822

I am a beginner in SQL, I have a table with GroupRole column and Age column.

    CREATE TABLE [Persons](
        [PersonID] [int] IDENTITY(1,1) NOT NULL,
        [FullName]   [varchar](70) NULL,
        [Age] [int] NULL,
        [GroupRole ] [varchar](30) NULL

 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
    [PersonID] 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

I want to limit the value range that can be placed in an age column lower 30 and GroupRole column equal 'Admin'.

I do not want to do this in the c# code.

How can I do it?

sql-server database
2 Answers

this have easy way:

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=30 AND GroupRole ='Admin');
user_17327260

You need to use Check Constraints. They are part of your table's metadata and define conditions to be checked every time, when the data is modified or inserted in the table. If these conditions are not met, the DML operation will fail with an error.

You need to modify your CREATE TABLE statement as follows:

CREATE TABLE [Persons](
    [PersonID] [int] IDENTITY(1,1) NOT NULL,
    [FullName]   [varchar](70) NULL,
    [Age] [int] NULL CONSTRAINT CHK_Age CHECK ([Age] < 30),
    [GroupRole] [varchar](30) NULL CONSTRAINT CHK_GroupRole CHECK ([GroupRole] in ('Admin'))

CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
    [PersonID] 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
user_86493930
Related