SQL
NOT NULL 제약
에어팟맥스
2022. 7. 15. 17:43
-- tbl_new_member 테이블의 name 컬럼에 있는 NOT NULL 제약 제거하기
alter table tbl_new_member
modify name null;
-- Table TBL_NEW_MEMBER이(가) 변경되었습니다.
-- tbl_new_member 테이블의 name 컬럼에 있는 NOT NULL 제약 추가하기
alter table tbl_new_member
modify name not null;
-- Table TBL_NEW_MEMBER이(가) 변경되었습니다.
-- tbl_new_member 테이블의 mobile 컬럼에 있는 NOT NULL 제약 추가하기
alter table tbl_new_member
modify mobile not null;
-- ORA-02296: (HR.) 사용으로 설정 불가 - 널 값이 발견되었습니다.
-- mobile 컬럼에 null 값이 있는 행이 있기 때문에 변경 불가
select count(*)
from tbl_new_member
where mobile is null;
-- 1
------- ***** 제약조건 (Constraint) 조회하기 ***** -------
select *
from user_constraints
where table_name = 'TBL_NEW_MEMBER';
-- P : primary key
-- U : unique
-- C : check, not null
select *
from user_constraints
where table_name = 'TBL_NEW_BOARD';
-- R : reference key(foreign key)
select *
from user_cons_columns
where table_name = 'TBL_NEW_MEMBER';
----- TBL_NEW_MEMBER 테이블에 생성된 제약조건 조회하기 -----
select A.constraint_name, A.constraint_type, A.search_condition, A.r_constraint_name,
A.status, A.index_name, B.column_name, B.position
from user_constraints A JOIN user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = 'TBL_NEW_MEMBER';
----- TBL_NEW_BOARD 테이블에 생성된 제약 foreign key 조건 조회하기 -----
select A.constraint_name, A.constraint_type, A.search_condition, A.r_constraint_name,
A.status, A.index_name, B.column_name, B.position
from user_constraints A JOIN user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = 'TBL_NEW_BOARD' and constraint_type = 'R';
select *
from user_constraints A JOIN user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = 'TBL_NEW_BOARD';
select *
from user_cons_columns;
select *
from user_constraints;
----- TBL_NEW_BOARD 테이블에 생성된 foreign key 제약조건 조회하기 -----
----- 제약조건명 foreign key컬럼명 부모테이블명 부모테이블의컬럼명 -----
-- FK_TBL_NEW_BOARD_FK_MEMBER_ID FK_MEMBER_ID TBL_NEW_MEMBER MEMBER_ID
[쌤풀이]
with
V1 as (
select A.table_name as 자식테이블명
, A.constraint_name as 제약조건명
, B.column_name as 포린키컬럼명
, A.r_constraint_name
from user_constraints A JOIN user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = 'TBL_NEW_BOARD' and A.constraint_type = 'R'
)
,
V2 as
(
select constraint_name, table_name, column_name
from user_cons_columns
where constraint_name = ( select r_constraint_name
from user_constraints
where table_name = 'TBL_NEW_BOARD' and constraint_type = 'R' )
)
select 자식테이블명, 제약조건명, 포린키컬럼명,
table_name as 부모테이블명,
column_name as 부모테이블의컬럼명
from V1 JOIN V2
on V1.r_constraint_name = V2.constraint_name;
[찬안이 풀이]
with
V1 as
(select A.table_name tbn, A.constraint_name csn, A.r_constraint_name rcsn,
B.column_name cln
from user_constraints A JOIN user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = 'TBL_NEW_BOARD'
),
V2 as
(select constraint_name csn, column_name cln, table_name tbn
from user_cons_columns
)
select V1.tbn 자식테이블명, V1.csn 제약조건명, V1.cln 포린키컬럼명, V2.tbn 부모테이블명, V2.cln 부모테이블컬럼명
from
V1 join V2
on V1.rcsn = V2.csn;
---- ----- TBL_NEW_COMMENT 테이블에 생성된 foreign key 제약조건 조회하기 -----
with
V1 as (
select A.table_name as 자식테이블명
, A.constraint_name as 제약조건명
, B.column_name as 포린키컬럼명
, A.r_constraint_name
from user_constraints A JOIN user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = 'TBL_NEW_COMMENT' and A.constraint_type = 'R'
)
,
V2 as
(
select constraint_name, table_name, column_name
from user_cons_columns
where constraint_name IN ( select r_constraint_name
from user_constraints
where table_name = 'TBL_NEW_COMMENT' and constraint_type = 'R' )
)
select 자식테이블명, 제약조건명, 포린키컬럼명,
table_name as 부모테이블명,
column_name as 부모테이블의컬럼명
from V1 JOIN V2
on V1.r_constraint_name = V2.constraint_name;
foreign key 제약조건 조회하는 프로시저 생성하기
create or replace procedure pcd_search_fk
(p_table_name IN varchar2)
is
cursor cur_name
is
WITH
V1 as
(
select A.table_name
, A.constraint_name
, B.column_name
, A.r_constraint_name
from user_constraints A join user_cons_columns B
on A.constraint_name = B.constraint_name
where A.table_name = p_table_name and A.constraint_type = 'R'
)
,
V2 as
(
select constraint_name, table_name, column_name
from user_cons_columns
where constraint_name in ( select r_constraint_name
from user_constraints
where table_name = p_table_name and constraint_type = 'R' )
)
select V1.table_name AS CHILD_TABLE_NAME
, V1.constraint_name
, V1.column_name AS FK_COLUMN_NAME
, V2.table_name AS PARENT_TABLE_NAME
, V2.column_name AS PARENT_COLUMN_NAME
from V1 join V2
on V1.r_constraint_name = V2.constraint_name;
begin
for v_rcd in cur_name loop
if cur_name%rowcount = 1 then
dbms_output.put_line( lpad('-',60,'-') );
dbms_output.put_line( '자식테이블명 제약조건명 포린키컬럼명 부모테이블명 부모테이블의컬럼명' );
dbms_output.put_line( lpad('-',60,'-') );
end if;
dbms_output.put_line( v_rcd.CHILD_TABLE_NAME || ' ' ||
v_rcd.constraint_name || ' ' ||
v_rcd.FK_COLUMN_NAME || ' ' ||
v_rcd.PARENT_TABLE_NAME || ' ' ||
v_rcd.PARENT_COLUMN_NAME );
end loop;
end pcd_search_fk;
exec pcd_search_fk('TBL_NEW_COMMENT');
exec pcd_search_fk('TBL_NEW_BOARD');
/*
------------------------------------------------------------
자식테이블명 제약조건명 포린키컬럼명 부모테이블명 부모테이블의컬럼명
------------------------------------------------------------
TBL_NEW_BOARD FK_TBL_NEW_BOARD_FK_MEMBER_ID FK_MEMBER_ID TBL_NEW_MEMBER MEMBER_ID
*/