Creating Identifier Columns

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

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

Creating Identifier Columns

Some databases provide a special column kind which is known as "identifier", or "auto-increment" column. When a column is defined as an identity column, the database will automatically generate a unique, incremental value for this column whenever a new row is inserted into the table. Support for identifier columns varies by database.

 

メモ:デザインエディター ウィンドウ does not support defining identifier columns for the following database types: Firebird, Informix, Oracle, PostgreSQL, Progress OpenEdge, Teradata, and SQLite.

 

When supported by the database, a property called Auto Increment (or Identity, in some databases) becomes available in プロパティウィンドウ when you click the column in デザインエディター ウィンドウ (provided that the column is of numeric type, see "Prerequisites" below). Databases such as IBM DB2 and SQL Server also let you specify the Seed and Increment properties, in addition to the Identity property.

ds_identity_column_01

Properties window (SQL Server database)

Prerequisites:

 

Typically, you can create identity columns at the table creation time, not later. Some databases (Microsoft Access, Microsoft SQL Server) let you specify a column as identity when you add a new column to an existing table.
The column must not be nullable. In other words, the Nullable チェックボックス must not be selected in プロパティウィンドウ (or in the table design).
The column must have a numeric type (such as INT, INTEGER, SMALLINT, BIGINT, or even DECIMAL with a scale of 0). Other numeric types may qualify, depending on the database.

 

 

To create an identity column:

1.In デザインエディター ウィンドウ or オンラインブラウザー内で、 select the column you want to use an as identity column.
2.プロパティウィンドウ内で、 select the Identity チェックボックス (or the Auto Increment チェックボックス, depending on the database).

 

メモ:In Microsoft Access, the column データ型 will be automatically converted to COUNTER when you select the Auto Increment チェックボックス. This データ型 is specific to Microsoft Access, it ensures that the value will be auto-incremented.

 

3.適応できる場合、 change the デフォルトの値 in the Seed and Increment fields. Seed specifies the initial value that will be generated by the database while Increment specifies by how much the initial value should be incremented on each insert. Normally, these values are 1 and 1, 個々のly.

 

As always when making changes to the database structure, a change script is generated which must be executed in order to actually create the identifier column in the database. DatabaseSpy performs a validation of the column definition that will be used for the identifier column. The image below illustrates a script where validation has failed because the column is nullable and not of numeric type:

ds_identity_column_02

A valid script, on the other hand, could look like:

ds_identity_column_03

If the script is valid and you wish to commit the changes to the database, click 実行 dbs_ic_execute_script  in the データベース構造変更スクリプト window.


(C) 2019 Altova GmbH