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

OceanBase Database supports the following types of constraints:

• NOT NULL constraint: requires that the constrained column must not contain NULL values.

  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.

  For example, create a table named tbl1 and specify the NOT NULL constraint for the col1 column.

  obclient> CREATE TABLE tbl1(col1 INT NOT NULL,col2 INT);
  Query OK, 0 rows affected
  
  obclient> DESC tbl1;
  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | col1  | int(11) | NO   |     | NULL    |       |
  | col2  | int(11) | YES  |     | NULL    |       |
  +-------+---------+------+-----+---------+-------+
  2 rows in set
  

• UNIQUE constraint: requires that the constrained column must not contain duplicate values. NULL values are not prohibited by this constraint type.

  For example, create a table named tbl6 and specify the UNIQUE constraint for the col2 column.

  obclient> CREATE TABLE tbl6(col1 INT UNIQUE,col2 INT);
  Query OK, 0 rows affected
  

• PRIMARY KEY constraint: It is the combination of the NOT NULL constraint and the UNIQUE constraint.

  For example, create a table named tbl2 and specify the PRIMARY KEY constraint for the col1 column.

  obclient> CREATE TABLE tbl2(col1 INT PRIMARY KEY,col2 INT);
  Query OK, 0 rows affected
  
  obclient> DESC tbl2;
  +-------+---------+------+-----+---------+-------+
  | Field | Type    | Null | Key | Default | Extra |
  +-------+---------+------+-----+---------+-------+
  | col1  | int(11) | NO   | PRI | NULL    |       |
  | col2  | int(11) | YES  |     | NULL    |       |
  +-------+---------+------+-----+---------+-------+
  2 rows in set
  

  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 constraint: requires that the constrained column must contain only values from the primary key column of another table.

  For example, create a FOREIGN KEY constraint.

  obclient> CREATE TABLE tbl4(col1 INT,col2 INT,FOREIGN KEY(col2) REFERENCES tbl2(col1));
  Query OK, 0 rows affected
  

  In the example, the col2 column in the tbl4 table is associated with the primary key column col1 in the tbl2 table.

  If you do not specify the foreign key name when you create a FOREIGN KEY constraint, the system automatically assigns a constraint name in the table name_OBFK_creation timestamp format, such as t1_OBFK_1627747200000000.

  By default, OceanBase Database checks foreign keys. To disable or enable foreign key check, modify the foreign_key_checks tenant variable.

  For more information about the foreign_key_checks variable, see foreign_key_checks.

• CHECK constraint: enforces that the values of a column in the table must conform to 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.

  For 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.

For 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.

For 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.

• For 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.