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

OceanBase Database supports the following types of constraints in the Oracle mode of OceanBase Database:

• NOT NULL constraint: requires that the constrained column do 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 default value for the column. 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  | NUMBER(38) | NO   | NULL | NULL   | NULL  |
  | COL2  | NUMBER(38) | YES  | NULL | NULL   | NULL  |
  +-------+------------+------+-----+---------+-------+
  2 rows in set
  

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

  Example: Create a table named tbl2 and specify the UNIQUE constraint for the col1 column.

  obclient> CREATE TABLE tbl2(col1 INT UNIQUE,col2 INT);
  Query OK, 0 rows affected
  
  obclient> desc tbl2;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | COL1  | NUMBER(38) | YES  | UNI | NULL    | NULL  |
  | COL2  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  2 rows in set
  

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

  Example: Create a table named tbl3 and specify the PRIMARY KEY constraint for the col1 column.

  obclient> CREATE TABLE tbl3(col1 INT PRIMARY KEY,col2 INT);
  Query OK, 0 rows affected
  
  obclient> desc tbl3;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | COL1  | NUMBER(38) | NO   | PRI | NULL    | NULL  |
  | COL2  | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  2 rows in set
  

  In the example, the col1 column must contain unique values and must not contain NULL values.

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

  Example: Create a table named tbl4 and associate the col2 column with the primary key column col1 of the tbl3 table.

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

  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.

View constraints

You can check the constraints that you created by querying the ALL_CONS_COLUMNS, DBA_CONS_COLUMNS, and USER_CONS_COLUMNS views.

Here is an example:

obclient> SELECT * FROM USER_CONS_COLUMNS WHERE table_name='TBL3';
+-------+----------------------------+------------+-------------+----------+
| OWNER | CONSTRAINT_NAME            | TABLE_NAME | COLUMN_NAME | POSITION |
+-------+----------------------------+------------+-------------+----------+
| SYS   | TBL4_OBPK_1649992639477914 | TBL3       | COL1        |        1 |
+-------+----------------------------+------------+-------------+----------+
1 row in set

Manage constraints

Manage PRIMARY KEY constraints

After you create a table, you can add a PRIMARY KEY constraint to the table or modify a PRIMARY KEY constraint for the table.

• Add a PRIMARY KEY constraint to the table.

  The syntax is as follows:

  obclient> ALTER TABLE table_name ADD PRIMARY KEY(column_name);
  

  We recommend that you add a PRIMARY KEY constraint to a table when you create the table.

• Modify a PRIMARY KEY constraint for the table.

  The syntax is as follows:

  obclient> ALTER TABLE table_name MODIFY PRIMARY KEY(column_name);
  

• Drop a PRIMARY KEY constraint from the table.

  The syntax is as follows:

  obclient> ALTER TABLE table_name DROP PRIMARY KEY;
  

Here is an example:

1. Create a table named tbl4.

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

2. Add a PRIMARY KEY constraint to the table.

   obclient> ALTER TABLE tbl4 ADD PRIMARY KEY(col1);
   Query OK, 0 rows affected
   

3. Change the column constrained by the PRIMARY KEY constraint to col2 for the table.

   obclient> ALTER TABLE tbl4 MODIFY PRIMARY KEY(col2);
   

4. Drop a PRIMARY KEY constraint from the table.

   obclient> ALTER TABLE tbl4 DROP PRIMARY KEY;
   Query OK, 0 rows affected
   

Manage FOREIGN KEY constraints

After you create a table, you can manage FOREIGN KEY constraints for the table as follows:

• Enable or disable a FOREIGN KEY constraint.

  By default, a constraint is enabled after it is created. If you have added a FOREIGN KEY constraint to a table when you created the table, you can enable or disable the constraint.

  The syntax is as follows:

  obclient> ALTER TABLE table_name ENABLE | DISABLE CONSTRAINT constraint_name;
  

• Add a FOREIGN KEY constraint to the table.

  The syntax is as follows:

  obclient> ALTER TABLE table_name1 ADD CONSTRAINT fk_name FOREIGN KEY (column_name1) REFERENCES table_name2(column_name2);
  

  The parameters are described as follows:

  • table_name1: the name of the table to which you want to add the constraint; table_name2: the name of the associated table.

  • fk_name: the name of the FOREIGN KEY constraint to be added.

  • column_name1: the name of the column to which the constraint applies; column_name2: the name of the primary key column in the associated table.

• Drop a FOREIGN KEY constraint from the table.

  The syntax is as follows:

  obclient> ALTER TABLE test DROP CONSTRAINT fk_name;
  

Here is an example:

1. Create a table named tbl5 and a table named tbl6.

   obclient> CREATE TABLE tbl5(col1 INT PRIMARY KEY,col2 INT);
   Query OK, 0 rows affected
   
   obclient> CREATE TABLE tbl6(col3 INT,col4 INT);
   Query OK, 0 rows affected
   

2. Add a FOREIGN KEY constraint to the tbl6 table.

   obclient> ALTER TABLE tbl6 ADD CONSTRAINT fk FOREIGN KEY(col4) REFERENCES  tbl5(col1);
   Query OK, 0 rows affected
   

3. Drop the FOREIGN KEY constraint from the tbl6 table.

   obclient> ALTER TABLE tbl6 DROP CONSTRAINT fk;
   Query OK, 0 rows affected
   

Manage CHECK constraints

After you create a table, you can manage CHECK constraints for the table as follows:

• Enable or disable a CHECK constraint.

  If you have added a CHECK constraint to a table when you created the table, you can enable or disable the constraint.

  The syntax is as follows:

  obclient> ALTER TABLE table_name ENABLE | DISABLE CONSTRAINT constraint_name;
  

• Add a CHECK constraint to the table.

  The syntax is as follows:

  ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(expression);
  

  Here is an example:

  obclient> ALTER TABLE ware ADD CONSTRAINT cst CHECK(w_city='hz');
  

Manage NOT NULL constraints

After you create a table, you can add a NOT NULL constraint to the table or drop a NOT NULL constraint from the table.

• Add a NOT NULL constraint to the table.

  The syntax is as follows:

  obclient> ALTER TABLE table_name MODIFY c1 NOT NULL;
  

• Drop a NOT NULL constraint from the table.

  The syntax is as follows:

  obclient> ALTER TABLE table_name DROP CONSTRAINT constraint_name;
  

  You can also use the following statement to drop a NOT NULL constraint:

  obclient> ALTER TABLE table_name MODIFY c1 NULL;
  

For more information about the syntax for managing 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.

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