To ensure that the data in a table complies with business rules, you can define constraints on columns.
Constraints, defined on columns, restrict the values that can be stored in those columns. When you attempt to write or update a value in a column that violates the constraint, an error is triggered and the operation is rolled back. Similarly, when you attempt to add a constraint that conflicts with existing data on a column of an existing table, an error is triggered and the operation is rolled back.
Constraint types
OceanBase AI Database supports the NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.
NOT NULL constraint (NOT NULL)
The NOT NULL constraint prohibits a NULL value in the specified column.
For a column with a NOT NULL constraint, you must specify a value for that column in an INSERT statement, unless the column also has a non-NULL default value.
The following example creates table tbl1 and sets column col1 to be NOT NULL:
obclient> CREATE TABLE tbl1(col1 INT NOT NULL,col2 INT);
UNIQUE constraint (UNIQUE)
The UNIQUE constraint prohibits duplicate values in the specified column. Multiple NULL values are allowed.
The following example creates a table named tbl6 and defines a UNIQUE constraint on the col1 column:
obclient> CREATE TABLE tbl6(col1 INT UNIQUE,col2 INT);
PRIMARY KEY constraint (PRIMARY KEY)
The PRIMARY KEY constraint is a combination of the NOT NULL and UNIQUE constraints.
The following example creates a table named tbl2 and specifies the PRIMARY KEY constraint for the col1 column:
obclient> CREATE TABLE tbl2(col1 INT PRIMARY KEY,col2 INT);
In this example, table tbl2 has a primary key column col1, which cannot be NULL and must be unique.
FOREIGN KEY constraint (FOREIGN KEY)
Values in a column constrained by a FOREIGN KEY constraint come from a primary key column of another table, which is referred to as a parent table.
When you create a FOREIGN KEY constraint, you can use the following keywords to specify the cascaded operation on the constrained column of the child table when a DELETE or UPDATE operation is performed on the referenced column of the parent table:
RESTRICT: Indicates that the DELETE or UPDATE operation on the parent table will be rejected when the referenced column in the parent table is deleted or updated.CASCADE: Indicates that when the referenced column in the parent table is deleted or updated, the corresponding row's foreign key column in the child table will be automatically deleted or updated.To specify the cascade action for a foreign key column as
CASCADE, the following conditions must be met:The foreign key cannot be defined on a stored-generated column.
The foreign key cannot be defined on a base column of a generated column.
NO ACTION: Equivalent toRESTRICT. Indicates that the DELETE or UPDATE operation on the parent table will be rejected when the referenced column in the parent table is deleted or updated.When creating a foreign key, if the
ON DELETEorON UPDATEclause is not specified, the default action is alwaysNO ACTION.SET NULL: Indicates that when the referenced column of the foreign key in the parent table is deleted or updated, the value of the foreign key column in the corresponding row of the child table is set toNULL.When specifying the cascade operation for a foreign key column as
SET NULL, the following conditions must also be met:The foreign key column cannot be defined as
NOT NULL.The foreign key cannot be defined on a stored-generated column.
The foreign key cannot be defined on a base column of a generated column.
For example, create table tbl4 and specify column col2 to reference the primary key column col1 of table tbl2.
obclient> CREATE TABLE tbl4(col1 INT,col2 INT,FOREIGN KEY(col2) REFERENCES tbl2(col1) ON UPDATE CASCADE ON DELETE RESTRICT);
In this example, when updating column col1 in the parent table tbl2, the foreign key column in the corresponding row of the child table is automatically updated. When deleting column col1 in the parent table tbl2, the operation is rejected.
If you do not specify the constraint name when you create a FOREIGN KEY constraint, the system automatically assigns a constraint name in the format of table_name_OBFK_creation_timestamp, such as t1_OBFK_1627747200000000.
By default, OceanBase AI Database checks foreign keys. To disable or enable foreign key check, modify the tenant variable foreign_key_checks.
For more information about the foreign_key_checks variable, see foreign_key_checks.
CHECK constraint
A CHECK constraint requires that the values in a specific column of the database meet specified conditions.
You can define one or more CHECK constraints for a single column so that only specified values are allowed for the column. You can also define table-level CHECK constraints to apply the constraints to multiple columns. When you modify a table name, the CHECK constraint name remains unchanged. When you drop a table, the CHECK constraints applied to the table are also dropped.
The following example creates a table named tbl8 and sets a constraint that the value of the col1 column must be greater than 0:
obclient> CREATE TABLE tbl8(col1 INT CHECK(col1>0),col2 INT);
Query OK, 0 rows affected
If you do not specify the constraint name when you create a CHECK constraint, the system automatically assigns a constraint name in the format of table_name_OBCHECK_creation_timestamp, such as t1_OBCHECK_1629350823880271.
For more information about the syntax for creating constraints, see CREATE TABLE.
View constraints
After a constraint is successfully created, you can view the constraints of a table using the information_schema.TABLE_CONSTRAINTS view. Example:
obclient> SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='tbl6';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def | test | col1 | test | tbl6 | UNIQUE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
1 row in set
Manage constraints
OceanBase AI Database supports the following management operations for constraints:
Manage primary key constraints
OceanBase AI Database supports adding and modifying primary key constraints on existing tables.
The SQL syntax for adding a primary key constraint is as follows:
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
The SQL syntax for dropping a primary key constraint is as follows:
ALTER TABLE table_name DROP PRIMARY KEY;
Examples:
Create a table named
tbl2.obclient> CREATE TABLE tbl2(col1 INT,col2 INT);Add a primary key to table
tbl2.obclient> ALTER TABLE tbl2 ADD PRIMARY KEY (col1);Drop the primary key from table
tbl2.obclient> ALTER TABLE tbl2 DROP PRIMARY KEY;
Manage foreign key constraints
OceanBase AI Database supports adding and dropping foreign key constraints on existing tables.
Examples:
First, create table
tbl5, then add a foreign key to columncol2of tabletbl5, referencing the primary tabletbl2.obclient> CREATE TABLE tbl5(col1 INT,col2 INT); Query OK, 0 rows affected obclient> ALTER TABLE tbl5 ADD CONSTRAINT tbl5_fk1 FOREIGN KEY(col2) REFERENCES tbl2(col1); Query OK, 0 rows affectedDrop the foreign key from column
col1of tabletbl5.obclient> SELECT TABLE_SCHEMA database_name,TABLE_NAME table_name,COLUMN_NAME column_name,CONSTRAINT_NAME constraint_name FROM information_schema.KEY_COLUMN_USAGE; +--------------+--------+--------+----------------------------+ | Database name. | Table name | Column name | Constraint name | +--------------+--------+--------+----------------------------+ | test | tbl2 | col1 | PRIMARY | | test | tbl3 | col1 | PRIMARY | | test | tbl3 | col2 | PRIMARY | | test | tbl4 | col2 | tbl4_OBFK_1645173651329873 | | test | tbl5 | col1 | tbl5_OBFK_1645174931376110 | | test | tbl5 | col2 | tbl5_fk1 | +--------------+--------+--------+----------------------------+ 6 rows in set obclient> ALTER TABLE tbl5 DROP FOREIGN KEY tbl5_OBFK_1645174931376110; Query OK, 0 rows affected
Manage unique constraints
OceanBase AI Database supports adding unique constraints to existing tables.
The following example shows how to create a table named tbl7 and then add a unique constraint to the col2 column of the tbl7 table:
obclient> CREATE TABLE tbl7(col1 INT,col2 INT);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl7 ADD UNIQUE(col2);
Query OK, 0 rows affected
Manage NOT NULL constraints
OceanBase AI Database supports changing a column's attribute to NOT NULL.
The following example shows how to create a table named tbl9 and then change a column's attribute to NOT NULL:
obclient> CREATE TABLE tbl9(c1 int);
Query OK, 0 rows affected
obclient> ALTER TABLE tbl9 MODIFY c1 int NOT NULL;
Query OK, 0 rows affected
Notice
In OceanBase AI Database, NOT NULL is a column attribute. Therefore, you cannot view the NOT NULL attribute of a column in a table using the information_schema.TABLE_CONSTRAINTS view.
Manage CHECK constraints
OceanBase AI Database supports adding CHECK constraints to existing tables, as well as modifying and dropping CHECK constraints from tables.
Examples:
Add a
CHECKconstraint to thecol2column of thetbl8table.obclient> ALTER TABLE tbl8 ADD CONSTRAINT tbl8_c1 CHECK(col2>0);Modify a table's
CHECKconstraint.By default, a
CHECKconstraint is enabled after it is created. If you want the constraint to be non-effective, you can disable or enable it.Disable a
CHECKconstraint:obclient> ALTER TABLE tbl8 ALTER CHECK tbl8_c1 NOT ENFORCED;Enable a
CHECKconstraint:obclient> ALTER TABLE tbl8 ALTER CHECK tbl8_c1 ENFORCED;
Drop a
CHECKconstraint on a table.obclient> ALTER TABLE tbl8 DROP CHECK tbl8_c1;
For more information about the syntax for modifying constraints, see ALTER TABLE.
Set the default time for a time column
When a column has a NOT NULL constraint, it is generally recommended to set a default value. For date or time columns, you can set the default value to the current database time.
- Example: To set a default value for a time column in a table, you can use the
current_timestampfunction.
obclient> CREATE TABLE t1(
id bigint NOT NULL PRIMARY KEY
, gmt_create datetime NOT NULL default current_timestamp
, gmt_modified datetime NOT NULL default current_timestamp
);
Query OK, 0 rows affected
obclient> INSERT INTO t1(id) VALUES(1),(2),(3);
Query OK, 3 rows affected
obclient> SELECT * FROM t1;
+----+---------------------+---------------------+
| id | gmt_create | gmt_modified |
+----+---------------------+---------------------+
| 1 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
| 2 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
| 3 | 2020-02-27 17:09:23 | 2020-02-27 17:09:23 |
+----+---------------------+---------------------+
3 rows in set
References
For more information about foreign key constraints, see Foreign Key Constraints.