How do I create a table with a minimum and maximum value condition in an SQL server?

 To create a table in SQL Server with constraints that enforce a minimum and maximum value for a column, you can use CHECK constraints. A CHECK constraint ensures that the values entered into a column meet a specific condition. For a minimum and maximum value condition, you would define the condition within the CHECK constraint.

Here’s an example of how to create a table with a column that has both a minimum and maximum value constraint:

Example:

Let's say we want to create a table for storing employee ages, and we want to ensure that the age is between 18 and 100.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Age INT,
    CONSTRAINT CHK_AgeRange CHECK (Age BETWEEN 18 AND 100)
);

Breakdown:

1. EmployeeID: An integer column to uniquely identify each employee (Primary Key).
2. Name: A VARCHAR column to store the employee’s name.
3. Age: An integer column to store the age.
4. CHK_AgeRange: The CHECK constraint that enforces the condition Age BETWEEN 18 AND 100.

Explanation:

• CHECK (Age BETWEEN 18 AND 100): This ensures that the value of the Age column must always be between 18 and 100, inclusive. Any attempt to insert or update a row with an age outside this range will result in an error.

Inserting Data:

If you try to insert an age outside the valid range, SQL Server will reject the insertion:

-- Valid insert (Age 25 is between 18 and 100)
INSERT INTO Employees (EmployeeID, Name, Age)
VALUES (1, 'John Doe', 25);

-- Invalid insert (Age 120 is outside the allowed range)
INSERT INTO Employees (EmployeeID, Name, Age)
VALUES (2, 'Jane Smith', 120);  -- This will result in an error

Notes:

• The CHECK constraint is applied to individual columns or combinations of columns.

• You can add CHECK constraints to existing tables using ALTER TABLE:

  ALTER TABLE Employees
  ADD CONSTRAINT CHK_AgeRange CHECK (Age BETWEEN 18 AND 100);
  

By using CHECK constraints in SQL Server, you can ensure that the values in your tables comply with the defined minimum and maximum range.