This is generally used as an identification code, as the primary key to define the table. Generated syntax can customize the strategy you want to generate this value.
The syntax is as follows:
column definition generated {always | by default}
as {identity identity rules | using your rules}
Let's first delete the table we created last time:
db2 => drop table nomination
Then create a table:
Create table nomination
(
nominationID BIGINT Not Null Primary Key generated always as identity,
nominee char(6) Not Null,
nominator char(6) Not Null,
reason VARCHAR(250),
nomdate date Not Null,
categoryid INTEGER Not Null,
check (nominee != nominator) not enforced enable query optimization,
Foreign Key CategoryExists (categoryid)
references category (categoryid) on delete restrict
)
Pay attention to bold characters, we cannot use insert or update to explicitly specify its value in the future.
The identity in DB2 also provides a variety of strategies. For specific purposes, you can check the DB2 manual. Let's give the following example:
Let's first delete the table we created last time:
db2 => drop table category
Then create the form
Create table category
(
CategoryID INTEGER Primary Key Generated Always as Identity
(Start With 1 Increment by 1 minvalue 0 maxvalue 999999999
no cycle cache 5 no order),
CateogryName VARCHAR(50) Not Null,
Eligibility VARCHAR(250)
)
You can find all the statements in identity in bold characters in DB2's manual, and they are all understood in natural language at a glance.
Sometimes you don’t just want to fill numbers, you may also want to process some letters, so the following example of converting capitals is for you:
db2 => alter table category add column
UpperCatName VARCHAR(50) generated always as (upper(CategoryName))
These are specified in the DB2 documentation.
The syntax is as follows:
column definition generated {always | by default}
as {identity identity rules | using your rules}
Let's first delete the table we created last time:
db2 => drop table nomination
Then create a table:
Copy the codeThe code is as follows:
Create table nomination
(
nominationID BIGINT Not Null Primary Key generated always as identity,
nominee char(6) Not Null,
nominator char(6) Not Null,
reason VARCHAR(250),
nomdate date Not Null,
categoryid INTEGER Not Null,
check (nominee != nominator) not enforced enable query optimization,
Foreign Key CategoryExists (categoryid)
references category (categoryid) on delete restrict
)
Pay attention to bold characters, we cannot use insert or update to explicitly specify its value in the future.
The identity in DB2 also provides a variety of strategies. For specific purposes, you can check the DB2 manual. Let's give the following example:
Let's first delete the table we created last time:
db2 => drop table category
Then create the form
Copy the codeThe code is as follows:
Create table category
(
CategoryID INTEGER Primary Key Generated Always as Identity
(Start With 1 Increment by 1 minvalue 0 maxvalue 999999999
no cycle cache 5 no order),
CateogryName VARCHAR(50) Not Null,
Eligibility VARCHAR(250)
)
You can find all the statements in identity in bold characters in DB2's manual, and they are all understood in natural language at a glance.
Sometimes you don’t just want to fill numbers, you may also want to process some letters, so the following example of converting capitals is for you:
db2 => alter table category add column
UpperCatName VARCHAR(50) generated always as (upper(CategoryName))
These are specified in the DB2 documentation.