select * from user_constraints where table_name = 'EMP'; --제약 정보 확인

--1. NOT NULL : null 포함 안됨
--2. UNIQUE key : 유일값 , 중복값 안됨 , null값을 가질 수 있다. >> null을 못가지게 하려면 not null을 사용
--3. PRIMARY key : not null하고 unique한 제약 ,
--     내부적으로 index page가 자동 설정 >> 검색을 빠르게 하기 위해서 >> 유지보수는 힘들다. 
create table temp7(
  id number constraint pk_temp7_id primary key, -->> 관용적인 표현 (제약의 축소표현_테이블명_컬럼명) 으로 이름을 붙여준다.
  --id number primary key , ->> 권장하지 않음 -- 제약의 이름이 자동으로 등록이 된다. >>제약의 이름이 sys_c006997등으로 붙여진다. >>제약이름으로 제약을 지울 때 힘들다.
  name varchar2(20) not null,
  addr varchar2(50) 
);
select * from user_constraints where table_name = 'TEMP7';

insert into temp7 (name,addr) values('홍길동','서울시 강남구'); -->>ORA-01400: cannot insert NULL into ("BITUSER"."TEMP7"."ID") : null값 error
insert into temp7 (id,name,addr) values(10,'홍길동','서울시 강남구');
insert into temp7 (id,name,addr) values(10,'김유신','서울시 강남구'); -->>unique constraint (BITUSER.PK_TEMP7_ID) violated : 중복키값 error

create table temp8(
  id number constraint pk_temp8_id primary key, --PK는 테이블 당 한개(중복 키 가능 : 묶어서 한개)
  name varchar2(20) not null,
  jumin char(6) constraint uk_temp8_jumin unique, --null을 허용한다.
  --jumin char(6) not null constraint uk_temp8_jumin unique (중복 X , null X)
  addr varchar2(20)
);
select*from user_constraints where table_name = 'TEMP8';
insert into temp8(id,name,jumin,addr) values(10,'홍길동','123456','서울시 강남구');

select * from temp8;

insert into temp8(id,name,jumin,addr) values(100,'김유신','123456','경기도 '); -->>unique constraint (BITUSER.UK_TEMP8_JUMIN) violated 중복 에러
insert into temp8(id,jumin,addr) values(200,'123456','경기도 '); -->>cannot insert NULL into ("BITUSER"."TEMP8"."NAME") 이름이 널
insert into temp8(id,name,jumin) values(20,'아무개','123456');

--테이블 생성 후에 제약 걸기
create table temp9(id number);
--기존 테이블에 제약 추가하기
--주의) 입력된 데이터가 있다면 >> 10,10 이 들어 있으면 >> pk 제약(x) >> 데이터 삭제 >>제약
alter table temp9
add constraint pk_temp9_id primary key (id);

select*from user_constraints where table_name='TEMP9';

alter table temp9 
add ename varchar2(20);

desc temp9; --테이블 기본정보
select*from user_constraints where table_name='TEMP9'; --테이블 제약 정보 

alter table temp9
modify(ename not null);

-- 테이블과 테이블 간의 관계 설정
-- table과 table간의 제약 >> 관계를 확보할 수 있다.

create table c_emp
as 
  select empno,ename , deptno from emp where 1=2;

create table c_dept
as select deptno, dname from dept where 1=2;

select*from c_emp;
select * from c_dept;

--참조키(c_emp (deptno) fk -> c_dept(deptno) 컬럼을 참조하도록 ) 이 때 dept table deptno 컬럼은 기본키로 되어야 한다.
--1.
alter table c_dept
add constraint pk_dept_deptno PRIMARY key(deptno);

--2.
alter table c_emp 
add constraint fk_emp_deptno FOREIGN key(deptno) REFERENCES c_dept(deptno);

insert into c_dept(deptno,dname) values(100,'인사팀');
insert into c_dept(deptno,dname) values(200,'관리팀');
insert into c_dept(deptno,dname) values(300,'회계팀');
commit;

--사원 입사 (부서정하지 않고)
insert into c_emp(empno,ename)
values(100,'홍길동'); --fk not null을 강제하지 않는다.(null)

select * from c_emp;

insert into c_emp(empno,ename,deptno)
values(200,'김유신',500); -- 회사의 deptno는 100 200 300 밖에 없는데 500은 안들어간다.  integrity constraint (BITUSER.FK_EMP_DEPTNO) violated - parent key not found

insert into c_emp(empno,ename,deptno)
values(200,'김유신',300);
commit;

delete from c_dept where deptno = 200;--참조하고 있는 것이 없어서 지울 수 있다.
delete from c_dept where deptno = 300;--참조하고 있어서 지울 수 없으므로 참조 하고 있는 것 부터 지워야 한다.

/*
column datatype [CONSTRAINT constraint_name]        
REFERENCES table_ name (column1[,column2,..] [ON DELETE CASCADE]) 

column datatype, . . . . . . . ,
[CONSTRAINT constraint_name] FOREIGN KEY (column1[,column2,..])       
REFERENCES table_name  (column1[,column2,..] [ON DELETE CASCADE])

-- [ON DELETE CASCADE] >> 부모 테이블과 같이 사라지게 하는 것
-- ms sql은 on update cascade도 있다. oracle을 delete밖에 없다.
-- 참조하는 자식 데이터 같이 삭제

*/
alter table c_emp 
add constraint fk_emp_deptno FOREIGN key(deptno) REFERENCES c_dept(deptno) ON DELETE CASCADE; -->> 참조하는 것들을 바로 지울 수 있다.

-- 설정한 범위 내의 값만 입력받겠다. ex) gender 컬럼에 '남' 또는 '여'라는 데이터만 넣고 싶다.
-- where gender in('남','여') 와 같다.
--where 조건과 동일한 형태 >> where gender in('남','여');
create table temp10(
id number constraint pk_temp10_id primary key,-- (묶어서 1개)
name varchar2(20) not null, --필수 입력
jumin char(6) constraint uk_temp10_jumin Unique, --필요하다면 not null을 추가하는 형태 가능하다.
addr varchar2(20), --추가 입력
age number constraint ck_temp10_age check(age >= 19)
);

desc temp10;
select*from user_constraints where table_name='TEMP10';

insert into temp10(id,name,jumin,addr,age)
values(100,'홍길동','123456','서울시 강남구',25);

select * from temp10;

insert into temp10(id,name,jumin,addr,age)
values(200,'홍길동','123457','서울시 강남구',25);

insert into temp10(id,name,jumin,addr,age)
values(300,'홍길동','123458','서울시 강남구',18); -- check constraint (BITUSER.CK_TEMP10_AGE) violated >>나이가 19가 안되므로 체크제약 위반으로 걸린다.

/*
--학생 성적 테이블
--학번의 데이터는 중복되거나 NULL 값을 허용하면 안된다
--이름 NULL 값을 허용하지 않는다
--국어
--영어
--수학 점수 컬럼은 숫자 타입이고 NULL 값을 허용
--는 값을 입력하지 않으면  default로 0값을 갖는다

--총점 ,평균 컬럼은 가상컬럼으로(조합컬럼) 생성한다
--학과코드는 학과 테이블에 학과코드를 참조한다
--학번 , 이름 , 국어 , 영어 , 수학 , 총점 , 평균 , 학과코드

--학과 테이블
--학과코드 데이터는 중복되거나 NULL 값을 허용하면 안된다,
--학과명 은 null값을 허락하지 않는다

--학과코드 , 학과명

--그리고 select 결과는
--학번 , 이름  총점, 평균 , 학과코드 , 학과명 을 출력하세요
*/
create table student(
  snumber number constraint pk_student_snumber primary key,
  sname varchar2(20) not null,
  lan number default 0,
  eng number default 0,
  math number default 0,
  total_score number GENERATED ALWAYS as (lan+eng+math) VIRTUAL,
  average_score number GENERATED ALWAYS as ((lan+eng+math)/3) VIRTUAL
  deptno number
);
create table department(
 deptno number constraint pk_student_deptno primary key,
 dname VARCHAR2(20) not null
);
alter table student 
add constraint fk_student_deptno FOREIGN key(deptno) REFERENCES department(deptno);



select * from student;
select * from department;


insert into department(deptno,dname) values (10,'화학과');
insert into department(deptno,dname) values (20,'수학과');
insert into department(deptno,dname) values (30,'컴퓨터공학과');
insert into department(deptno,dname) values (40,'철학과');

insert into student(snumber,sname,lan,eng,math,deptno)
values (1,'홍길동',45,55,90,10);
insert into student(snumber,sname,lan,eng,math,deptno)
values (2,'김유신',77,45,63,20);
insert into student(snumber,sname,lan,eng,math,deptno)
values (3,'강아지',78,12,45,30);
insert into student(snumber,sname,lan,eng,math,deptno)
values (4,'고양이',89,55,44,40);
insert into student(snumber,sname,lan,eng,math,deptno)
values (5,'김철수',11,22,53,30);
insert into student(snumber,sname,lan,eng,math,deptno)
values (6,'김영희',23,56,14,20);
insert into student(snumber,sname,lan,eng,math,deptno)
values (7,'오라클',11,78,65,10);
commit;
--학번 , 이름  총점, 평균 , 학과코드 , 학과명 을 출력하세요
select student.snumber as "학번" , student.SNAME as "이름",student.TOTAL_SCORE as "총점",round(student.AVERAGE_SCORE) as "평균",student.DEPTNO as "학과코드",department.DNAME as "학과명"
from student  join department 
on student.deptno = department.deptno;