Check constraint and Like condition

 Check Constraints

This SQL Server tutorial explains how to use the check constraints in SQL Server (Transact-SQL) with syntax and examples.

Note

• A check constraint can NOT be defined on a SQL View.
• The check constraint defined on a table must refer to only columns in that table. It can not refer to columns in other tables.
• A check constraint can NOT include a Subquery.
• A check constraint can be defined in either a CREATE TABLE statement or a ALTER TABLE statement.

Create Check

CREATE TABLE nremployees1

( employee_id INT NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50),

  salary INT,

  CONSTRAINT NRCHECK

    CHECK (salary > 500)

);

Using  money datatype

CREATE TABLE employees

( employee_id INT NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50),

  salary MONEY,

  CONSTRAINT check_salary

    CHECK (salary > 0)

);

Alter check constraint

ALTER TABLE nrdata

ADD CONSTRAINT checksal CHECK (sal > 500);

 

Drop conatraint

 

ALTER TABLE nrdata

DROP CONSTRAINT checksal;

Disable a Check Constraint

 

ALTER TABLE nrdata

NOCHECK CONSTRAINT checksal;

Enable  a Check Constraint

 

ALTER TABLE nrdata

WITH CHECK CHECK CONSTRAINT checksal;

SQL Server Conditions

AND Condition	2 or more conditions to be met
OR Condition	Any one of the conditions are met
AND and OR	Combine AND and OR conditions
LIKE Condition	Use wildcards in a WHERE clause
IN Condition	Alternative to multiple OR conditions
NOT Condition	Negate a condition
IS NULL Condition	Test for a NULL value
IS NOT NULL Condition	Test for a NOT NULL value
BETWEEN Condition	Retrieve within a range (inclusive)
EXISTS Condition	Condition is met if subquery returns at least one row

SELECT *

FROM employees

WHERE last_name LIKE 'B%';

 

SELECT *

FROM employees

WHERE last_name LIKE '%o%';

 

SELECT *

FROM employees

WHERE first_name LIKE 'Ad_m';

This SQL Server LIKE condition example would return all employees whose first_name is 4 characters long, where the first two characters is 'Ad' and the last character is 'm'. For example, it could return employees whose first_name is 'Adam', 'Adem', 'Adim', 'Adom', 'Adum', etc.

 

SELECT *

FROM employees

WHERE employee_number LIKE '123_';

You might find that you are looking for an employee_number, but you only have 3 of the 4 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return employees whose employee numbers are:

1230, 1231, 1232, 1233, 1234, 1235, 1236, 1237, 1238, 1239