Define column constraints

To ensure that the data in a table conforms to your business requirements, you can define constraints at the column level.

Constraints limit the types of data that can be stored in columns. If you attempt to enter or update a value that does not satisfy the constraint on the corresponding column, an error is returned and the operation is rolled back. Likewise, if you attempt to add a constraint that conflicts with the existing data to a column in an existing table, an error is returned and the operation is rolled back.

Types of constraints

MySQL tenants of OceanBase Database support the NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.

NOT NULL

A NOT NULL constraint requires that the constrained column do not contain a NULL value.

For a column with the NOT NULL constraint, you must specify the values in the INSERT statement unless you have defined a non-null default value for the column.

The following sample code creates a table named tbl1 and specifies the NOT NULL constraint for the col1 column.

obclient> CREATE TABLE tbl1(col1 INT NOT NULL,col2 INT);

UNIQUE

A UNIQUE constraint requires that the constrained column do not contain duplicate values. However, it can contain multiple NULL values.

The following sample code creates a table named tbl6 and specifies the UNIQUE constraint for the col2 column.

obclient> CREATE TABLE tbl6(col1 INT UNIQUE,col2 INT);

PRIMARY KEY

A PRIMARY KEY constraint is the combination of the NOT NULL constraint and the UNIQUE constraint.

The following sample code 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 the example, the tbl2 table contains a primary key column col1. This column must contain unique values and must not contain NULL values.

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: If the referenced column of the parent table is dropped or updated, the DROP or UPDATE operation on the child table is rejected.

• CASCADE: If the referenced column of the parent table is dropped or updated, the corresponding constrained column of the child table is automatically dropped or updated.

  To specify the CASCADE keyword, make sure that the following conditions are met:

  • The FOREIGN KEY constraint is not defined on a generated column.

  • The FOREIGN KEY constraint is not defined on the base column of a generated column.

• NO ACTION: This keyword is equivalent to RESTRICT. If the referenced column of the parent table is dropped or updated, the DROP or UPDATE operation on the child table is rejected.

  By default, if you do not specify the ON DELETE or ON UPDATE clause when you create a FOREIGN KEY constraint, the NO ACTION keyword is used.

• SET NULL: If the referenced column of the parent table is dropped or updated, values of the corresponding constrained column of the child table are set to NULL.

  To specify the SET NULL keyword, make sure that the following conditions are met:

  • The column to be constrained by the FOREIGN KEY constraint is not constrained by a NOT NULL constraint.

  • The FOREIGN KEY constraint is not defined on a generated column.

  • The FOREIGN KEY constraint is not defined on the base column of a generated column.

The following sample code creates a table named tbl4 and specifies to associate the col2 column with the primary key column col1 of a table named tbl3.

obclient> CREATE TABLE tbl4(col1 INT,col2 INT,FOREIGN KEY(col2) REFERENCES tbl2(col1) ON UPDATE CASCADE ON DELETE RESTRICT);

In this example, if the col1 column of the parent table tbl3 is updated, the constrained column of the child table is automatically updated. If the col1 column of the parent table tbl3 is deleted, the DELETE operation on the constrained column of the child table 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 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

A CHECK constraint requires that the values of the constrained column must 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.

Example: Create a table named tbl8 and set a constraint where 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 table name_OBCHECK_creation timestamp format, such as t1_OBCHECK_1629350823880271.

For more information about the syntax for creating constraints, see CREATE TABLE.

View constraints

You can check the constraints that you created by querying the information_schema.TABLE_CONSTRAINTS view. Here is an 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

You can manage constraints in OceanBase Database.

Manage PRIMARY KEY constraints

You can add a PRIMARY KEY constraint to a table or drop a PRIMARY KEY constraint from a table.

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;

Here is an example:

1. Create a table named tbl2.

   obclient> CREATE TABLE tbl2(col1 INT,col2 INT);
   

2. Add a PRIMARY KEY constraint to the tbl2 table.

   obclient> ALTER TABLE tbl2 ADD PRIMARY KEY (col1);
   

3. Drop the PRIMARY KEY constraint from the tbl2 table.

   obclient> ALTER TABLE tbl2 DROP PRIMARY KEY;
   

Manage FOREIGN KEY constraints

You can add a FOREIGN KEY constraint to a table or drop a FOREIGN KEY constraint from a table.

Here is an example:

• Create a table named tbl5 and add a FOREIGN KEY constraint to the col2 column in the tbl5 table. The associated primary table is tbl2.

  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 affected
  

• Drop the FOREIGN KEY constraint on the col1 column from the tbl5 table.

  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

You can add UNIQUE constraints to a table.

Example: Create a table named tbl7 and add a UNIQUE constraint to the col2 column in 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

You can change the attribute of a column in a table to NOT NULL.

Example: Create a table named tbl9 and change the attribute of a column in the table 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

Manage CHECK constraints

You can add a CHECK constraint to a table and modify or drop a CHECK constraint from a table.

Here is an example:

• Add a CHECK constraint to the col2 column in the tbl8 table.

  obclient> ALTER TABLE tbl8 ADD CONSTRAINT tbl8_c1 CHECK(col2>0);
  

• Modify the CHECK constraint for the table.

  After you create a CHECK constraint, it is enabled by default. If you want to invalidate the constraint, you can disable it.

  • Disable a CHECK constraint.

    obclient> ALTER TABLE tbl8 ALTER CHECK tbl8_c1 NOT ENFORCED;
    

  • Enable a CHECK constraint.

    obclient> ALTER TABLE tbl8 ALTER CHECK tbl8_c1 ENFORCED;
    

• Drop the CHECK constraint.

  obclient> ALTER TABLE tbl8 DROP CHECK tbl8_c1;
  

For more information about the syntax for modifying constraints, see ALTER TABLE.

Default setting for a date or time column

We recommend that you specify a default value for a column with the NOT NULL constraint. For a date or time column, you can set the current date or time of the database as the default value.

• Example: Call the current_timestamp function to set the default value for the datetime column of a table.

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.