Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Constraint Definition

Description

You can define NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY as the constraints. You can also create an index by using INDEX or KEY.

<column_constraint> ::=

NOT NULL | UNIQUE | PRIMARY KEY | FOREIGN KEY <referential definition>

 

<table_constraint> ::=

[ CONSTRAINT [ <constraint_name> ] ] UNIQUE [ KEY | INDEX ]( column_name_comma_list ) |

[ { KEY | INDEX } [ <constraint_name> ]( column_name_comma_list ) |

[ PRIMARY KEY ( column_name_comma_list )] |

[ <referential_constraint> ]

 

<referential_constraint> ::=

FOREIGN KEY ( column_name_comma_list ) <referential definition>

 

<referential definition> ::=

REFERENCES [ referenced_table_name ] ( column_name_comma_list )

[ <referential_triggered_action> ... ]

 

<referential_triggered_action> ::=

{ ON UPDATE <referential_action> } |

{ ON DELETE <referential_action> } |

{ ON CACHE OBJECT cache_object_column_name }

 

<referential_action> ::=

CASCADE | RESTRICT | NO ACTION  | SET NULL

NOT NULL Constraints
Description

A column for which the NOT NULL constraint has been defined must have a certain value that is not NULL. The NOT NULL constraint can be defined for all columns. An error occurs if you try to insert a NULL value into a column with the NOT NULL constraint by using the INSERT or UPDATE statement.

Example

CREATE TABLE const_tbl1(id INT NOT NULL, INDEX i_index(id ASC), phone VARCHAR);

 

CREATE TABLE const_tbl2(id INT NOT NULL PRIMARY KEY, phone VARCHAR);

INSERT INTO const_tbl2 (NULL,'000-0000');

 

In line 2, column 25,

 

ERROR: syntax error, unexpected Null

UNIQUE Constraint
Description

The UNIQUE constraint enforces a column to have a unique value. An error occurs if a new record that has the same value as the existing one is added by this constraint.

You can place a UNIQUE constraint on either a column or a set of columns. If the UNIQUE constraint is defined for multiple columns, the uniqueness is ensured not for each column, but the combination of multiple 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.

--UNIQUE constraint is defined on a single column only

CREATE TABLE const_tbl5(id INT UNIQUE, phone VARCHAR);

INSERT INTO const_tbl5(id) VALUES (NULL), (NULL);

INSERT INTO const_tbl5 VALUES (1, '000-0000');

SELECT * FROM const_tbl5;

 

           id  phone

===================================

         NULL  NULL

         NULL  NULL

            1  '000-0000'

 

INSERT INTO const_tbl5 VALUES (1, '111-1111');

 

ERROR: Operation would have caused one or more unique constraint violations.

 

 

--UNIQUE constraint is defined on several columns

CREATE TABLE const_tbl6(id INT, phone VARCHAR, CONSTRAINT UNIQUE(id,phone));

INSERT INTO const_tbl6 VALUES (1,NULL), (2,NULL), (1,'000-0000'), (1,'111-1111');

SELECT * FROM const_tbl6;

 

           id  phone

===================================

            1  NULL

            2  NULL

            1  '000-0000'

            1  '111-1111'

PRIMARY KEY Constraint
Description

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

By default, the index created by defining the primary key is created in ascending order, and you can define the order by specifying ASC or DESC keyword next to the column.

Syntax

CREATE TABLE pk_tbl (a INT, b INT, PRIMARY KEY (a, b DESC));

Example

CREATE TABLE const_tbl7(

id INT NOT NULL,

phone VARCHAR,

CONSTRAINT pk_id PRIMARY KEY(id));

 

--CONSTRAINT keyword

CREATE TABLE const_tbl8(

id INT NOT NULL PRIMARY KEY,

phone VARCHAR);

 

--primary key is defined on multiple columns

CREATE TABLE const_tbl8 (

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

[ CONSTRAINT < constraint_name > ]

FOREIGN KEY [ <foreign_key_name> ] ( column_name_comma_list )

REFERENCES [ referenced_table_name ] ( column_name_comma_list )

[ <referential_triggered_action> ]

 

<referential_triggered_action> :

ON UPDATE <referential_action>

[ ON DELETE <referential_action> [ ON CACHE OBJECT cache_object_column_name ]]

 

<referential_action> :

CASCADE | RESTRICT | NO ACTION | SET NULL

  • constraint_name : Specifies the name of the table to be created.
  • foreign_key_name : Specifies a name of the FOREIGN KEY constraint. You can skip the name specification. However, if you specify this value, constraint_name will be ignored, and the specified value will be used.
  • column_name : Specifies the name of the column to be defined as a foreign key after the FOREIGN KEY keyword. There is no limit on the number of foreign keys to be defined (the number of columns), but it must be the same number as that of the referred primary keys.
  • referenced_table_name : Specifies the name of the table to be referenced.
  • column_name : Specifies the name of the referred primary key column after the FOREIGN KEY keyword.
  • 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, RESTRICT, or SET NULL option. The default is RESTRICT.
    • 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, CASCADE, or SET NULL option. The default is RESTRICT.
    • 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.
  • referential_ action : You can define an option that determines whether to maintain the value of the foreign key when the primary key value is deleted or updated.
    • CASCADE : If the primary key is deleted, the foreign key is deleted as well. This option is supported only for the ON DELETE operation.
    • RESTRICT : Prevents the value of the primary key from being deleted or updated, and rolls back any transaction that has been attempted.
    • SET NULL : When a specific record is being deleted or updated, the column value of the foreign key is updated to NULL.
    • NO ACTION : Its behavior is the same as that of the RESTRICT option.
Example

--creaing two tables where one is referencing the other

CREATE TABLE a_tbl(

id INT NOT NULL DEFAULT 0 PRIMARY KEY,

phone VARCHAR(10));

 

CREATE TABLE b_tbl(

ID INT NOT NULL,

name VARCHAR(10) NOT NULL,

CONSTRAINT pk_id PRIMARY KEY(id),

CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES a_tbl(id)

ON DELETE CASCADE ON UPDATE RESTRICT);

 

INSERT INTO a_tbl VALUES(1,'111-1111'), (2,'222-2222'), (3, '333-3333');

INSERT INTO b_tbl VALUES(1,'George'),(2,'Laura'),(3,'Max');

SELECT a.id, b.id, a.phone, b.name FROM a_tbl a, b_tbl b WHERE a.id=b.id;

 

           id           id                   phone                 name

==============================================================================

            1            1                   '111-1111'            'George'

            2            2                   '222-2222'            'Laura'

            3            3                   '333-3333'            'Max'

 

--when deleting primay key value, it cascades foreign key value  

DELETE FROM a_tbl WHERE id=3;

 

1 rows affected.

 

SELECT a.id, b.id, a.phone, b.name FROM a_tbl a, b_tbl b WHERE a.id=b.id;

 

           id           id                   phone                 name

==============================================================================

            1            1                   '111-1111'            'George'

            2            2                   '222-2222'            'Laura'

 

--when attempting to update primay key value, it restricts the operation

UPDATE  a_tbl SET id = 10 WHERE phone = '111-1111';

 

In the command from line 1,

 

ERROR: Update/Delete operations are restricted by the foreign key 'fk_id'.

 

0 command(s) successfully processed.

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.
  • The actions cascaded by reference constraints do not activate the trigger action.
  • It is not recommended to use referential_triggered_action in the CUBRID HA environment. In the CUBRID HA environment, the trigger action is not supported. Therefore, if you use referential_triggered_action, the data between the master database and the slave database can be inconsistent. For more information, see CUBRID HA.
KEY or INDEX
Description

KEY and INDEX are used interchangeably. They create an index that uses the corresponding column as a key. You can specify the index name. If omitted, a name is assigned automatically.

Example

CREATE TABLE const_tbl3(id INT, phone VARCHAR, INDEX(id DESC, phone ASC));

 

CREATE TABLE const_tbl4(id INT, phone VARCHAR, KEY i_key(id DESC, phone ASC));