About types of column constraints

This topic describes how to use different types of constraints in OceanBase Database in Oracle mode.

Prerequisites

The current user has the CREATE and ALTER privileges on the target table.

Constraint types

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.

The types of constraints are described as follows:

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

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

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

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

• DEFAULT constraint: inserts the default value into a column. The default value is added to all new records unless otherwise specified.

  Note

  By default, the FOREIGN KEY constraint is enabled. You can set the foreign_key_checks variable to disable this constraint.

Example 1

• The NOT NULL constraint on the w_name column in the ware table requires that each warehouse must have a name.

  obclient> CREATE TABLE ware(
              id number,
              w_name varchar(256) not null,
              PRIMARY KEY (id)
           ) ;
  Query OK, 0 rows affected
  
  obclient> DESC ware;
  +--------+--------------+------+-----+---------+-------+
  | Field  | Type         | Null | Key | Default | Extra |
  +--------+--------------+------+-----+---------+-------+
  | id     | number(11)   | NO   | PRI | NULL    |       |
  | w_name | varchar(256) | NO   |     | NULL    |       |
  +--------+--------------+------+-----+---------+-------+
  2 rows in set
  

• 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.

  obclient> INSERT INTO ware(id) values(1);
  ORA-01400: cannot insert NULL into '(W_NAME)'
  

• 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.

  obclient> CREATE TABLE cust(          
              id number,          
              c_discount varchar(256) default '0.99',          
              PRIMARY KEY (id)          
             );
  Query OK, 0 rows affected
  
  obclient> DESC cust;
  +------------+--------------+------+-----+---------+-------+
  | Field      | Type         | Null | Key | Default | Extra |
  +------------+--------------+------+-----+---------+-------+
  | id         | number(11)   | NO   | PRI | NULL    |       |
  | c_discount | varchar(256) | YES  |     | 0.99    |       |
  +------------+--------------+------+-----+---------+-------+
  2 rows in set
  

Example 2

• The UNIQUE constraint on the w_name and w_city columns in the ware table requires that the warehouse names in each city must be unique.

  obclient> CREATE TABLE ware(
              w_id number,
              w_name varchar(256) unique,
              w_city varchar(256) unique,
              PRIMARY KEY (w_id)
                  ) ;
  Query OK, 0 rows affected
  
  obclient> DESC ware;
  +--------+--------------+------+-----+---------+-------+
  | Field  | Type         | Null | Key | Default | Extra |
  +--------+--------------+------+-----+---------+-------+
  | w_id   | number(11)   | NO   | PRI | NULL    |       |
  | w_name | varchar(256) | YES  | UNI | NULL    |       |
  | w_city | varchar(256) | YES  | UNI | NULL    |       |
  +--------+--------------+------+-----+---------+-------+
  3 rows in set
  

• If the w_name and w_city columns in the ware table violate the UNIQUE constraint, an error is reported.

  obclient> SELECT * FROM ware;
  +------+--------+--------+
  | w_id | w_name | w_city |
  +------+--------+--------+
  |    1 | ny     | LA     |
  +------+--------+--------+
  1 row in set
  
  obclient> INSERT INTO ware VALUES (2,'ny','LA');
  ORA-00001: unique constraint 'ny' for key 'WARE_OBUNIQUE_1650597684808687' violated
  

Example 3

• The ware table has a primary key column w_id, and this column must contain unique values and must not contain null values.

  • If the primary key column contains null values, an error is reported.

    obclient> INSERT INTO ware(w_name,w_city) VALUES ('ny', 'LA');
    ORA-01400: cannot insert NULL into '(W_ID)'
    

  • If the primary key column contains duplicate values, an error is reported.

    obclient> SELECT * FROM ware;
    +------+--------+--------+
    | w_id | w_name | w_city |
    +------+--------+--------+
    |    1 | ny     | LA     |
    +------+--------+--------+
    1 row in set (0.00 sec)
    
    obclient> INSERT INTO ware VALUES (1,'zs','EH');
    ORA-00001: unique constraint '1' for key 'WARE_OBPK_1650597684808654' violated
    

Example 4

• The FOREIGN KEY constraint on the c_w_id column of the cust table references the w_id column of the ware table. This means the warehouses allocated to the customers must reside in the ware table.

  obclient> ALTER TABLE cust ADD CONSTRAINT ware FOREIGN KEY (c_w_id) REFERENCES ware(w_id);
  Query OK, 0 rows affected
  

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.

Examples

Call the current_timestamp function to set a default value for each datetime column in a table.

1. Create a table named t1 and define the current_timestamp function.

   obclient> CREATE TABLE t1(
                id number not null primary key,
                gmt_create date not null default sysdate,
                gmt_Modified date not null default sysdate
   );
   Query OK, 0 rows affected
   

2. Insert data into the t1 table.

   obclient> insert into t1(id) values(1),(2),(3);
   Query OK, 3 rows affected (0.01 sec)
   Records: 3  Duplicates: 0  Warnings: 0
   

3. Execute an INSERT statement.

   obclient> INSERT INTO t1(id) values(1),(2),(3);
   Query OK, 3 rows affected
   Records: 3  Duplicates: 0  Warnings: 0
   

4. View the t1 table.

   obclient> SELECT * FROM t1;
   +----+------------+--------------+
   | ID | GMT_CREATE | GMT_MODIFIED |
   +----+------------+--------------+
   |  1 | 22-APR-22  | 22-APR-22    |
   |  2 | 22-APR-22  | 22-APR-22    |
   |  3 | 22-APR-22  | 22-APR-22    |
   +----+------------+--------------+
   3 rows in set