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.

Types of constraints

Oracle tenants of OceanBase Database support the NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK constraints.

NOT NULL

A NOT NULL constraint requires that the constrained column do not contain a NULL value. For a column with the NOT NULL constraint, you must specify the column value in an INSERT statement unless you have defined a default value for the column.

The following sample code creates a table named tbl1 and specifies the NOT NULL constraint for the col1 column:

obclient> CREATE TABLE tbl1(col1 INT NOT NULL,col2 INT);

UNIQUE

A UNIQUE constraint requires that the constrained column do not contain duplicate values. However, multiple NULL values are allowed.

The following sample code creates a table named tbl2 and specifies the UNIQUE constraint for the col1 column:

obclient> CREATE TABLE tbl2(col1 INT UNIQUE,col2 INT);

PRIMARY KEY

A PRIMARY KEY constraint is the combination of the NOT NULL constraint and the UNIQUE constraint.

The following sample code creates a table named tbl3 and specifies the PRIMARY KEY constraint for the col1 column.

obclient> CREATE TABLE tbl3(col1 INT PRIMARY KEY,col2 INT);

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

FOREIGN KEY

Values in the column constrained by a FOREIGN KEY constraint come from a primary key column of another table, which is referred to as a parent table.

When you create a FOREIGN KEY constraint, you can use the following keywords to specify the cascaded operation on the constrained column of the child table when a DELETE operation is performed on the referenced column of the parent table:

• CASCADE: If the referenced column of the parent table is dropped, the corresponding constrained column of the child table is automatically dropped.

• SET NULL: If the referenced column of the parent table is dropped, values of the corresponding constrained column of the child table are set to NULL.

  If you specify the SET NULL keyword, the constrained column cannot be a generated column.

If you do not specify the ON DELETE clause when you create a FOREIGN KEY constraint, the operation to drop the referenced column of the parent table violates the FOREIGN KEY constraint and will be rejected.

The following sample code creates a table named tbl4 and specifies to associate the col2 column with the primary key column col1 of a table named tbl3.

obclient> CREATE TABLE tbl4(col1 INT,col2 INT,FOREIGN KEY(col2) REFERENCES tbl3(col1) ON DELETE SET NULL);

In this example, if the col1 column of the parent table tbl3 is dropped, the system sets the values of the FOREIGN KEY column of the child table to NULL.

CHECK

A CHECK constraint requires that the values of a column must meet 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 SQL 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 SQL syntax is as follows:

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

• Drop a PRIMARY KEY constraint from the table.

  The SQL 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);
   

2. Add a PRIMARY KEY constraint to the table.

   obclient> ALTER TABLE tbl4 ADD PRIMARY KEY(col1);
   

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;
   

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 SQL 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);
  

  where

  • table_name1 specifies the name of the table to which you want to add the constraint, and table_name2 specifies the name of the associated table.

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

  • column_name1 specifies the name of the column to which the constraint applies, and column_name2 specifies 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);
   

   obclient> CREATE TABLE tbl6(col3 INT,col4 INT);
   

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

   obclient> ALTER TABLE tbl6 ADD CONSTRAINT fk FOREIGN KEY(col4) REFERENCES  tbl5(col1);
   

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

   obclient> ALTER TABLE tbl6 DROP CONSTRAINT fk;
   

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 SQL syntax is as follows:

  obclient> ALTER TABLE table_name ENABLE | DISABLE CONSTRAINT constraint_name;
  

• Add a CHECK constraint to the table.

  The SQL 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 SQL syntax is as follows:

  obclient> ALTER TABLE table_name MODIFY c1 NOT NULL;
  

• Drop a NOT NULL constraint from the table.

  The SQL 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.

1. Create a table named t1, and specify to use the current time of the database as the default values of the gmt_create and gmt_modified columns.

   obclient> CREATE TABLE t1(
      id INT NOT NULL PRIMARY KEY, 
      gmt_create TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
      gmt_modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
   );
   

2. Insert data into the table.

   obclient> INSERT INTO t1(id) VALUES(1),(2),(3);
   

3. View the data in the table.

   obclient> SELECT * FROM t1;
   

   The query result is as follows:

   +----+------------------------------+------------------------------+
   | ID | GMT_CREATE                   | GMT_MODIFIED                 |
   +----+------------------------------+------------------------------+
   |  1 | 10-NOV-23 10.12.36.417000 AM | 10-NOV-23 10.12.36.417000 AM |
   |  2 | 10-NOV-23 10.12.36.417000 AM | 10-NOV-23 10.12.36.417000 AM |
   |  3 | 10-NOV-23 10.12.36.417000 AM | 10-NOV-23 10.12.36.417000 AM |
   +----+------------------------------+------------------------------+
   3 rows in set