SQL CHECK Constraint
SQL CHECK Constraint
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.
SQL CHECK Constraint on CREATE TABLE
The following SQL creates a CHECK constraint on the "P_Id" column when
the "Persons" table is created. The CHECK constraint specifies that the column "P_Id"
must only include integers greater than 0.
My SQL:
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)
)
|
To allow naming of a CHECK constraint, and for defining a CHECK constraint
on multiple columns, use the
following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
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')
)
|
SQL CHECK Constraint on ALTER TABLE
To create a CHECK constraint on the "P_Id" column when the
table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK (P_Id>0)
|
To allow naming of a CHECK constraint, and for defining a CHECK constraint
on multiple columns, use the
following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
|
To DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
|
The Altova MissionKit is a suite of intelligent XML tools, including:
XMLSpy® – industry-leading XML editor
- Support for all XML-based technologies
- Graphical editing views, powerful debuggers, code generation, & more
MapForce® – graphical data mapping tool
- Drag-and-drop data conversion with code generation
- Support for XML, DBs, EDI, Excel® 2007, text, Web services
StyleVision® – visual stylesheet designer
- Drag-and-drop stylesheet design for XML & databases
- Output to HTML, PDF, RTF, Word 2007, & more
And more…
Try before you buy with a free fully functional 30-day trial
Download today
 |
|
Get Your Diploma!
W3Schools' Online Certification Program is the perfect solution for busy
professionals who need to balance work, family, and career building.
The HTML Certificate is for developers who want to document their knowledge of HTML, XHTML, and CSS.
The JavaScript Certificate is for developers who want to document their knowledge of JavaScript and the HTML DOM.
The ASP Certificate is for developers who want to document their knowledge of ASP, SQL, and ADO.
|
|
HOME
Printer friendly page
