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 in 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.
Constraints can be enabled or disabled. By default, a constraint is enabled after being created.
The types of constraints include:
NOT NULL constraint: enforces that the constrained column must not contain null values.
For example, the NOT NULL constraint in the w_name column in the ware table requires that each warehouse in the column must have a name. For a column with the NOT NULL constraint, you must specify the values in the INSERT statement unless you have defined a default value for the column. For example, if you define a default value 0.99 for the c_discount column in the cust table, the default discount per user is 0.99.
UNIQUE constraint: enforces that the constrained column must not contain duplicate values, but it can contain multiple null values.
For example, the UNIQUE constraint on the (w_name, w_city) column in the ware table enforces that the warehouse names in each city must be unique.
PRIMARY KEY constraint: The combination of the NOT NULL constraint and the UNIQUE constraint.
For example, both the ware table and cust table have primary key columns named w_id and c_id, which do not accept NULL or duplicate values.
FOREIGN KEY constraint: enforces that the constrained column must only contain values from the primary key column of another table.
For example, if a FOREIGN KEY constraint on the c_w_id column of the cust table refers to the w_id column of the ware table, the warehouses allocated to the customers must be warehouses in the ware table.
By default, the FOREIGN KEY constraint is disabled for OceanBase tenants. You can set the foreign_key_checks variable to control whether to enable this constraint.
Note
OceanBase does not support adding or changing constraints with the ALTER TABLE statement. Therefore, you must define the constraints when using the CREATE TABLE statement to create a table.
About default date/time settings for date/time columns
We recommend that you specify a default value for a column with the NOT NULL constraint. For a date/time column, you can set the default value to the current date/time of the database.
- Example: Specifying a default value for the date/time column of a table. You can use the SYSDATE or SYSTIMESTAMP 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 (0.07 sec)
obclient> insert into t1(id) values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
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 (0.00 sec)