| Oracle Rdb SQLリファレンス・マニュアル リリース7.2 E06178-01 |
|
 戻る |
 次へ |
UNIQUEセマンティクスは、実行時に選択された任意の言語で使用されます。つまり、UNIQUE制約の新規スタイルを有効にするには、リストされている言語で表を作成する必要があります。 |
~S: Constraint "UNIQUE_A" evaluated
Cross block of 2 entries
Cross block entry 1
Conjunct Firstn Get Retrieval by DBK of relation T_UNIQUE
Cross block entry 2
Conjunct Aggregate-F2 Conjunct
Index only retrieval of relation T_UNIQUE
Index name T_UNIQUE_INDEX_A [0:0]
|
簡易化されたUNIQUE制約（"UNIQUE_B"）の場合、オプティマイザは、索引の直接参照（[1:1]）を使用できます。これにより、制約評価を実行するための索引に対するI/Oを削減できます。
~S: Constraint "UNIQUE_B" evaluated
Cross block of 2 entries
Cross block entry 1
Conjunct Firstn Get Retrieval by DBK of relation T_UNIQUE
Cross block entry 2
Conjunct Aggregate-F2 Index only retrieval of relation T_UNIQUE
Index name T_UNIQUE_INDEX_B [1:1]
|
Oracle Rdbのこのリリースの、Item=Table修飾子を持つRMU Extractコマンドでは、旧UNIQUE制約と新規UNIQUE制約は区別されません。生成されたSQLスクリプトを変更し、このスクリプトを使用してデータベースを作成する前に適切な言語を確立する必要があります。 |
SQL> CREATE TABLE T_USER_UNIQUE_NEW cont> A INTEGER, cont> B INTEGER, cont> CONSTRAINT UNIQUE_AB_NEW cont> CHECK ((SELECT COUNT(*) cont> FROM T_USER_UNIQUE_NEW T2 cont> WHERE T2.A = T_USER_UNIQUE_NEW.A and cont> T2.B = T_USER_UNIQUE_NEW.B) <= 1) cont> NOT DEFERRABLE cont> ); |
Oracle Rdbの以前のバージョンでは、1と等価であることのみが一意性制約とみなされていました。この例では、LESS THANまたはEQUAL TO 1の比較にも一意性制約の資格があります。
一時表のメモリー使用率の計算は、『Oracle Rdb7 Guide to Database Design and Definition』を参照してください。
参照制約、トリガー、記憶域マップおよび索引などの他の表属性は継承されないため、個別に作成する必要があります。
現行表を参照するための副選択がCOMPUTED BY式に使用される場合、この情報は変更されずに新規表に継承されます。後でALTER TABLE文を実行し、COMPUTED BY列を削除して再定義する必要があります。 |
SQL> create table my_sys like rdb$database; %RDB-E-NO_META_UPDATE, metadata update failed -RDMS-E-NOMETSYSREL, operation illegal on system defined metadata |
例1: 主キーと外部キーを使用した新規表の作成この例では、CREATE TABLE文を使用して、人事データベースにEMPLOYEES_2表、SALARY_HISTORY_2表およびWORK_STATUS_2表を作成しています。列定義は、データベース全体のドメイン定義に基づいて指定しています。
SALARY_HISTORY_2表に指定されるFOREIGN KEY制約は、EMPLOYEES_2表に指定されるPRIMARY KEY制約と一致する必要があります。
また、指定されるCHECK制約は、参照先の列からカンマによって区切られるため、表制約です。この場合、単一列EMPLOYEE_IDのみを参照しているため、EMPLOYEE_IDに対する列制約であっても効果は同じです。
この言語はSQL99であるため、制約の評価時間のデフォルトはNOT DEFERRABLEです。
SQL> -- *** Set Dialect *** SQL> -- SQL> SET DIALECT 'SQL99'; SQL> -- SQL> -- *** Create tables *** SQL> -- SQL> CREATE TABLE WORK_STATUS_2 cont> ( cont> STATUS_CODE STATUS_CODE_DOM cont> CONSTRAINT WS2_STATUS_CODE_PRIMARY cont> PRIMARY KEY, cont> STATUS_NAME STATUS_NAME_DOM, cont> STATUS_TYPE STATUS_DESC_DOM cont> ); SQL> -- SQL> CREATE TABLE EMPLOYEES_2 cont> ( cont> EMPLOYEE_ID ID_DOM cont> CONSTRAINT E2_EMPLOYEE_ID_PRIMARY cont> PRIMARY KEY, cont> LAST_NAME LAST_NAME_DOM, cont> FIRST_NAME FIRST_NAME_DOM, cont> MIDDLE_INITIAL MIDDLE_INITIAL_DOM, cont> ADDRESS_DATA_1 ADDRESS_DATA_1_DOM, cont> ADDRESS_DATA_2 ADDRESS_DATA_2_DOM, cont> CITY CITY_DOM, cont> STATE STATE_DOM, cont> POSTAL_CODE POSTAL_CODE_DOM, cont> SEX SEX_DOM cont> CONSTRAINT EMPLOYEE_SEX_VALUES cont> CHECK ( cont> SEX IN ('M', 'F') OR SEX IS NULL cont> ), cont> BIRTHDAY DATE_DOM, cont> STATUS_CODE STATUS_CODE_DOM cont> CONSTRAINT E2_STATUS_CODE_FOREIGN cont> REFERENCES WORK_STATUS_2 (STATUS_CODE), cont> CONSTRAINT EMP_STATUS_CODE_VALUES_2 cont> CHECK ( cont> STATUS_CODE IN ('0', '1', '2') cont> OR STATUS_CODE IS NULL cont> ) cont> ); SQL> -- SQL> CREATE TABLE SALARY_HISTORY_2 cont> ( cont> EMPLOYEE_ID ID_DOM cont> CONSTRAINT SH2_EMPLOYEES_ID_FOREIGN cont> REFERENCES EMPLOYEES_2 (EMPLOYEE_ID), cont> SALARY_AMOUNT SALARY_DOM, cont> SALARY_START DATE_DOM, cont> SALARY_END DATE_DOM cont> ); SQL>
例2: 多数のSQLデータ型を使用した表の作成
次の例は、サンプル・ディレクトリでのOracle Rdbのインストール時に作成したサンプル・プログラムsql_all_datatypesからの抜粋です。sql_all_datatypesは、様々な言語について、各種データ型に合せてプログラム変数を宣言する方法や、列値またはNULL値を格納および取得するときにこれらの変数をSQL文で指定する方法を示します。
この例は、sql_all_datatypesプログラムのCREATE TABLE文を示しています。
EXEC SQL CREATE TABLE ALL_DATATYPES_TABLE ( CHAR_COL CHAR(10), SMALLINT_COL SMALLINT, SMALLINT_SCALED_COL SMALLINT (3), INTEGER_COL INTEGER, INTEGER_SCALED_COL INTEGER (2), QUADWORD_COL QUADWORD, QUADWORD_SCALED_COL QUADWORD (5), REAL_COL REAL, DOUBLE_PREC_COL DOUBLE PRECISION, DATE_COL DATE, VARCHAR_COL VARCHAR(40) );
例3: 列のデフォルト値の指定
次の例は、列のデフォルト値の使用方法を示しています。各営業担当者は、自分の日々の売上情報をDAILY_SALES表に入力します。
SQL> -- SQL> CREATE TABLE DAILY_SALES cont> -- cont> -- The column SALESPERSON is based on LAST_NAME_DOM and cont> -- the default value is the user name of the person who cont> -- enters the information: cont> (SALESPERSON LAST_NAME_DOM DEFAULT USER, cont> -- cont> -- Typical work day is 8 hours: cont> HOURS_WORKED SMALLINT DEFAULT 8, cont> HOURS_OVERTIME SMALLINT, cont> GROSS_SALES INTEGER ); SQL> -- SQL> -- Insert daily sales information accepting the SQL> -- default values for SALESPERSON and HOURS_WORKED: SQL> -- SQL> INSERT INTO DAILY_SALES cont> (HOURS_OVERTIME, GROSS_SALES ) cont> VALUES cont> (1, 2499.00); 1 row inserted SQL> SELECT * FROM DAILY_SALES; SALESPERSON HOURS_WORKED HOURS_OVERTIME GROSS_SALES KILPATRICK 8 1 2499 1 row selected
例4: DELETE文による制約の間接的違反
制約により、制約に指定されている条件を満たさない表にINSERT文によって行を追加できなくなります。また、削除または変更がデータベース内の別の表の制約に違反している場合も、制約により、DELETE文またはUPDATE文によって値を削除または変更できなくなります。次の例は、この点を示しています。
SQL> -- TEST has no constraints defined for it, but it is subject to SQL> -- restrictions nonetheless because of the constraint specified SQL> -- in TEST2: SQL> CREATE TABLE TEST cont> (COL1 REAL); SQL> SQL> CREATE TABLE TEST2 cont> (COL1 REAL, cont> CHECK (COL1 IN cont> (SELECT COL1 FROM TEST)) cont> ); SQL> COMMIT; SQL> SQL> INSERT INTO TEST VALUES (1); 1 row inserted SQL> INSERT INTO TEST2 VALUES (1); 1 row inserted SQL> COMMIT; SQL> -- This DELETE statement will fail because it will cause COL1 in SQL> -- TEST2 to contain a value without the same value in COL1 of TEST: SQL> DELETE FROM TEST WHERE COL1 = 1; 1 row deleted SQL> COMMIT; %RDB-E-INTEG_FAIL, violation of constraint TEST2_CHECK1 caused operation to fail
例5: 動詞実行時における制約の評価
遅延可能制約は、トランザクションによってCOMMIT文が発行されるまで評価されません。SET TRANSACTION文のEVALUATING句を使用すると、この制約をより頻繁に評価するように指定できます。
SQL> create table TEST cont> (col1 integer, cont> col2 integer cont> constraint C2 cont> unique cont> deferrable cont> ); SQL> SQL> insert into TEST (col1, col2) values (1, 2); 1 row inserted SQL> commit; SQL> SQL> /* ***> This INSERT will violate the constraint as shown by ***> the error during COMMIT ***> */ SQL> insert into TEST (col1, col2) values (1, 2); 1 row inserted SQL> commit; %RDB-E-INTEG_FAIL, violation of constraint C2 caused operation to fail -RDB-F-ON_DB, on database USER_DISK:[DOC.DATABASES]MF_PERSONNEL.RDB;1 SQL> /* ***> The COMMIT failed, so we will ROLLBACK ***> */ SQL> rollback; SQL> SQL> /* ***> You can change the evalution time using the EVALUATING ***> clause of SET TRANSACTION ***> */ SQL> set transaction read write evaluating C2 at verb time; SQL> insert into TEST (col1, col2) values (1, 2); %RDB-E-INTEG_FAIL, violation of constraint C2 caused operation to fail -RDB-F-ON_DB, on database USER_DISK:[DOC.DATABASES]MF_PERSONNEL.RDB;1 SQL> rollback;
例6: CREATE TABLE文でのDECIMALデータ型の指定
SQLは、パック10進数または数値文字列データ型をサポートしていません。CREATE TABLE文またはALTER TABLE文の列にDECIMALデータ型またはNUMERICデータ型を指定すると、SQLでは警告メッセージが表示され、指定した精度引数に応じたデータ型で列が作成されます（詳細は、第2.3.3項を参照）。この例は、DECIMALデータ型を指定するCREATE TABLE文を示しています。
SQL> CREATE TABLE TEMP cont> (DECIMAL_EX DECIMAL); %SQL-I-NO_DECIMAL, DECIMAL_EX is being converted from DECIMAL to INTEGER. SQL>
例7: リポジトリ・レコード定義に基づく表の作成
次の例では、CREATE TABLE文でFROM句を使用して、リポジトリ・レコード定義に基づいて制約のある表を作成しています。PARTSレコード（表）には、フィールド（列）PART_IDに基づく主キー、フィールド（列）PART_NOに基づく一意キー、および他の制約があります。
この例は、OTHER_PARTSレコードとOTHER_PARTS_IDフィールドが事前にリポジトリに定義されていることを前提としています。この例は、共通ディクショナリ演算子ユーティリティを使用したリポジトリでのフィールドおよびレコードの定義から始まっています。
$ ! $ ! Define CDD$DEFAULT: $ ! $ DEFINE CDD$DEFAULT SYS$COMMON:[REPOSITORY]TABLE_TEST $ ! $ ! Enter the respository to create new field and record definitions: $ ! $ REPOSITORY CDO> ! CDO> ! Create the field definitions for the PARTS record: CDO> ! CDO> DEFINE FIELD PART_NO DATATYPE IS SIGNED WORD. CDO> DEFINE FIELD PART_ID DATATYPE IS SIGNED LONGWORD. CDO> DEFINE FIELD PART_ID_USED_IN DATATYPE IS SIGNED LONGWORD. CDO> DEFINE FIELD PART_QUANT DATATYPE IS SIGNED WORD. CDO> ! CDO> ! Create the PARTS record definition by first defining the constraints CDO> ! and then including the field definitions just created. Note that CDO> ! CDO creates the constraints as not deferrable. CDO> ! CDO> DEFINE RECORD PARTS cont> CONSTRAINT PARTS_PMK PRIMARY KEY PART_ID cont> CONSTRAINT PARTS_UNQ UNIQUE PART_NO cont> CONSTRAINT PART_CST CHECK cont> (ANY P IN PARTS WITH (PART_ID IN cont> PARTS = PART_ID_USED_IN IN P)) cont> CONSTRAINT PART_FRK cont> FOREIGN KEY PART_ID REFERENCES OTHER_PARTS OTHER_PART_ID. cont> PART_NO. cont> PART_ID. cont> PART_ID_USED_IN. cont> PART_QUANT. cont> END. CDO> ! CDO> ! Display the RECORD PARTS: CDO> ! CDO> SHOW RECORD PARTS/FULL Definition of record PARTS | Contains field PART_NO | | Datatype signed word | Contains field PART_ID | | Datatype signed longword | Contains field PART_ID_USED_IN | | Datatype signed longword | Contains field PART_QUANT | | Datatype signed word | Constraint PARTS_PMK primary key PART_ID NOT DEFERRABLE | Constraint PARTS_UNQ unique PART_NO NOT DEFERRABLE | Constraint PART_CST (ANY (P IN PARTS WITH | (PART_ID IN PARTS EQ PART_ID_USED_IN IN P))) NOT DEFERRABLE | Constraint PART_FRK foreign key PART_ID references OTHER_PARTS | OTHER_PART_ID NOT DEFERRABLE CDO> EXIT $ ! $ ! Entering SQL: $ SQL SQL> ! SQL> ! Attach to the AUTO database: SQL> ! SQL> ATTACH 'ALIAS AUTO PATHNAME AUTO'; SQL> ! SQL> ! Create a table called PARTS using the PARTS record (table) SQL> ! just created in the repository: SQL> ! SQL> CREATE TABLE FROM SYS$COMMON:[REPOSITORY]TABLE_TEST.PARTS cont> ALIAS AUTO; SQL> ! SQL> ! Use the SHOW TABLE statement to display the information about the SQL> ! PARTS table: SQL> ! SQL> SHOW TABLE AUTO.PARTS; Information for table AUTO.PARTS CDD Pathname: SYS$COMMON:[REPOSITORY]TABLE_TEST.PARTS;1 Columns for table AUTO.PARTS: Column Name Data Type Domain ----------- --------- ------ PART_NO SMALLINT AUTO.PART_NO PART_ID INTEGER AUTO.PART_ID PART_ID_USED_IN INTEGER AUTO.PART_ID_USED_IN PART_QUANT SMALLINT AUTO.PART_QUANT Table constraints for AUTO.PARTS: AUTO.PARTS_PMK Primary Key constraint Table constraint for AUTO.PARTS Evaluated on each VERB Source: primary key PART_ID AUTO.PARTS_UNQ Unique constraint Table constraint for AUTO.PARTS Evaluated on each VERB Source: unique PART_NO AUTO.PART_CST Check constraint Table constraint for AUTO.PARTS Evaluated on each VERB Source: (ANY (P IN PARTS WITH (PART_ID IN PARTS EQ PART_ID_USED_IN IN P))) AUTO.PART_FRK Foreign Key constraint Table constraint for AUTO.PARTS Evaluated on each VERB Source: foreign key PART_ID references OTHER_PARTS OTHER_PART_ID Constraints referencing table AUTO.PARTS: No constraints found . . . SQL> -- SQL> COMMIT; SQL> DISCONNECT DEFAULT; SQL> EXIT;
例8: 単一列の主キーおよび外部キーを使用した表固有の制約の定義
この例では、単一列のキーを使用して表固有の制約を定義しています。この例では、主キーおよび外部キーを使用して、関連する4つの表間の参照整合性を維持しています。
3つの単一列の主キー制約により、表間の整合性を保持します。主キー制約は、EMPLOYEES_TEST表のEMPLOYEE_ID列、JOBS_TEST表のJOB_CODE列、およびDEPARTMENTS_TEST表のDEPARTMENT_CODE列です。JOB_HISTORY_TEST表には、これらの主キーを参照する3つの外部キー制約があります。
言語はSQL99に設定されているため、制約はNOT DEFERRABLEです。
SQL> SET DIALECT 'SQL99'; SQL> -- SQL> CREATE TABLE EMPLOYEES_TEST cont> (EMPLOYEE_ID ID_DOM cont> CONSTRAINT E_TEST_EMP_ID_PRIMARY cont> PRIMARY KEY, cont> LAST_NAME LAST_NAME_DOM, cont> FIRST_NAME FIRST_NAME_DOM, cont> MIDDLE_INITIAL MIDDLE_INITIAL_DOM, cont> ADDRESS_DATA_1 ADDRESS_DATA_1_DOM, cont> ADDRESS_DATA_2 ADDRESS_DATA_2_DOM, cont> CITY CITY_DOM, cont> STATE STATE_DOM, cont> POSTAL_CODE POSTAL_CODE_DOM, cont> SEX SEX_DOM, cont> BIRTHDAY DATE_DOM, cont> STATUS_CODE STATUS_CODE_DOM); SQL> -- SQL> CREATE TABLE JOBS_TEST cont> (JOB_CODE JOB_CODE_DOM, cont> CONSTRAINT J_TEST_CODE_PRIMARY cont> PRIMARY KEY (JOB_CODE), cont> WAGE_CLASS WAGE_CLASS_DOM, cont> JOB_TITLE JOB_TITLE_DOM, cont> MINIMUM_SALARY SALARY_DOM, cont> MAXIMUM_SALARY SALARY_DOM); SQL> -- SQL> CREATE TABLE DEPARTMENTS_TEST cont> (DEPARTMENT_CODE DEPARTMENT_CODE_DOM, cont> CONSTRAINT D_DEPT_CODE_PRIMARY cont> PRIMARY KEY (DEPARTMENT_CODE), cont> DEPARTMENT_NAME DEPARTMENT_NAME_DOM, cont> MANAGER_ID ID_DOM, cont> BUDGET_PROJECTED BUDGET_DOM, cont> BUDGET_ACTUAL BUDGET_DOM); SQL> -- SQL> CREATE TABLE JOB_HISTORY_TEST cont> (EMPLOYEE_ID ID_DOM cont> CONSTRAINT JH_TEST_EMP_ID_FOREIGN cont> REFERENCES EMPLOYEES_TEST (EMPLOYEE_ID), cont> JOB_CODE JOB_CODE_DOM cont> CONSTRAINT JH_J_CODE_FOREIGN cont> REFERENCES JOBS_TEST (JOB_CODE), cont> JOB_START DATE_DOM, cont> JOB_END DATE_DOM, cont> DEPARTMENT_CODE DEPARTMENT_CODE_DOM cont> CONSTRAINT JH_D_CODE_FOREIGN cont> REFERENCES DEPARTMENTS_TEST (DEPARTMENT_CODE), cont> SUPERVISOR_ID ID_DOM); SQL>
例9: 複数列の主キーおよび外部キーを使用した表固有の制約の定義
次の例では、複数列のキーを使用して、人事データベースのセグメントを使用して表固有の制約を定義しています。この例では、サンプル・データベースには用意されていない定義を使用しています。
この例では、2つの列LOCおよびDEPTがキーを構成しており、これらはWORK_STATION表のPRIMARY KEY制約として定義されています。WORKER表の2つの列LOCATIONおよびDEPARTMENTは、WORK_STATION表の主キーを参照する外部キーです。
言語はSQL99に設定されているため、制約はNOT DEFERRABLEであり、制約を定義する場合、廃止予定の機能に関するメッセージは表示されません。
SQL> SET DIALECT 'SQL99'; SQL> -- SQL> CREATE DOMAIN LOC_DOM CHAR (10); SQL> CREATE DOMAIN DEPT_DOM CHAR (10); SQL> CREATE DOMAIN MGR_DOM CHAR (20); SQL> CREATE DOMAIN NAME_DOM CHAR (20); SQL> -- SQL> CREATE TABLE WORK_STATION cont> (LOC LOC_DOM, cont> DEPT DEPT_DOM, cont> CONSTRAINT WS_LOC_DEPT_PRIMARY cont> PRIMARY KEY (LOC, DEPT), cont> MGR MGR_DOM); SQL> -- SQL> CREATE TABLE WORKER cont> (NAME NAME_DOM cont> CONSTRAINT WORKER_PRIMARY_NAME cont> PRIMARY KEY, cont> LOCATION LOC_DOM, cont> DEPARTMENT DEPT_DOM, cont> CONSTRAINT WORKER_FOREIGN_LOCATION_DEPT cont> FOREIGN KEY (LOCATION, DEPARTMENT) cont> REFERENCES WORK_STATION (LOC, DEPT)); SQL>
次の例では、従業員の履歴書を格納するためにLIST OF BYTE VARYINGデータ型の列を定義しています。この例では、履歴書は人事管理アプリケーションで実際に使用される従業員用としてのみ保存されるため、EMPLOYEES表のEMPLOYEE_ID列を外部キー制約として定義しています。アプリケーションでは、この表を使用して特別な経歴や能力を持つ従業員を特定し、仕事の割当てや昇進に活かすことができます。
SQL> CREATE DOMAIN RESUME_DOM LIST OF BYTE VARYING; SQL> CREATE TABLE RESUMES cont> (EMPLOYEE_ID ID_DOM cont> REFERENCES EMPLOYEES (EMPLOYEE_ID), cont> RESUME RESUME_DOM); SQL> SHOW TABLE RESUMES; Information for table RESUMES Columns for table RESUMES: Columns for table RESUMES: Column Name Data Type Domain ----------- --------- ------ EMPLOYEE_ID CHAR(5) ID_DOM Foreign Key constraint RESUMES_FOREIGN1 Unique constraint RESUMES_UNIQUE_EMPLOYEE_ID RESUME VARBYTE LIST RESUME_DOM Segment Length: 1 Table constraints for RESUMES: RESUMES_FOREIGN1 Foreign Key constraint Column constraint for RESUMES.EMPLOYEE_ID Evaluated on COMMIT Source: RESUMES.EMPLOYEE_ID REFERENCES EMPLOYEES (EMPLOYEE_ID) RESUMES_UNIQUE_EMPLOYEE_ID Unique constraint Column constraint for RESUMES.EMPLOYEE_ID Evaluated on COMMIT Source: RESUMES.EMPLOYEE_ID UNIQUE Constraints referencing table RESUMES: No constraints found Indexes on table RESUMES: No indexes found Storage Map for table RESUMES: RESUMES_MAP Triggers on table RESUMES: No triggers found SQL>
COMPUTED BY句では選択式を使用できます。次の例は、COMPUTED BY句を使用して特定部門の現在の従業員数をカウントする方法を示しています。
SQL> CREATE TABLE DEPTS1 cont> (DEPARTMENT_CODE DEPARTMENT_CODE_DOM, cont> DEPT_COUNT COMPUTED BY cont> (SELECT COUNT (*) FROM JOB_HISTORY JH cont> WHERE JOB_END IS NULL cont> AND cont> -- cont> -- Use correlation names to qualify the DEPARTMENT_CODE columns. cont> DEPTS1.DEPARTMENT_CODE = JH.DEPARTMENT_CODE), cont> DEPARTMENT_NAME DEPARTMENT_NAME_DOM) cont> ; SQL> SELECT * FROM DEPTS1 WHERE DEPARTMENT_CODE = 'ADMN'; DEPARTMENT_CODE DEPT_COUNT DEPARTMENT_NAME ADMN 7 Corporate Administration 1 row selected
例12: 列を定義するためにデータベースのデフォルト・キャラクタ・セット、各国語キャラクタ・セットおよび他のキャラクタ・セットを使用した表の作成
データベースのデフォルト・キャラクタ・セットをDEC_KANJI、各国語キャラクタ・セットをKANJIとして定義してデータベースが作成されていることを前提とします。
