[Oracle] 테이블 생성 및 테이터 삽입

[학생 테이블 생성 및 삽입]
CREATE TABLE student

(stu_id CHAR(5)    CONSTRAINT student_stu_id_pk PRIMARY KEY

                CONSTRAINT student_stu_id_ck CHECK(LENGTH(TRIM(' ' FROM stu_id)) =5),

 stu_name   VARCHAR2(10) CONSTRAINT student_stu_name_nn NOT NULL

);

 

INSERT INTO student VALUES('94031','박지성');

INSERT INTO student VALUES('94032','이청용');

INSERT INTO student VALUES('94033','차두리');

INSERT INTO student VALUES('94034','기성용');

INSERT INTO student VALUES('94035','박주영');

CHAR과 VCHAR2
char - 고정길이  , vchar - 가변길이

---

 

[과목 테이블 생성 및 삽입]
CREATE TABLE subject

(sub_id NUMBER(3)     CONSTRAINT subject_sub_id_pk PRIMARY KEY,

 sub_name VARCHAR2(20) CONSTRAINT subject_sub_name_nn NOT NULL

);

INSERT INTO subject VALUES(1,'국어');

INSERT INTO subject VALUES(2,'영어');

INSERT INTO subject VALUES(3,'과학');

INSERT INTO subject VALUES(4,'수학');

INSERT INTO subject VALUES(5,'국사');

---

[성적 테이블 생성 및 삽입]
CREATE TABLE score

(stu_id CHAR(5)           CONSTRAINT score_stu_id_nn NOT NULL,

 sub_id NUMBER(3)     CONSTRAINT score_sub_id_nn NOT NULL,

 score  NUMBER(3)      CONSTRAINT score_score_nn NOT NULL,

 grade  CHAR(1)           CONSTRAINT score_grade_ck CHECK(grade IN('A','B','C','D','F')),

 CONSTRAINT score_pk  PRIMARY KEY (stu_id, sub_id),

 CONSTRAINT score_fk1   FOREIGN KEY (stu_id) REFERENCES student(stu_id) ON DELETE CASCADE,

 CONSTRAINT score_fk2   FOREIGN KEY (sub_id) REFERENCES subject(sub_id) ON DELETE CASCADE

);

INSERT INTO score VALUES('94031',1,90,'B');

INSERT INTO score VALUES('94031',2,65,'D');
.
.
COMMIT;

CONSTRAINT score_grade_ck CHECK(grade IN('A', 'B', 'C', 'D', 'F')
과목 점수가 A,B,C,D,F외에는 insert할 수 없다(제약조건), 여기서 IN()함수는 AND와 같은 역할을 한다

CONSTRAINT score_fk1 FOREIGN KEY(stu_id) REFERENCES student(stu_id) ON DELETE CASCASE
primary key로 지정된 stu_id에서 이블의 stu_id와 foreign키 이며 delete cascade는 stu_id를 삭제 했을 경우
모든 테이블의 stu_id가 삭제가 된다