Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
CREATE TABLE statementUse the CREATE TABLE statement to create a new permanent table in the current database.
Column definitionUse the column definition segment of the CREATE TABLE statement to declare the name and data type (and optionally the default value and the constraints or the security label) of a single column of the new table.
Single-Column Constraint FormatUse the Single-Column Constraint format to define and declare the name of at least one constraint on a single column, and to specify the mode of each constraint.
Using UNIQUE or DISTINCT Constraints

Using UNIQUE or DISTINCT Constraints

Use the UNIQUE or DISTINCT keyword to require that a column or set of columns accepts only unique data values. You cannot insert values that duplicate the values of some other row into a column that has a unique constraint. When you create a UNIQUE or DISTINCT constraint, the database server automatically creates an internal index on the constrained column or columns. (In this context, the keyword DISTINCT is a synonym for UNIQUE.)

You cannot place a unique constraint on a column that already has a primary-key constraint. You cannot place a unique constraint on a BYTE or TEXT column.

As previously noted, you cannot place a unique or primary-key constraint on a BLOB or CLOB column of HCL OneDB™.

Opaque data types support a unique constraint only where a secondary-access method supports uniqueness for that type. The default secondary-access method is a generic B-tree, which supports the equal( ) operator function. Therefore, if the definition of the opaque type includes the equal( ) function, a column of that opaque type can have a unique constraint.

The following example creates a simple table that has a unique constraint on one of its columns:

CREATE TABLE accounts
   (acc_name  CHAR(12), 
    acc_num   SERIAL UNIQUE CONSTRAINT acc_num);

For an explanation of the constraint name, refer to Declaring a Constraint Name.