SQL Advanced Application-constraint three-(CHECK, DEFAULT)

SQL CHECK constraints

CHECK constraints are used to limit the range of values in a column

If a CHECK constraint is defined on a single column, the column only allows a specific value

If a CHECK constraint is defined on a table, the secondary constraint limits the value in a specific column

The following SQL creates a check constraint for a column when the table is created

The following SQL creates a CHECK constraint for the "id_p" column when the "Persons" table is created. The CHECK constraint stipulates that the "id_p" column must contain only integers greater than 0

Mysql

CREATE TABLE Persons (    id_p int not NULL,    LastName varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255), City    varchar (255),    CHECK (id_p>0))

SQL Server/oracle/ms Access

CREATE TABLE Persons (    id_p int not null CHECK (id_p>0),    LastName varchar (255) is not NULL,    FirstName varchar ( 255),    Address varchar (255), City    varchar (255))

　　

If you need to name a check constraint and define a CHECK constraint for more than one column, use the following SQL syntax

Mysql/sql Server/oracle/ms Access

CREATE TABLE Persons (    id_p int not NULL,    LastName varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255), City    varchar (255),    CONSTRAINT Chk_person CHECK (id_p>0 and city= ' Sandnes ')

　　

If a check constraint is created for a column if the table already exists

If you create a check constraint for the "id_p" column if the table already exists, use the following SQL

Mysql/sql Server/oracle/ms Access

ALTER TABLE personsadd CHECK (id_p>0)

　　

If you need to name a check constraint and define a CHECK constraint for more than one column, use the following SQL

Mysql/sql Server/oracle/ms Access

ALTER TABLE personsadd CONSTRAINT chk_person chekc (id_p>0 and city= ' Sandnes ')

　　

Revoke a CHECK constraint

If you need to revoke a check constraint, use the following SQL

SQL Server/oracle/ms Access

ALTER TABLE Personsdrop CONSTRAINT Chk_person

Mysql

ALTER TABLE Personsdrop CHECK Chk_person

　　

SQL DEFAULT constraints

Default constraints are used to insert defaults into columns

If no other value is specified, the default value is added to all new records

Create a DEFAULT constraint for a column when the table is created

The following SQL creates a default constraint for the "City" column when the "Persons" table is created:

Mysql/sql Server/oracle/ms Access

CREATE TABLE Persons (    id_p int not NULL,    LastName varchar (255) is not NULL,    FirstName varchar (255),    Address varchar (255), City    varchar (255) DEFAULT ' Sandnes ')

By using a function like GETDATE (), the default constraint can also be used to insert system values

CREATE TABLE Orders (    id_o int not NULL,    OrderNo int. NOT NULL,    id_p int,    OrderDate date DEFAULT GETDATE ())

　　

Create a DEFAULT constraint for a column if the table already exists

If you create a default constraint for the city column if the table already exists, use the following sql:

Mysql

ALTER TABLE personsalter City SET DEFAULT ' Sandnes '

SQL Server/oracle/ms Access

ALTER TABLE personsalter COLUMN city SET DEFAULT ' Sandnes '

　　

Undo Default Constraint

To revoke the DEFAULT constraint, use the following SQL

Mysql

ALTER TABLE personsalter City DROP DEFAULT

SQL Server/oracle/ms Access

ALTER TABLE personsalter COLUMN City DROP DEFAULT

　　

SQL Advanced Application-constraint three-(CHECK, DEFAULT)