check constraint

check constraint.sql

--► The CHECK constraint is used to limit the value range that can be placed in a column.
--► If you define a CHECK constraint on a single column it allows only certain values for this column.
--► If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns 
--  in the row.
--MySQL:
CREATE TABLE Persons
(
  P_Id int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  CHECK (P_Id>0)
)
--SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
  P_Id int NOT NULL CHECK (P_Id>0),
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255)
)
-- or
CREATE TABLE Persons
(
  P_Id int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255),
  Address varchar(255),
  City varchar(255),
  CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

--alter (MySQL / SQL Server / Oracle / MS Access:)
ALTER TABLE Persons ADD CHECK (P_Id>0)
--or
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

--To DROP a CHECK Constraint
--SQL Server / Oracle / MS Access:
ALTER TABLE Persons DROP CONSTRAINT chk_Person

--MySQL:
ALTER TABLE Persons DROP CHECK chk_Person