oracle18_테이블, 제약조건, 컬럼 변경

 

1.테이블 생성

-- create : 생성, alter 수정(변경), drop 제거

-- number  : 실수 (정수 와 실수)


create table t_emp(

 id number(5) not null,
 name varchar2(25),
 salary number(7,2),
 phone varchar2(15),
 dept_name varchar2(25)

);

 

2. 테이블에 데이터 입력
 

-- insert into 테이블 values (....); 모든 컬럼
-- insert into 테이블 (컬럼, 컬럼,,,,) values (...)

insert into t_emp values(100, '이상헌', 2000, '010-222-3333', '개발부');
insert into t_emp values(101, '최순철', 3000, '010-333-4444', '총무부');
insert into t_emp values(102, '장혜숙', 4000, '010-444-5555', '영업부');

commit;


select * from t_emp;

 

 

3. 테이블에 컬럼 추가  : hire_date, date 형

alter table t_emp add(hire_date date) ;

 

4.컬럼 수정 : phone 의 varchar2(15) --> varchar2(20)

 

alter table t_emp modify(phone varchar2(20));

 

4-1. 컬럼의 이름 수정

-- rename column A to B : A를 B 로 변경

alter table t_emp rename column id to t_id;



*** 테이블 이름 변경
-- alter table 기존 테이블 rename to 변경테이블
alter table t_emp rename to v_emp;

= 

rename v_emp to s_emp;

 

5. 컬럼의 삭제 : dept_name 삭제

alter table t_emp drop column dept_name;

 

 

6. 테이블의 데이터 조작 : insert , update, delete

 

1) 기존의 row 에 있는 hire_date에 값 입력하기

-- update 테이블 set 컬럼 =수정할값

-- 조건절이 없으면 모든 레코드가 변경됨(주의)

update t_emp set hire_date =sysdate where t_id =100;

update t_emp set hire_date = sysdate where t_id =200;

update t_emp set hire_date = sysdate where t_id =300;

 

 

2) 새로운 데이터를 새로운 row에 입력하기

 

insert into t_emp(t_id, hire_date) values(400, sysdate);

3) 테이블의 row 삭제하기 : delete

--  delete v_emp 조건절이 없으면 모든 레코드가 삭제됨

delete from t_emp where t_id =200;

 

7. 기타

 

1) 테이블명 수정하기 : rename ~ to ~

rename t_emp to s_emp;

(문제) s_emp 테이블에 대하여

1. e-mail을 관리하기 위한 mailid 컬럼을 10byte 로 추가하시오.

alter table s_emp add (mailid varchar2(10));

 

2. mailid 컬럼을 20byte로 수정하시오.

alter table s_emp modify (mailid varchar2(20));

 

3. mailid 컬럼명을 e_mail 로 수정하시오.

alter table s_emp rename COLUMN mailid TO e_mail;

 

4. s_emp 테이블명을 t_emp 로 변경하시오.

alter table  rename s_emp to t_emp

-- = rename s_emp to t_emp;

 

 

<제약조건(constraint)>

1.정의 

테이블의 해당 컬럼에 원치않는 데이터가 입력/변경/삭제되는 것을 방지하기 위해 

테이블 생성시 또는 변경시 설정하는 조건

 

2. 종류

primary key, foreign key, unique, check, not null

 

<제약조건을 반영한 테이블 생성하기 >

create table c_emp (

  id number(5) constraint c_emp_id_pk primary key,
  
  name varchar2(25) constraint c_emp_name_nn not null,

  salary number(7,2),
        
  phone varchar2(15) constraint c_emp_phone_ck check(phone like '3429-%'),
  
  dept_id number(7) constraint c_emp_dept_id_fk references dept(deptno)  

);

 

1. 제약조건 이름 검색하기

select constraint_name from user_constraints;

2. 제약조건은 수정할 수 없고 추가 삭제만 가능하다.

alter table c_emp drop constraint c_emp_name_nn;

3.제약조건 추가하기

alter table c_emp add constraint c_emp_name_un unique(name);

(not null 제약조건은 add 로 할 수 없고 modify로 가능)

alter table c_emp modify (name varchar2(25) constraint c_emp_name_nn not null);

 

 

-- 1.primary key(기본키)  : 중복을 허용하지 않음, not null unique(유일한 값)
-- 
-- constraint 제약조건이름
-- 제약조건 이름 : 테이블이름_컬럼이름_제약조건종류

create table k_emp(
   id number(5) constraint k_emp_id_pk primary key,
   name VARCHAR2(25),
   salary number (7,2),
   phone varchar2(15),
   dept_id number(7) 
);

-- 제약조건 관련 데이터 사전 조회 ( K_EMP 대문자)
select * from SYS.USER_CONSTRAINTS where table_name ='K_EMP';

-- 2. check 제약조건
-- check(필드명 연산자 값 또는 수식)
drop table c_emp ;


-- salary에 100~1000 이외의 값이 들어가면 입력되지 ㅇ낳음
create table c_emp (
id number(5),
name VARCHAR2(25),
salary number(7,2) constraint c_emp_salary_ck check(salary between 100 and 1000),
phone varchar2(15),
dept_id number(7)
);

select * from user_constraints where table_name='C_EMP';

insert into c_emp(salary) values(2000);
-- 출력
--02290. 00000 -  "check constraint (%s.%s) violated"
-- *Cause:    The values being inserted do not satisfy the named check

-- 3. Foreign Key 제약조건
-- 외래키, 외부키
-- 주로 다른 테이블의 PK 를 참조할 경우에 사용
-- references 테이블(컬럼) 
-- references dept(deptno)

drop table c_emp;

-- dept_id 컬럼에는 dept 테이블의 deptno 컬럼값만 입력 가능

create table c_emp(
 id number(5),
 name varchar2(25),
 salary number(7, 2),
 phone varchar2(150),
 dept_id number(7) constraint c_emp_dept_id_fk2 
    references dept(deptno)
);

insert into c_emp (dept_id) values (10);

insert into c_emp (dept_id) values (30);

insert into c_emp (dept_id) values (100);

-- 출력
--오류 보고 -
--SQL 오류: ORA-02291: integrity constraint (HR.C_EMP_DEPT_ID_FK2) violated - parent key not found
--02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
--*Cause:    A foreign key value has no matching primary key value.
--*Action:   Delete the foreign key or add a matching primary key.

-- 테이블 생성 후 제약조건을 추가하는 방법
drop table c_emp;

create table c_emp(
 id number(5),
 name varchar2(25),
 salary number(7, 2),
 phone varchar2(150),
 dept_id number(7)
);

alter table c_emp  add constraint c_emp_id_pk2 primary  key (id);

-- 제약조건 삭제 방법
-- alter table 테이블 drop constraint 제약조건이름 ;
alter table c_emp drop constraint c_emp_id_pk2;

-- 4.unique 제약조건
-- Primary key : 중복값x + not null
-- Unique : 중복값 x, null 입력 가능
-- Primary key 와 Unique 는 => 자동으로 인덱스 생성

-- Primary key 와 Unique 차이는 null 값 허용 여부차이 이다.

 

 

4. 제약조건 활성화/비활성화

 

alter table c_emp disable constraint c_emp_name_nn;

alter table c_emp enable constraint c_emp_name_nn;

비활성화했던 것을 다시 활성화시킬 때 기존에 입력된 중복자료를 삭제해야 함

 

5. 제약조건의 삭제

alter table c_emp drop constraint c_emp_name_nn;