Defining and Modifying チェック制約

www.altova.com このトピックを印刷 前のページ 1つ上のレベル 次のページ

ホーム >  データベース構造の探索と変更 > デザインエディター内でのテーブルのデザイン >

Defining and Modifying チェック制約

チェック制約 can be defined both on table and on column level and make sure that only valid data is entered into a specific column of a table. If the condition is not met, the 個々の column cannot be updated and new rows cannot be added (下のスクリーンショットを参照してください)。 DatabaseSpy outputs a corresponding message in the Message tab.

app_output-check-constraint-failed

メモ:Adding チェック制約 is not supported for the following databases: MariaDB, MySQL, SQLite.

 

 

To define a check constraint in デザインエディター ウィンドウ:

1.次のいずれかを実行してください:

 

テーブルを右クリックし、 「新規の挿入 | Check Constraint をコンテキストメニューから選択します。
Expand the チェック制約 section by clicking the triangle at the right edge of the section header and click the Create new Check Constraint ic_add-column アイコン in the Check Constraint column.

 

A new check constraint is added to the "チェック制約" section and highlighted for editing. デフォルトでは、 the name of the 外部キー is <generated>, which means its name will be automatically generated by the database. This ensures uniqueness of the name in the database. If necessary, you can override 生成された名前. To do this, enter a descriptive name in place of <generated>, and press Enter.

If you override 生成された名前 with a custom name, the new name must be unique; otherwise, errors will occur when the database change script is executed.

2.In the "Expression" column, enter the expression that is to be checked when data is committed to the database and press Enter.

dbs_chk_constraint

As always when making changes to the database structure, a change script is generated which must be executed in order to actually create the check constraint in the database. DatabaseSpy performs a validation of the expression that will be used for the check constraint. While you are defining a check constraint in デザインエディター ウィンドウ, the データベース構造変更スクリプト window displays comments if the expression is invalid.

cs_CK-invalid-expression

The following issues are validated and corresponding messages are displayed in the change script:

 

Missing expression
Invalid identifier
Textual or numeric constant not matching column データ型
Column データ型 not matching
Invalid expression
"IS NOT NULL" clause applied to a nullable column

 

Viewing チェック制約

デザインエディター ウィンドウ内で、 チェック制約 on table level are displayed in a separate section where they can be edited and where you can add additional or delete unneeded チェック制約.

design_check-constraints

Columns that have a check constraint assigned are marked with the check constraint ic_check-column アイコン in デザインエディター ウィンドウ and the オンラインブラウザー. チェック制約 on column level can be edited in プロパティウィンドウ of the column to which they are applied. Please note that they do not show up in the チェック制約 section of the table. To see the name and definition of this column's constraint, you have to display the column properties in プロパティウィンドウ.

 

オンラインブラウザー内で、 チェック制約 appear in the constraints sub-folder of the table or column, depending on whether the constraint has been defined on table or column level.

 

チェック制約 on table level appear in the Constraints folder of the table they are assigned to.

ob_check-constraint-table

チェック制約 on column level are displayed under the Constraints folder of the column. Note that no Constraints folder on table level is available in this case.

ob_check-constraint-col

 

Modifying チェック制約

You can rename, delete or change the expression of a check constraint. Note that the check constraint is always deleted first and then recreated with the new definition. The 個々の SQL statement is generated automatically in the データベース構造変更スクリプト window if you modify the check definition in デザインエディター ウィンドウ.

 

When you rename a column that is used in a check constraint expression, DatabaseSpy automatically updates the check constraint in the change script that is used to rename that column.

cs_alter-CK

If a column is deleted, any チェック制約 that reference this column are dropped as well.

 

Adding チェック制約 using SQL

If チェック制約 already exist in a table, you can add new constraints to the table also by right-clicking an existing check constraint and generating a statement that uses the properties of the existing item as default. After editing the properties as required and executing the SQL, a new check constraint is added to the database.

 

 

To add a check constraint to a table using SQL:

1.Connect to the database and show the 個々の data source in the オンラインブラウザー.
2.Expand the table to which you want to add a check constraint and right-click an existing constraint in the Constraints folder.
3.「新規の SQL エディターで表示 | Add をコンテキストメニューから選択、または、holding down the right mouse ボタン, drag the constraint into an open SQL エディターウィンドウ and choose Add from the popup. An ALTER TABLE statement is generated in the SQL エディターウィンドウ.
4.Edit the statement as follows:

 

a)Enter the path of the table that is to be checked after the ALTER TABLE part of the statement.
b)Type the name of the new constraint after the ADD CONSTRAINT part of the statement.
c)Edit the CHECK (Expression) section; enter the expression that is to be checked when the table is updated.

 

5. Click the 実行 ic_execute-sql ボタン to execute the statement and to add the new check constraint to the database.
6.オンラインブラウザー内で、 right-click the database and choose Refresh をコンテキストメニューから選択します。

(C) 2019 Altova GmbH