ALWAYS
If you specify ALWAYS, then Oracle Database always uses the sequence generator to assign avalue to the column.
If you attempt to explicitly assign a value to the column using INSERT orUPDATE, then an error will be returned.
This is the default.
BY DEFAULT
If you specify BY DEFAULT, then Oracle Database uses the sequence generator to assign avalue to the column by default,
but you can also explicitly assign a specified value to thecolumn.
If you specify ON NULL, then Oracle Database uses the sequence generator to assigna value to the column when a subsequent INSERT statement attempts to assign a value thatevaluates to NULL.
identity_options
Use the identity_options clause to configure the sequence generator. Theidentity_options clause has the same parameters as the CREATE SEQUENCE statement.
Refer to CREATE SEQUENCE for a full description of these parameters and characteristics.
The exception is START WITH LIMIT VALUE, which is specific to identity_options and canonly be used with ALTER TABLE MODIFY.
Refer to identity_options for more information.
下面举一个例子：
alter table employees add (empno number GENERATED AS IDENTITY);
Table altered.
HR@pdbprod1> desc employees
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
EMPNO NOT NULL NUMBER
SQL> select employee_id,last_name,email,hire_date,job_id,empno from employees
HR@pdbprod1> insert into employees (employee_id,last_name,email,hire_date,job_id,EMPNO)
2 values(300,'test1','test1',sysdate,'SA_REP',109);
insert into employees (employee_id,last_name,email,hire_date,job_id,EMPNO)
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2024-3-2 17:24:42
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对