you are at Encyclopedia of SQL >> CHECK
CHECK
This constraint is used to limit value to be set in column. This constraint can be set during creating the table or for existing table with keyword ALTER.Syntax for MySQL
CREATE `table_name`
(
`column_name1` data_type ,
`column_name2` data_type ,
CHECK (`column_name` operator 'value')
)
Syntax for SQL Server / Oracle / MS Access
CREATE `table_name`
(
`column_name1` data_type CHECK (`column_name` operator 'value'),
`column_name2` data_type
)
Syntax for naming and creating constraint
CREATE `table_name`
(
`column_name1` typ_danych ,
`column_name2` typ_danych ,
CONSTRAINT constraint_name CHECK
(`column_name1` operator 'value' AND|OR `column_name2` operator 'value')
)
Giving name for constraint give ability of modification or deleting.
Syntax for creating constraint for existing table
ALTER TABLE `table_name`
ADD CONSTRAINT constraint_name `
CHECK (`column_name1` operator 'value'
AND|OR `column_name2` operator 'value')
Syntax for deleting constraint for existing table
ALTER TABLE `table_name`
DROP CONSTRAINT constraint_name
Example
Create table `people` with columns : ordinal_number which can't be negative, name, surname, evidention_id which have to consist of 11 characters.
query
CREATE TABLE `people`
`ordinal_number` int
`name` text
`surname` text
`evidention_id` int
CONSTRAINT `table_constraint` CHECK
( `ordinal_number`>=0 AND LENGTH(`evidention_id`)=11 )
}
Now for existing table from prevoius example delete `table_constraint`
query
ALTER TABLE `people`
DROP CONSTRAINT `table_constraint`
[ wróć na górę strony ]
