티스토리 뷰
아래의 조건에 맞는 쿼리문을 작성하시오.
1. parent 테이블 작성
create table parent (
ID VARCHAR2(20) primary key,
PASSWORD VARCHAR2(12) NOT NULL,
name varchar2(10) not null,
BIRTHDAY date NOT NULL
)
2. child 테이블 작성
create table child (
ID VARCHAR2(20) constraint child_id_fk references parent(id),
ADDRESS varchar2(20) default '서울시',
gender VARCHAR2(12) constraint child_gender_ck check(gender in ('남','여'))
)
3. insert시 오류 발생한 부분 수정
> alter table parent modify name varchar2(12);
4. 아래의 select문 완성
select constraint_name, constraint_type, table_name, search_condition
from user_constraints natural join USER_CONS_COLUMNS
where table_name in ('PARENT', 'CHILD');
5. 아래의 select문 완성
select TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME
from user_constraints natural join USER_CONS_COLUMNS
where table_name in ('PARENT', 'CHILD');
6. 두 개의 테이블 삭제
7. 다음의 결과를 얻을 수 있도록
USER_CONSTRAINTS와 USER_CONS_COLUMNS를 JOIN 해 보세요.
TABLE_NAME COLUMN_NAM CO CONSTRAINT_NAME
---------- ---------- -- -------------------
DEPT DEPTNO P PK_DEPT
EMP EMPNO P PK_EMP
EMP DEPTNO R FK_DEPTNO
>select table_name,constraint_type ,column_name,constraint_name, search_condition from user_constraints natural join USER_CONS_COLUMNS;
=================================================================
조건)
SQL> desc parent;
Name Null? Type
----------------------------------- -------- ------------------------
ID NOT NULL VARCHAR2(20)
PASSWORD NOT NULL VARCHAR2(12)
NAME NOT NULL VARCHAR2(10)
BIRTHDAY NOT NULL DATE
SQL> desc child;
Name Null? Type
----------------------------------- -------- ------------------------
ID VARCHAR2(20)
ADDRESS VARCHAR2(20) - 기본값 '서울시'
GENDER VARCHAR2(2) - 남 또는 여
SQL> select constraint_name, constraint_type, table_name, search_condition
from user_constraints natural join USER_CONS_COLUMNS
where table_name in ('PARENT', 'CHILD');
CONSTRAINT_NAME CO TABLE_NAME SEARCH_CONDITION
-------------------- -- ---------- --------------------------------------------------------------------------------
SYS_C008087 C CHILD gender in ('남','여')
SYS_C008088 R CHILD
SYS_C008083 C PARENT "PASSWORD" IS NOT NULL
SYS_C008084 C PARENT "NAME" IS NOT NULL
SYS_C008085 C PARENT "BIRTHDAY" IS NOT NULL
SYS_C008086 P PARENT
SQL> select TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME
2 from user_constraints natural join USER_CONS_COLUMNS
3 where table_name in ('PARENT', 'CHILD');
TABLE_NAME CONSTRAINT_NAME COLUMN_NAME
---------- -------------------- ---------------
PARENT SYS_C008083 PASSWORD
PARENT SYS_C008084 NAME
PARENT SYS_C008085 BIRTHDAY
PARENT SYS_C008086 ID
CHILD SYS_C008087 GENDER
CHILD SYS_C008088 ID
3. 오류 수정
insert into parent values('abcd','1234','홍길동','88/7/23');
insert into parent values('bbbb','5678','신사임당','85/11/01');
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."PARENT"."NAME" (actual: 12,
maximum: 10)
SQL> select * from parent;
ID PASSWORD NAME BIRTHDAY
---------------------------------------- ------------------------ ------------------------ --------
abcd 1234 홍길동 88/07/23
bbbb 5678 신사임당 85/11/01
cccc 90as 성춘향 93/12/15
dddd efgy 리카엘 82/05/01
ffff wjdgml 김풍 85/04/08
SQL> insert into child values('abcd','경상북도','나');
insert into child values('abcd','경상북도','나')
*
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."CHILD"."GENDER" (actual: 3,
maximum: 2)
SQL> insert into child values('abcd','경상북도','나');
insert into child values('abcd','경상북도','나')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C007134) violated
SQL> select * from child;
ID ADDRESS GENDER
---------------------------------------- ---------------------------------------- ------
abcd 경상북도 남
cccc 퐁당시 여
dddd 불가리아 남
ffff 제주시 남
bbbb 기품시 여
ffff 제주시 남