在SQL中检查约束-用MySQL和SQL Server语法示例解释

The CHECK constraint is used to limit the value range that can be placed in a column.

CHECK约束用于限制可以放置在列中的值范围。

If you define a CHECK constraint on a single column it allows only certain values for this column.

如果在单个列上定义CHECK约束，则该列仅允许某些值。

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.

如果在表上定义CHECK约束，则可以基于行中其他列中的值来限制某些列中的值。

在CREATE TABLE上进行SQL检查 (SQL CHECK on CREATE TABLE)

The following SQL creates a CHECK constraint on the “Age” column when the “Persons” table is created. The CHECK constraint ensures that you can not have any person below 18 years:

创建“人员”表时，以下SQL在“年龄”列上创建CHECK约束。 CHECK约束确保您不能有18岁以下的任何人：

MySQL:

MySQL：

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

SQL Server / Oracle / MS Access:

SQL Server / Oracle / MS Access：

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age>=18)
);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

若要命名CHECK约束，并在多个列上定义CHECK约束，请使用以下SQL语法：

MySQL / SQL Server / Oracle / MS Access:

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255),
    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);

对ALTER TABLE进行SQL检查 (SQL CHECK on ALTER TABLE)

To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:

要在表已创建时在“年龄”列上创建CHECK约束，请使用以下SQL：

MySQL / SQL Server / Oracle / MS Access:

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CHECK (Age>=18);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

若要命名CHECK约束，并在多个列上定义CHECK约束，请使用以下SQL语法：

MySQL / SQL Server / Oracle / MS Access:

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');

放弃检查约束 (DROP a CHECK Constraint)

To drop a CHECK constraint, use the following SQL:

要删除CHECK约束，请使用以下SQL：

SQL Server / Oracle / MS Access:

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;

MySQL:

MySQL：

ALTER TABLE Persons
DROP CHECK CHK_PersonAge;

翻译自: https://www.freecodecamp.org/news/check-constraint-sql-server-mysql/