Types of column constraints

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

Prerequisites

You must have the CREATE and ALTER privileges for tables.

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 include:

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

• UNIQUE constraint: enforces that the constrained column must not contain duplicate values, but it can contain multiple null values.

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

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

  Note

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

Example 1

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

  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

• For example, the UNIQUE constraint on the (w_name and w_city) column 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 or w_city column in the ware table violates the UNIQUE constraint, an error is returned.

  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

• If the ware table has a primary key column w_id, this column cannot contain NULL or duplicate values.

  • An error is returned if the primary key column contains NULLs.

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

  • An error is returned if the primary key column contains duplicate values.

    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
    

    Note

    In MySQL mode, the primary key column has a NOT NULL constraint by default.

Example 4

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

  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 the default value for the datetime column of a table.

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

   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