About types of column constraints

This topic describes how to use different types of constraints in OceanBase Database in MySQL mode.

Prerequisites

The current user has the CREATE and ALTER privileges on the target table.

Constraint types

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

The constraints limit the type of values that can be stored in a column. 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.

The types of constraints are described as follows:

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

• UNIQUE constraint: enforces that the constrained column must not contain duplicate values, but it can contain multiple null values.

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

• FOREIGN KEY constraint: enforces that the constrained column must contain only values from the primary key column of another table.

• 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 delete a table, the CHECK constraints applied to the table are also deleted.

• DEFAULT constraints: inserts default values to columns. If no other values are specified, the default values will be added to all new records.

Note

• By default, the FOREIGN KEY constraint is enabled. You can set the foreign_key_checks variable to disable this constraint.
• OceanBase Database allows you to change DEFAULT NULL to DEFAULT 'var' NOT NULL.

Example 1

• The NOT NULL constraint on the w_name column in the ware table requires that each warehouse must have a name.

  obclient> CREATE TABLE ware(
              id int,
              w_name varchar(256) not null,
              PRIMARY KEY (id)
           ) ;
  Query OK, 0 rows affected
  
  obclient> DESC ware;
  +--------+--------------+------+-----+---------+-------+
  | Field  | Type         | Null | Key | Default | Extra |
  +--------+--------------+------+-----+---------+-------+
  | id     | int(11)      | NO   | PRI | NULL    |       |
  | w_name | varchar(256) | NO   |     | NULL    |       |
  +--------+--------------+------+-----+---------+-------+
  2 rows in set
  

• For a column with the NOT NULL constraint, you must specify the value in an INSERT statement unless you have defined a default value for the column.

  obclient> INSERT INTO ware(id) values(1);
  ERROR 1364 (HY000): Field 'w_name' doesn't have a default value
  

• For example, if you define a default value 0.99 for the c_discount column in the cust table, the default discounted price is 99% of the original price.

  obclient> CREATE TABLE cust(          
              id int,          
              c_discount varchar(256) default '0.99',          
              PRIMARY KEY (id)          
             );
  Query OK, 0 rows affected
  
  obclient> DESC cust;
  +------------+--------------+------+-----+---------+-------+
  | Field      | Type         | Null | Key | Default | Extra |
  +------------+--------------+------+-----+---------+-------+
  | id         | int(11)      | NO   | PRI | NULL    |       |
  | c_discount | varchar(256) | YES  |     | 0.99    |       |
  +------------+--------------+------+-----+---------+-------+
  2 rows in set
  

• Example of a CHECK constraint: Create table tbl1 and set a constraint that the value of column col1 must be greater than 0.

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

Example 2

• The UNIQUE constraint on the w_name and w_city columns in the ware table requires that the warehouse names in each city must be unique.

  obclient> CREATE TABLE ware(
              w_id int,
              w_name varchar(256) unique,
              w_city varchar(256) unique,
              PRIMARY KEY (w_id)
                  ) ;
  Query OK, 0 rows affected
  
  obclient> DESC ware;
  +--------+--------------+------+-----+---------+-------+
  | Field  | Type         | Null | Key | Default | Extra |
  +--------+--------------+------+-----+---------+-------+
  | w_id   | int(11)      | NO   | PRI | NULL    |       |
  | w_name | varchar(256) | YES  | UNI | NULL    |       |
  | w_city | varchar(256) | YES  | UNI | NULL    |       |
  +--------+--------------+------+-----+---------+-------+
  3 rows in set
  

• If the w_name and w_city columns in the ware table violate the UNIQUE constraint, an error is reported.

  obclient> SELECT * FROM ware;
  +------+--------+--------+
  | w_id | w_name | w_city |
  +------+--------+--------+
  |    1 | ny     | LA     |
  +------+--------+--------+
  1 row in set
  
  obclient> INSERT INTO ware VALUES (2,'ny','LA');
  ERROR 1062 (23000): Duplicate entry 'ny' for key 'w_name'
  

Example 3

The ware table has a primary key column w_id, and this column must contain unique values and must not contain null values.

• If the primary key column contains null values, an error is reported.

  obclient> INSERT INTO ware(w_name,w_city) VALUES ('ny', 'LA');
  ERROR 1364 (HY000): Field 'w_id' doesn't have a default value
  

• If the primary key column contains duplicate values, an error is reported.

  obclient> SELECT * FROM ware;
  +------+--------+--------+
  | w_id | w_name | w_city |
  +------+--------+--------+
  |    1 | ny     | LA     |
  +------+--------+--------+
  1 row in set
  
  obclient> INSERT INTO ware VALUES (1,'zs','EH');
  ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
  

  Note

  In MySQL mode, the NOT NULL constraint is enabled for the primary key column by default.

Example 4

The FOREIGN KEY constraint on the c_w_id column of the cust table references the w_id column of the ware table. This means the warehouses allocated to the customers must reside in the ware table.

obclient> ALTER TABLE cust ADD CONSTRAINT ware FOREIGN KEY (c_w_id) REFERENCES ware(w_id);
Query OK, 0 rows affected

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.

Examples

Call the current_timestamp function to set a default value for each datetime column in a table.

1. Create a table named t1 and define the current_timestamp function.

   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
   

2. Insert data into the t1 table.

   obclient> insert into t1(id) values(1),(2),(3);
   Query OK, 3 rows affected
   Records: 3  Duplicates: 0  Warnings: 0
   

3. View the t1 table.

   obclient> select * from t1;
   +----+---------------------+---------------------+
   | id | gmt_create          | gmt_modified        |
   +----+---------------------+---------------------+
   |  1 | 2022-03-16 14:23:10 | 2022-03-16 14:23:10 |
   |  2 | 2022-03-16 14:23:10 | 2022-03-16 14:23:10 |
   |  3 | 2022-03-16 14:23:10 | 2022-03-16 14:23:10 |
   +----+---------------------+---------------------+
   3 rows in set