Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 | 

Constraints

NOT NULL Constraint
Description

The NOT NULL constraint enforces a column to always contain a value other than NULL. Any column can have a NOT NULL constraint on the data. An error occurs if a NULL value is inserted or updated by using the INSERT or UPDATE statement.

Example

The following is an example of specifying the name column of the manager2 table as NOT NULL.

CREATE TABLE manager2
(name VARCHAR(40) NOT NULL,
event VARCHAR(50));

UNIQUE Constraint
Description

The UNIQUE constraint enforces a column to have a unique value. You can place a UNIQUE constraint on either a column or a set of columns.

Example

If a UNIQUE constraint is defined on a set of columns, this ensures the uniqueness of the values in all the columns. As shown below, the second INSERT statement succeeds because the value of column "a" is the same, but the value of column "b" is unique. The third INSERT statement causes an error because the values of column "a" and "b" are the same as those in the first INSERT statement.

CREATE TABLE "test"(
a INTEGER,
b INTEGER,
UNIQUE(a,b))
INSERT INTO "test" VALUES(1, 2);
INSERT INTO "test" VALUES(1, 3);
-- Succeeds because the value of column b is unique
INSERT INTO "test" VALUES(1, 2);
-- error because the values of columns a and b are the same as those in the first statement

PRIMARY KEY Constraint
Description

A key in a table is a set of column(s) that uniquely identifies each instance. A candidate key is a set of columns that uniquely identifies each instance of the table. You can define one of such candidate keys a a primary key. That is, the column defined as a primary key is uniquely identified in each instance.

Example
  • Example 1
  • As an example, take a look at the schema of the nation table provided in the olympic database as a demo. The code column is set as a single primary key.

CREATE TABLE nation (
   code             CHAR(3)     NOT NULL PRIMARY KEY,
   name             VARCHAR(40) NOT NULL,
   continent        VARCHAR(10) ,
   capital          VARCHAR(30)
);

  • Example 2
  • The record table is an example that has a 4-column composite key.

CREATE TABLE record (
host_year    INT NOT NULL,
event_code   INT NOT NULL,
athlete_code INT NOT NULL,
medal        CHAR(1)  NOT NULL,
score        VARCHAR(20),
unit         VARCHAR(5),
PRIMARY KEY(host_year, event_code, athlete_code, medal)
);

FOREIGN KEY Constraint
Description

A foreign key is a column or a set of columns that references the primary key in other tables in order to maintain reference relationship. The foreign key and the referenced primary key must have the same data type. Consistency between two tables is maintained by the foreign key referencing the primary key, which is called referential integrity.

Syntax

CREATETABLE | CLASS } table_name
[ {UNDER | AS SUBCLASS OF } super_class_name [ {, super_class_name }_ ] ] ...
[( column_definition [ { , column_definition }...; ]
| unique_constraint
| referential_constraint
)]

referential_constraint:
[FOREIGN KEY [constraint-name] (attribute_name[{, attribute_name }...; ])
REFERENCES referenced_class_name (attribute_name [{ , attribute_name }...; ])
[referential_triggered_action] ]

referential_triggered_action:
update_rule [ delete_rule [ cache_object_rule ] ]

update_rule:
ON UPDATE referential_action

delete_rule:
ON DELETE referential_action

cache_object_rule:
ON CACHE OBJECT cache_object_column_name

referential_action:
CASCADE
| RESTRICT
| NO ACTION

  • table_name : Specifies the name of the table to be created.
  • referential_constraint :
    • constraint-name : Specifies the name of the constraint. If omitted, it is automatically specified by CUBRID.
    • attribute_name : Specifies the name of the referencing foreign key. There is no limit to the number of foreign keys defined (the number of attributes identified), but the number must be identical to that of primary keys.
    • referenced_table_name : Specifies the name of the table to be referenced.
    • attribute_name : Specifies the name of the primary key to be referenced.
  • referential_triggered_action : Specifies the trigger action that responds to a certain operation in order to maintain referential integrity. ON UPDATE, ON DELETE or ON CACHE OBJECT can be specified. Each action can be defined multiple times, and the definition order is not significant.
    • ON UPDATE : Defines the action to be performed when attempting to update the primary key referenced by the foreign key. You can use either NO ACTION or RESTRICT option. The default is RESTRICT.
      • RESTRICT : Prevents the primary key from being changed.
      • NO ACTION : Does not change the foreign key even when the primary key is changed.
    • ON DELETE : Defines the action to be performed when attempting to delete the primary key referenced by the foreign key. You can use NO ACTION, RESTRICT or CASCADE option. The default is RESTRICT.
      • CASCADE : Specifies that all instances containing the referencing foreign key will be deleted when the primary key is deleted.
      • RESTRICT :Prevents the primary key from being deleted.
      • NO ACTION : Does not change the foreign key even when the primary key is deleted.
    • ON CACHE OBJECT : You can search an object using a direct object reference in object-oriented model. ON CACHE OBJECT option supports this feature in association with referential integrity (foreign key).
      • ON CACHE OBJECT option adds an OID reference to a foreign key configuration. The OID is used as a CACHE point for the foreign key to the primary key table. Such OID is managed by the system internally; it cannot be changed by users.
      • To define the ON CACHE OBJECT option, you must have defined a column whose domain is the table with a primary key and specified the column in the cache_object_column_name.
      • The attribute defined with ON CACHE OBJECT can use the OID the same way as the one of the existing object type and can maintain the OID reference when it is duplicated.
    Example

    The following is an example of showing the foreign key reference relationship in the olympic table provided as a demo. When creating the nation and olympic tables, specify that each table's primary key will be referenced by the foreign key in the participant table.

    CREATE TABLE nation (
       code             CHAR(3)     NOT NULL PRIMARY KEY,
      ....
    );

    CREATE TABLE olympic (
       host_year        INT    NOT NULL PRIMARY KEY,
       ....
    );

    CREATE TABLE participant (
       host_year        INT    NOT NULL ,
       nation_code      CHAR(3)     NOT NULL ,
       ....
       PRIMARY KEY(host_year, nation_code)   ,
       FOREIGN KEY(host_year) REFERENCES olympic(host_year),
       FOREIGN KEY(nation_code) REFERENCES nation(code)
    );

    Caution
    • In a referential constraint, the name of the primary key table to be referenced and the corresponding column names are defined. If the list of column names are is not specified, the primary key of the primary key table is specified in the defined order.
    • The number of primary keys in a referential constraint must be identical to that of foreign keys. The same column name cannot be used multiple times for the primary key in the referential constraint.