Scribd
Upload
43 views1 page

User-Defined Data Types: UNIQUE Column Constraint

The document discusses user-defined data types in SQL Anywhere. It provides the syntax to create a user-defined data type using the CREATE DOMAIN or CREATE DATATYPE statements followed by specifying a data type, identifier for the new type, and optional domain properties like DEFAULT values, identity, null/not null, and check conditions. User-defined data types can then be used like built-in data types.

Uploaded by

Nishki Gejmer
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
43 views1 page

User-Defined Data Types: UNIQUE Column Constraint

The document discusses user-defined data types in SQL Anywhere. It provides the syntax to create a user-defined data type using the CREATE DOMAIN or CREATE DATATYPE statements followed by specifying a data type, identifier for the new type, and optional domain properties like DEFAULT values, identity, null/not null, and check conditions. User-defined data types can then be used like built-in data types.

Uploaded by

Nishki Gejmer
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

Chapter 1: Creating 25

the child column to its DEFAULT value, one which presumably matches some
other row in the parent table.
The ON DELETE clause says that when a parent row is deleted, one of
three actions will be taken with all the corresponding child rows. ON DELETE
CASCADE eliminates the violation in a brute-force fashion by deleting all the
corresponding child rows. ON DELETE SET NULL changes the child column
to NULL so it no longer violates the constraint. ON DELETE SET DEFAULT
changes the child column to its DEFAULT value, one which matches some
other row in the parent table that hasnt been deleted yet.
All these repairs are made silently, with no error messages. There is a
fourth choice in each case: ON UPDATE RESTRICT and ON DELETE
RESTRICT are the default actions, which produce an error message and prevent
the operation on the parent table.
For performance reasons an index is created for every foreign key con-
straint, so you dont have to define the index yourself. This index may be
defined as CLUSTERED or NONCLUSTERED, with NONCLUSTERED
being the default. For more information about clustered indexes, see Section
10.7, CREATE INDEX.

1.10.5 UNIQUE Column Constraint


The UNIQUE column constraint specifies that all values must be non-NULL
and they must all be different from one another. A unique index is used to
implement this constraint, but a UNIQUE constraint is different because a
unique index allows NULL values. Also, a UNIQUE constraint can be treated
just like a PRIMARY KEY when a foreign key constraint is defined in another
table. A table may have more than one candidate key, only one of which can
be defined as the PRIMARY KEY; the others must be UNIQUE constraints.
The index corresponding to a UNIQUE constraint may be defined as
CLUSTERED or NONCLUSTERED, with NONCLUSTERED being the
default. For more information about clustered indexes, see Section 10.7,
CREATE INDEX.

1.11 User-Defined Data Types


SQL Anywhere provides a facility to combine built-in data types with
DEFAULT values, CHECK conditions, and NULL properties into user-defined
data types. These user-defined data types can then be used just like built-in data
types.
<create_domain> ::= CREATE DOMAIN <domain_definition>
| CREATE DATATYPE <domain_definition>
<domain_definition> ::= <user_defined_data_type> [ AS ] <data_type>
[ <domain_property_list> ]
<user_defined_data_type> ::= <identifier>
<domain_property_list> ::= <domain_property> { "," <domain_property> }
<domain_property> ::= DEFAULT <default_value>
| IDENTITY
| NULL
| NOT NULL
| CHECK "(" <boolean_expression> ")"

You might also like