Define column constraints

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.

Constraint types

The following topic describes the constraints in OceanBase Database with the ware table and the cust table as examples:

obclient>CREATE TABLE ware (w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);

Query OK, 0 rows affected (0.09 sec)

obclient>CREATE TABLE cust (c_w_id int NOT NULL
, c_d_id int NOT null
, c_id int NOT NULL
, c_discount decimal(4, 4)
, c_credit char(2)
, c_last varchar(16)
, c_first varchar(16)
, c_middle char(2)
, c_balance decimal(12, 2)
, c_ytd_payment decimal(12, 2)
, c_payment_cnt int
, c_credit_lim decimal(12, 2)
, c_street_1 varchar(20)
, c_street_2 varchar(20)
, c_city varchar(20)
, c_state char(2)
, c_zip char(9)
, c_phone char(16)
, c_since date
, c_delivery_cnt int
, c_data varchar(500)
, index icust(c_last, c_d_id, c_w_id, c_first, c_id)
, FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id)
, primary key (c_w_id, c_d_id, c_id)
);

Query OK, 0 rows affected (0.10 sec)

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

  For example, the NOT NULL constraint on the w_name column in the ware table column 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 discounted price is 99% of the original price.

• 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 and w_city) column in the ware) table enforces that the warehouse names in each city must be unique.

• PRIMARY KEY constraint: It is 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.

  obclient>CREATE TABLE cust (c_w_id int NOT NULL,
  c_d_id int NOT NULL, c_id int NOT NULL,
  c_discount decimal(4, 4),
  c_credit char(2),
  c_last varchar(16),
  c_first varchar(16),
  c_middle char(2),
  c_balance decimal(12, 2),
  c_ytd_payment decimal(12, 2),
  c_payment_cnt int,
  c_credit_lim decimal(12, 2),
  c_street_1 varchar(20),
  c_street_2 varchar(20),
  c_city varchar(20),
  c_state char(2),
  c_zip char(9),
  c_phone char(16),
  c_since date,
  c_delivery_cnt int,
  c_data varchar(500),
  index icust(c_last, c_d_id, c_w_id, c_first, c_id),
  FOREIGN KEY (c_w_id) REFERENCES table_name1(w_id),
  primary key (c_w_id, c_d_id, c_id)
  );
  
  Query OK, 0 rows affected (0.10 sec)
  

• FOREIGN KEY constraint: enforces that the constrained column must contain only 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 references the w_id column of the ware table, the warehouses allocated to the customers must be warehouses in the ware table.

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

• CHECK constraint: enforces that the value of a column must meet specified conditions. The CHECK constraint is supported in Oracle mode only.

In Oracle mode, you can enable or disable FOREIGN KEY and CHECK constraints by executing the following statement: The syntax is as follows:

obclient> ALTER TABLE table_name MODIFY CONSTRAINT constrain_name ENABLE | DISABLE;

By default, a constraint is enabled after it is created. Note

The current OceanBase Database version does not support constraint addition or modification by using the ALTER TABLE statement. Therefore, you must confirm the constraints for a table when you create the table.

Default date and time setting for the date and 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: Use the SYSDATE or SYSTIMESTAMP function to set the default value for the date and time column

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)