Unused&Constraint

最新推荐文章于 2025-11-26 14:12:29 发布

转载最新推荐文章于 2025-11-26 14:12:29 发布 · 128 阅读

文章标签：

#数据库

本文详细阐述了在SQL环境中通过UNUSED命令移除特定列约束的过程，包括约束定义、实例操作及约束恢复后的状态变化，提供了从初始状态到最终状态的完整步骤说明。

1 constraints defined on the column would be removed by the UNUSED command：

SQL> desc LOCATIONS;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
LOCATION_ID                               NOT NULL NUMBER(4)
STREET_ADDRESS                                     VARCHAR2(40)
POSTAL_CODE                                        VARCHAR2(12)
CITY                                      NOT NULL VARCHAR2(30)
STATE_PROVINCE                                     VARCHAR2(25)
COUNTRY_ID                                         CHAR(2)

SQL> select count(*) from LOCATIONS;

COUNT(*)
----------
23

SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';

CONSTRAINT_NAME                C
------------------------------ -
LOC_CITY_NN                    C
LOC_ID_PK                      P
LOC_C_ID_FK                    R

SQL> select column_name from user_cons_columns
2 where constraint_name='LOC_CITY_NN';

COLUMN_NAME
--------------------------------------------------------------------------------
CITY

SQL> alter table hr.LOCATIONS set unused (CITY);

表已更改。

------------------

SQL> select object_id from dba_objects where object_name='LOCATIONS';

OBJECT_ID
----------
51900
52376

SQL>
SQL> select col#,segcol#,name,intcol# from col$ where obj#=51900;

      COL#    SEGCOL# NAME                              INTCOL#
---------- ---------- ------------------------------ ----------
         1          1 LOCATION_ID                             1
         2          2 STREET_ADDRESS                          2
         3          3 POSTAL_CODE                             3
         0          4 SYS_C00004_12031823:21:22$              4
         4          5 STATE_PROVINCE                          5
         5          6 COUNTRY_ID                              6

已选择6行。

--------------------------

SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';

CONSTRAINT_NAME                C
------------------------------ -
LOC_ID_PK                      P
LOC_C_ID_FK                    R

2 recovery:

SQL> select col#,segcol#,name,intcol# from col$ where obj#=51900;

已选择6行。
-------------------

SQL> UPDATE COL$ SET COL#=INTCOL# WHERE OBJ#=51900;

已更新6行。

SQL> UPDATE TAB$ SET COLS=COLS+1 WHERE OBJ#=51900;

已更新 1 行。

SQL> commti;
SP2-0042: 未知命令 "commti" - 其余行忽略。
SQL> commit;

提交完成。

SQL> select header_file,header_block from dba_segments where segment_name='LOCATIONS';

HEADER_FILE HEADER_BLOCK
----------- ------------
5 35

SQL> select col#,segcol#,name,intcol# from col$ where obj#=51900;

      COL#    SEGCOL# NAME                              INTCOL#
---------- ---------- ------------------------------ ----------
         1          1 LOCATION_ID                             1
         2          2 STREET_ADDRESS                          2
         3          3 POSTAL_CODE                             3
         4          4 SYS_C00004_12031823:21:22$              4
         5          5 STATE_PROVINCE                          5
         6          6 COUNTRY_ID                              6

已选择6行。

SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';

CONSTRAINT_NAME                C
------------------------------ -
LOC_ID_PK                      P
LOC_C_ID_FK                    R

---------before set unused:

SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';

CONSTRAINT_NAME                C
------------------------------ -
LOC_CITY_NN                    C
LOC_ID_PK                      P
LOC_C_ID_FK                    R

SQL> desc LOCATIONS;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
LOCATION_ID                               NOT NULL NUMBER(4)
STREET_ADDRESS                                     VARCHAR2(40)
POSTAL_CODE                                        VARCHAR2(12)
SP2-0642: SQL*Plus 内部错误状态 2131, 上下文 0:0:0
继续执行将不安全

continue:

SQL> update col$ set name='CITY' where obj#=51900 and SEGCOL#=4;

已更新 1 行。

SQL> update col$ set PROPERTY=0 where obj#=51900;

已更新6行。

SQL> commit;

提交完成。

SQL> select col#,segcol#,name,intcol# from col$ where obj#=51900;

      COL#    SEGCOL# NAME                              INTCOL#
---------- ---------- ------------------------------ ----------
         1          1 LOCATION_ID                             1
         2          2 STREET_ADDRESS                          2
         3          3 POSTAL_CODE                             3
         4          4 CITY                                    4
         5          5 STATE_PROVINCE                          5
         6          6 COUNTRY_ID                              6

已选择6行。

after bounced my oracle db:

SQL> desc LOCATIONS;
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
LOCATION_ID                               NOT NULL NUMBER(4)
STREET_ADDRESS                                     VARCHAR2(40)
POSTAL_CODE                                        VARCHAR2(12)
CITY                                               VARCHAR2(30)
STATE_PROVINCE                                     VARCHAR2(25)
COUNTRY_ID                                         CHAR(2)

SQL> select constraint_name,constraint_type from user_constraints
2 where table_name='LOCATIONS';

CONSTRAINT_NAME                C
------------------------------ -
LOC_ID_PK                      P
LOC_C_ID_FK                    R

after all,now This can be summarized as follows：

1 constraints defined on the column would be removed by the UNUSED

2 after update col$ constraints not recoveried;

------------end--------------

refence:

col$ base insert as follows：
Insert into SYS.COL$
   (OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
    NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
    SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
    PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
    SPARE3, SPARE4, SPARE5, SPARE6)
Values
   (16416, 1, 1, 22, 0,
    'X', 2, 22, 0, NULL,
    NULL, 0, NULL, NULL, 1,
    0, 0, 0, 0, 0,
    0, NULL, NULL, NULL);
Insert into SYS.COL$
   (OBJ#, COL#, SEGCOL#, SEGCOLLENGTH, OFFSET,
    NAME, TYPE#, LENGTH, FIXEDSTORAGE, PRECISION#,
    SCALE, NULL$, DEFLENGTH, DEFAULT$, INTCOL#,
    PROPERTY, CHARSETID, CHARSETFORM, SPARE1, SPARE2,
    SPARE3, SPARE4, SPARE5, SPARE6)
Values
   (16416, 2, 2, 22, 0,
    'Y', 2, 22, 0, NULL,
    NULL, 0, NULL, NULL, 2,
    0, 0, 0, 0, 0,
    0, NULL, NULL, NULL);