반응형
PK는 Primary Key라고 불리며 기본키로 번역되서 불립니다.
특징으로는 테이블 컬럼 내의 유일한 값(Unique)과 빈 값이 없는(Not Null) 특징을 가지고 있습니다.
즉, 각 값들의 특징을 구분할 수 있는 유일한 값이 PK의 값이 되는것이죠.
예를들어 고객의 테이블에서 주민등록번호는 PK가 될 수 있습니다. 한 사람당 유일한 값을 가지는 값이기 때문입니다.
그리고 PK 설정시 자동적으로 Unique Index를 생성하여 줍니다. Oracle과 Tibero의 경우 PK와 같은 컬럼의 Unique Index가 존재 시 따로 생성하지 않고 기존에 있는 Unique Index를 사용합니다.
Mysql의 경우에는 이미 있더라도 PK설정시 따로 Unique Index를 생성합니다.
아래의 테스트를 통하여 PK 컬럼추가에 대한 테스트를 실습해보겠습니다.
PK는 alter table modify 명령어로 수정할 수 없습니다. 따라서 삭제 후 재생성을 해주어야 합니다.(Drop&Add)
공통 시나리오
1.기존의 EMP 테이블 데이터를 활용한 테이블을 생성
2.2개의 컬럼을 포함하는 UNIQUE INDEX와 PK를 생성(EMPNO,ENAME)
3.4개의 컬럼을 포함하는 PK로 재생성
Oracle
PK 재생성시에 중요한것은 안의 데이터값이 PK의 성질을 만족하느냐 확인하는 것입니다.
그리고 dba_constraints 뷰를 확인해보면 처음에는 동일한 컬럼의 Unique Index인 IX_EMP_TEST를 참조하는 것을 확인할 수 있지만, 두번째 재성시에는 ,별도의 Unique Index PK_EMP_TEST가 새로 생성되어 그것을 참조하는 것을 확인할 수 있습니다. 따라서 기존에 생성된 Index는 새로 생성된 Index에 포함되므로 삭제해줍니다. (반드시 삭제해야하는 것이 아닌, 필요가 없어짐에 따라 삭제하는 것)
1.테이블 생성
SQL>CREATE TABLE EMP_TEST
as select * from EMP;
확인
SQL> select * from EMP_TEST;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
7369 SMITH CLERK 7902 2020/11/19 20:08:48 800 20
7499 ALLEN SALESMAN 7698 2020/11/19 20:08:48 1600 300 30
7521 WARD SALESMAN 7698 2020/11/19 20:08:48 1250 500 30
7566 JONES MANAGER 7839 2020/11/19 20:08:48 2975 20
7654 MARTIN SALESMAN 7698 2020/11/19 20:08:48 1250 1400 30
7698 BLAKE MANAGER 7839 2020/11/19 20:08:48 2850 30
7782 CLARK MANAGER 7839 2020/11/19 20:08:48 2450 10
7788 SCOTT ANALYST 7566 2020/11/19 20:08:48 3000 20
7839 KING PRESIDENT 2020/11/19 20:08:48 5000 10
7844 TURNER SALESMAN 7698 2020/11/19 20:08:48 1500 0 30
7876 ADAMS CLERK 7788 2020/11/19 20:08:48 1100 20
7900 JAMES CLERK 7698 2020/11/19 20:08:48 950 30
7902 FORD ANALYST 7566 2020/11/19 20:08:48 3000 20
7934 MILLER CLERK 7782 2020/11/19 20:08:48 1300 10
14 rows selected.
2.UNIQUE INDEX,PK 생성
SQL>CREATE UNIQUE INDEX TEST.IX_EMP_TEST
ON TEST.EMP_TEST
(EMPNO,ENAME)
;
SQL>alter table TEST.EMP_TEST add constraint PK_EMP_TEST primary key(EMPNO,ENAME);
3.생성된 UNIQUE INDEX,PK 확인
SQL> select * from dba_constraints
where owner='TEST'
and table_name='EMP_TEST';
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION R_OWNER R_CONSTRAINT_NAME DELETE_RU STATUS DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY LAST_CHANGE INDEX_OWNER INDEX_NAME INVALID VIEW_RELATED
------------------------------------------------------------------------------------------------------------------------ ------------------------------ - ------------------------------ -------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ ------------------------------ --------- -------- -------------- --------- ------------- -------------- --- ---- ------------------- ------------------------------ ------------------------------ ------- --------------
TEST PK_EMP_TEST P EMP_TEST ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 2021/04/04 22:39:55 TEST IX_EMP_TEST
TEST SYS_C009332 C EMP_TEST "EMPNO" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 2021/04/04 22:35:21
2 rows selected.
4.PK 재생성
SQL>alter table TEST.EMP_TEST drop primary key;
SQL>alter table TEST.EMP_TEST add constraint PK_EMP_TEST primary key(EMPNO,ENAME,JOB,MGR);
##에러발생
ORA-01449 : column contains NULL values; cannot alter to NOT NULL
원인 : 컬럼 중 NULL 값 이 있어서 그럼
해결 : NULL 값 변경 후 다시 시도
SQL>select * from EMP_TEST
where empno is null
or ename is null
or job is null
or mgr is null
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEP
----- ---------- --------- ----- ------------------- --------- --------- ---
7839 KING PRESIDENT 2020/11/19 20:08:48 5000 10
1 rows selected.
Null값을 0으로 변경
SQL>update EMP_TEST
set mgr=0000
where mgr is null;
SQL>commit;
다시시도
SQL>alter table TEST.EMP_TEST add constraint PK_EMP_TEST primary key(EMPNO,ENAME,JOB,MGR);
성공
5.확인
SQL> select owner,index_name,table_owner,table_name,uniqueness from dba_indexes
where owner='TEST'
and table_name='EMP_TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME UNIQUENES
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------
TEST IX_EMP_TEST TEST EMP_TEST UNIQUE
TEST PK_EMP_TEST TEST EMP_TEST UNIQUE
2 rows selected.
SQL> select owner,constraint_name,constraint_type,table_name,search_condition,index_owner,index_name from dba_constraints
where owner='TEST'
and table_name='EMP_TEST';
OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION INDEX_OWNER INDEX_NAME
------------------------------------------------------------------------------------------------------------------------ ------------------------------ - ------------------------------ -------------------------------------------------------------------------------- ------------------------------ ------------------------------
TEST PK_EMP_TEST P EMP_TEST TEST PK_EMP_TEST
TEST SYS_C009332 C EMP_TEST "EMPNO" IS NOT NULL
2 rows selected.
기존에 생성한 Unique index 삭제
SQL>drop index test.IX_EMP_TEST
확인
SQL> select owner,index_name,table_owner,table_name,uniqueness from dba_indexes
where owner='TEST'
and table_name='EMP_TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME UNIQUENES
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------
TEST PK_EMP_TEST TEST EMP_TEST UNIQUE
1 rows selected.
Tibero
Tibero는 Oracle과 동일하게 진행되며 결과도 동일합니다.
1.생성 후 확인
SQL>CREATE TABLE EMP_TEST
as select * from EMP;
SQL> select * from EMP_TEST;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- -------------------------- --------- --------- ------
7369 SMITH CLERK 7902 2020/08/05 04:38:24.000000 800 20
7499 ALLEN SALESMAN 7698 2020/08/05 04:38:24.000000 1600 300 30
7521 WARD SALESMAN 7698 2020/08/05 04:38:24.000000 1250 500 30
7566 JONES MANAGER 7839 2020/08/05 04:38:24.000000 2975 20
7654 MARTIN SALESMAN 7698 2020/08/05 04:38:24.000000 1250 1400 30
7698 BLAKE MANAGER 7839 2020/08/05 04:38:24.000000 2850 30
7782 CLARK MANAGER 7839 2020/08/05 04:38:24.000000 2450 10
7788 SCOTT ANALYST 7566 2020/08/05 04:38:24.000000 4444 20
7839 KING PRESIDENT 2020/08/05 04:38:24.000000 5000 10
7844 TURNER SALESMAN 7698 2020/08/05 04:38:24.000000 1500 0 30
7876 ADAMS CLERK 7788 2020/08/05 04:38:24.000000 1100 20
7900 JAMES CLERK 7698 2020/08/05 04:38:24.000000 950 30
7902 FORD ANALYST 7566 2020/08/05 04:38:24.000000 3000 20
7934 MILLER CLERK 7782 2020/08/05 04:38:24.000000 1300 10
14 rows selected.
SQL Execution Time > 00:00:00.016
2.Unique Index/PK 생성
SQL>CREATE UNIQUE INDEX TEST.IX_EMP_TEST
ON TEST.EMP_TEST
(EMPNO,ENAME)
;
SQL>alter table TEST.EMP_TEST add constraint PK_EMP_TEST primary key(EMPNO,ENAME);
확인
SQL> select index_owner,index_name,table_owner,table_name,column_name from dba_ind_columns
where index_owner='TEST'
and table_name='EMP_TEST';
INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME COLUMN_NAME
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
TEST IX_EMP_TEST TEST EMP_TEST EMPNO
TEST IX_EMP_TEST TEST EMP_TEST ENAME
2 rows selected.
SQL> select owner,index_name,table_owner,table_name,uniqueness from dba_indexes
where owner='TEST'
and table_name='EMP_TEST';
OWNER INDEX_NAME TABLE_OWNER TABLE_NAME UNIQUENESS
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------
TEST IX_EMP_TEST TEST EMP_TEST UNIQUE
1 rows selected.
SQL>select owner,constraint_name,constraint_type,table_name,search_condition,index_owner,index_name from dba_constraints
where owner='TEST'
and table_name='EMP_TEST';
TEST PK_EMP_TEST P EMP_TEST TEST IX_EMP_TEST
TEST TEST_CON70500464 C EMP_TEST "EMPNO" IS NOT NULL
3.PK 재생성
SQL>alter table TEST.EMP_TEST drop primary key;
SQL>alter table TEST.EMP_TEST add constraint PK_EMP_TEST primary key(EMPNO,ENAME,JOB,MGR);
Error 7045 A column contains a NULL value : cannot create or enable the constraint
Oracle과 마찬가지로 Null값이 들어있어서 PK 생성 할 수 없다는 에러가 발생
Null 값을 0으로 변경
SQL>select * from EMP_TEST
where empno is null
or ename is null
or job is null
or mgr is null
7839 KING PRESIDENT 2020/08/05 04:38:24.000000 5000 10
SQL>update EMP_TEST
set mgr=0000
where mgr is null;
SQL>commit;
PK 재생성
SQL>alter table TEST.EMP_TEST add constraint PK_EMP_TEST primary key(EMPNO,ENAME,JOB,MGR);
확인
SQL>select index_owner,index_name,table_owner,table_name,column_name from dba_ind_columns
where index_owner='TEST'
and table_name='EMP_TEST'
order by 2
TEST IX_EMP_TEST TEST EMP_TEST EMPNO
TEST IX_EMP_TEST TEST EMP_TEST ENAME
TEST PK_EMP_TEST TEST EMP_TEST EMPNO
TEST PK_EMP_TEST TEST EMP_TEST ENAME
TEST PK_EMP_TEST TEST EMP_TEST JOB
TEST PK_EMP_TEST TEST EMP_TEST MGR
SQL>select owner,index_name,table_owner,table_name,uniqueness from dba_indexes
where owner='TEST'
and table_name='EMP_TEST';
TEST IX_EMP_TEST TEST EMP_TEST UNIQUE
TEST PK_EMP_TEST TEST EMP_TEST UNIQUE
SQL>select owner,constraint_name,constraint_type,table_name,search_condition,index_owner,index_name from dba_constraints
where owner='TEST'
and table_name='EMP_TEST';
TEST PK_EMP_TEST P EMP_TEST TEST PK_EMP_TEST
TEST TEST_CON70500464 C EMP_TEST "EMPNO" IS NOT NULL
Oracle과 마찬가지로 IN_EMP_TEST가 아닌 PK_EMP_TEST를 참조하는 PK가 생성되었음
기존의 unique index 삭제
SQL>drop index test.IX_EMP_TEST
SQL>select owner,index_name,table_owner,table_name,uniqueness from dba_indexes
where owner='TEST'
and table_name='EMP_TEST';
TEST PK_EMP_TEST TEST EMP_TEST UNIQUE
끝
Mysql
Mysql의 경우에는 위의 두 DB와 다르게 처음부터 Unique index와는 별개로 PK를 참조하는 Unique Index가 생성됩니다.
다른 DB와 마찬가지로 삭제 후 재생성(Drop&Add)로 PK를 다시 설정할 수 있습니다.
1.생성 및 확인
Mysql Workbench의 Table data import wizard를 이용해서 테이블 생성과 데이터삽입을 수행하였습니다.
mysql은 데이터 삽입 시부터 MGR이 0인 데이터는 삽입이 되지 않아서 0으로 임의의 값을 넣은 후 진행하였습니다.
select * From test.EMP2;
7369 SMITH CLERK 7902 2020/11/19 20:08:48 800 20
7499 ALLEN SALESMAN 7698 2020/11/19 20:08:48 1600 300 30
7521 WARD SALESMAN 7698 2020/11/19 20:08:48 1250 500 30
7566 JONES MANAGER 7839 2020/11/19 20:08:48 2975 20
7654 MARTIN SALESMAN 7698 2020/11/19 20:08:48 1250 1400 30
7698 BLAKE MANAGER 7839 2020/11/19 20:08:48 2850 30
7782 CLARK MANAGER 7839 2020/11/19 20:08:48 2450 10
7788 SCOTT ANALYST 7566 2020/11/19 20:08:48 3000 20
7844 TURNER SALESMAN 7698 2020/11/19 20:08:48 1500 0 30
7876 ADAMS CLERK 7788 2020/11/19 20:08:48 1100 20
7900 JAMES CLERK 7698 2020/11/19 20:08:48 950 30
7902 FORD ANALYST 7566 2020/11/19 20:08:48 3000 20
7934 MILLER CLERK 7782 2020/11/19 20:08:48 1300 10
7839 KING PRESIDENT 0 2020/11/19 20:08:48 5000 10
2.확인
컬럼 확인
mysql> SHOW FULL COLUMNS FROM test.EMP2;
+----------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+----------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| EMPNO | int | NULL | YES | | NULL | | select,insert,update,references | |
| ENAME | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| JOB | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| MGR | int | NULL | YES | | NULL | | select,insert,update,references | |
| HIREDATE | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| SAL | int | NULL | YES | | NULL | | select,insert,update,references | |
| COMM | text | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| DEPTNO | int | NULL | YES | | NULL | | select,insert,update,references | |
+----------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+
8 rows in set (0.01 sec)
인덱스 확인
mysql> show index from test.EMP2;
Empty set (0.00 sec)
PK 제약조건 확인
mysql> mysql> select * from information_schema.table_constraints where table_name='EMP2';
Empty set (0.00 sec)
3.Unique INdex 및 PK 추가
mysql> create unique index IX_EMP2 on test.EMP2 (EMPNO,ENAME);
ERROR 1170 (42000): BLOB/TEXT column 'ENAME' used in key specification without a key length
원인 BLOB/TEXT형식 속성은 Key로 사용할 수 없음
해결 ENAME,JOB을 VARCHAR로 변경하고 다시 시도
alter table test.EMP2 modify column ENAME varchar(10) NOT NULL;
alter table test.EMP2 modify column JOB varchar(9) NOT NULL;
재생성
mysql> create unique index IX_EMP2 on test.EMP2 (EMPNO,ENAME);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test.EMP2 add constraint PK_EMP2 primary key(EMPNO,ENAME);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
확인
mysql> show index from test.EMP2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| EMP2 | 0 | PRIMARY | 1 | EMPNO | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | PRIMARY | 2 | ENAME | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | IX_EMP2 | 1 | EMPNO | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | IX_EMP2 | 2 | ENAME | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
mysql> select * from information_schema.table_constraints where table_name='EMP2';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | test | IX_EMP2 | test | EMP2 | UNIQUE | YES |
| def | test | PRIMARY | test | EMP2 | PRIMARY KEY | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
2 rows in set (0.00 sec)
Table_constraints 테이블을 확인해보면 IX_EMP2와 상관없이 PRIMARY이름으로 Unique Index가 생성되는것을 확인할 수 있음
4.재생성
mysql> alter table test.EMP2 drop primary key;
Query OK, 14 rows affected (0.04 sec)
Records: 14 Duplicates: 0 Warnings: 0
삭제 확인
mysql> select * from information_schema.table_constraints where table_name='EMP2';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | test | IX_EMP2 | test | EMP2 | UNIQUE | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
1 row in set (0.00 sec)
mysql> show index from test.EMP2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| EMP2 | 0 | IX_EMP2 | 1 | EMPNO | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | IX_EMP2 | 2 | ENAME | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)
추가
mysql> alter table test.EMP2 add constraint PK_EMP2 primary key(EMPNO,ENAME,JOB,MGR);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
확인
mysql> show index from test.EMP2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| EMP2 | 0 | PRIMARY | 1 | EMPNO | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | PRIMARY | 2 | ENAME | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | PRIMARY | 3 | JOB | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | PRIMARY | 4 | MGR | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | IX_EMP2 | 1 | EMPNO | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
| EMP2 | 0 | IX_EMP2 | 2 | ENAME | A | 14 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.00 sec)
mysql> select * from information_schema.table_constraints where table_name='EMP2';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def | test | IX_EMP2 | test | EMP2 | UNIQUE | YES |
| def | test | PRIMARY | test | EMP2 | PRIMARY KEY | YES |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
2 rows in set (0.00 sec)반응형
'Oracle > 운영' 카테고리의 다른 글
| maxdatafile 갯수 초과 시 컨트롤 파일 재생성 유/무 테스트(Tibero6 포함) (0) | 2021.06.07 |
|---|---|
| v$version - Oracle 버전&Edition 확인 (0) | 2021.04.19 |
| dba_2pc_pending (0) | 2021.03.11 |
| Two-Phase Commit 매커니즘 (0) | 2021.03.10 |
| Index rebuild (0) | 2021.02.16 |
댓글