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.

Constraint types

The following uses the ware and cust tables as an example to introduce the constraint types in OceanBase Database.

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 ware(w_id),
primary key (c_w_id, c_d_id, c_id)
);
Query OK, 0 rows affected

• NOT NULL constraint: It does not allow the values in the constrained column to be NULL.

  For example, the NOT NULL constraint on 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.

• UNIQUE constraint: It does not allow duplicate values in the constrained column, but allows NULL values.

  For example, the UNIQUE constraint on the (w_name, w_city) column in the ware table requires that the warehouse names in each city must be unique.

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

  For example, both the ware table and the cust table have a primary key column. The primary key columns of the two tables are named w_id and c_id, which do not accept NULL or duplicate values.

  Here is a sample statement:

  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 ware(w_id),
  primary key (c_w_id, c_d_id, c_id)
  );
  
  Query OK, 0 rows affected
  

• FOREIGN KEY constraint: It requires the constrained column to contain only the values of the primary key column in another table.

  For example, if the FOREIGN KEY constraint on the c_w_id column in the cust table references the w_id column in the ware table, the warehouse allocated to a customer must be a warehouse in the ware table.

  OceanBase Database enables foreign key constraint checks by default, and the switch for foreign key constraint checks is controlled by the tenant variable foreign_key_checks. For information about foreign_key_checks, see foreign_key_checks.

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

Default setting for a datetime column

It is recommended that you specify a default value if a column has the NOT NULL constraint. If the data type of a column is datetime, you can set the default value to the current time of the database.

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