Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to manage DM7 SQL domain". In the operation process of actual cases, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

Manage SQL domains

In order to support the definition and use of domain objects in SQL standards, DM supports DOMAIN creation, deletion, and authorization DDL statements, and supports the use of DOMAIN in table definitions. DOMAIN is a set of allowable values. Domains are defined in the schema and are defined by

Identification. A domain is a set of valid values used to constrain a column stored in a base table by various operations. A domain definition describes a data type that further describes the valid values of a constraint domain.

It can also explain one.

This clause specifies the value or default value for the column to use if no value is explicitly specified.

Create DOMAIN

CREATE DOMAIN Creates a new data domain. The user of the domain becomes its owner. DOMAIN is a schema type object whose name is unique within the schema.

exemplified

SQL> CREATE DOMAIN DA INT CHECK (VALUE

select name,id from sysobjects where name='DA';LINEID NAME ID ---------- ---- -----------1 DA 419430401used time: 0.945(ms). Execute id is 149.

use DOMAIN

In table definition statements, support for using domains for table column declarations. If the type definition of a column declaration uses a domain reference, the column definition directly inherits the data types, defaults, and CHECK constraints from the domain. If a column definition uses a domain and then defines its own default value, it ends up using its own default value. Users can use their own domain. If you want to use another user's domain, you must have been granted USAGE permission for that domain. The DBA role has this permission by default.

For example, use the domain DA created above in the T table.

SQL> CREATE TABLE T(ID DA);executed successfullyused time: 12.655(ms). Execute id is 149.SQL> select name,id from sysobjects where name='T';LINEID NAME ID ---------- ---- -----------1 T 1540used time: 1.255(ms). Execute id is 157.SQL> select * from syscolumns where id=1540;LINEID NAME ID COLID TYPE$ LENGTH$ SCALE NULLABLE$ DEFVAL INFO1 INFO2 ---------- ---- ----------- ----------- ------- ----------- ----------- --------- ------ ----------- -----------1 ID 1540 0 INTEGER 4 0 Y NULL 0 0used time: 1.249(ms). Execute id is 158.SQL> insert into t values(1); affect rows 1used time: 1.180(ms). Execute id is 160.SQL> insert into t values(100);insert into t values(100);[-6604]:Violate check constraint of [CONS134218952].used time: 1.176(ms). Execute id is 0.

Column definition Although the domain is used, the type-related field in the SYSCOLUMNS system table records the data type defined by the domain. That is, references to domains do not appear from SYSCOLUMNS system tables. Users who use a domain must have USAGE DOMAIN or USAGE ANY DOMAIN privileges for that domain.

Delete DOMAIN

Delete a user-defined domain. Users can delete domains that they own, and users with DROP ANY TABLE system privileges can delete domains in arbitrary mode. RESTRICT indicates that DOMAIN can be deleted only if it is not used by a table column; CASCADE indicates cascading deletion.

For example:

SQL> DROP DOMAIN DA CASCADE;executed successfullyused time: 29.834(ms). Execute id is 163. "DM7 SQL domain how to manage" content introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.