[sql]제약조건, 데이터 삽입∙수정∙삭제

---

학생          : 학번(PK),    이름,        전화,   입학일
STUDENT     STID      STNAME PHONE INDATE

---

🐲제약조건(CONSTRAINTS)

TABLE 에 저장될 데이터에 조건을 부여하여 잘못된 DATA 입력되는 것을 방지
1. 주식별자 설정:기본키
   PRIMARY KEY :NOT NULL+UNIQUE 기본적용, CREATE명령문 안에서 한번밖에 사용못함
   CREATE TABLE 명령안에 한번만 사용가능
2. NOT NULL / NULL : 필수입력, 컬럼단위 제약조건(컬럼에만 적용됨)
3. UNIQUE :줄단위 중복방지
4. CHECK : 값의 범위 지정{DOMAIN(들어갈 수 있는 제약조건의 범위 ex)성별:남,여,모름) 제약조건}
5. FOREIGN KEY : 외래키 제약조건
참고:

[SQL]테이블 생성, 삭제, 변경

[SQL]테이블 생성, 삭제, 변경

 

CREATE TABLE STUDENT(
    STID NUMBER(6) PRIMARY KEY, --학번 숫자(6) 기본키
    STNAME VARCHAR(30) NOT NULL, --이름 문자(30) 필수입력
    PHONE VARCHAR(20) UNIQUE, --전화문자(20) 중복방지
    INDATE DATE DEFAULT SYSDATE --입학일 날짜 기본값 SYSDATE
);-- 칼럼 레벨 제약조건

 

=>Table STUDENT이(가) 생성되었습니다.

🐲제약조건 추가/수정/삭제

추가:ADD CONSTRAINT 데이터값 제약조건 (데이터값)

   ALTER TABLE  EMP2
    ADD CONSTRAINT  EMP2_PK  PRIMARY KEY ( EMPLOYEE_ID  );

수정::MODIFY (데이터값 CONSTRAINT 데이터 값 제약조건)     

   ALTER TABLE  EMP2
    MODIFY   (PHONE_NUMBER CONSTRAINT PHONE_NN  NOT NULL );

 

삭제:DROP CONSTRAINT 데이터 값

   ALTER TABLE  EMP2
    DROP CONSTRAINT  EMP2_UK;

 

---

🐲데이터 입력

성적 :          일련번호(PK),        교과목,        점수,     학번(FK)
SCORES         SCID               SUBJECT   SCORE    STID

CREATE TABLE SCORES(
	SCID NUMBER(6),--일련번호
	SUBJECT VARCHAR(30) NOT NULL, --교과목
	SCORE NUMBER(3) CHECK (SCORE BETWEEN 0 AND 100),-- 점수(범위:0~100)
	STID NUMBER(6) ,--학번
    INDATE DATE DEFAULT SYSDATE, --입학일 날짜 기본값 SYSDATE

CHECK 제약조건: SCORE를 0부터 100까지의 입력범위로 제한한다.

CONSTRAINT SCID_PK PRIMARY KEY (SCID,SUBJECT)

PRIMARY KEY 제약 조건: SCID와 SUBJECT 열을 결합하여 복합 기본 키를 설정한다.

CONSTRAINT STID_FK FOREIGN KEY(STID) REFERENCES STUDENT (STID)

FOREIGN KEY 제약 조건: STID 열이 STUDENT 테이블의 STID 열을 참조하도록 설정한다.

STUDENT테이블

📍INSERT INTO 테이블명

INSERT  INTO  SCORES (SCID,SUBJECT,SCORE,STID,INDATE)
              VALUES( 1, '국어', 100, 1, '2024-09-06');

INSERT  INTO  SCORES VALUES( 2, '영어', 100, 1);

💡default가 있는 경우 생략 가능

INSERT  INTO  SCORES VALUES( 3, '수학', 100, 1, SYSDATE);
INSERT  INTO  SCORES VALUES( 4, '국어', 100, 2, SYSDATE);
INSERT  INTO  SCORES VALUES( 5, '수학',  80, 2, SYSDATE);
INSERT  INTO  SCORES VALUES( 6, '국어',  70, 4, SYSDATE);
INSERT  INTO  SCORES VALUES( 7, '영어',  80, 4, SYSDATE);
INSERT  INTO  SCORES VALUES( 8, '수학',  85, 4, SYSDATE);
INSERT  INTO  SCORES VALUES( 9, '국어',  999, 5, SYSDATE);  -- 입력안됨, CHECK 제약조건 위배
INSERT  INTO  SCORES VALUES( 10, '영어',  100, 6, SYSDATE); -- 입력안됨, STUDENT테이블의 학생이 5번까지밖에 없다
COMMIT;

💡열을 명시하지 않고 삽입할 수 있지만, 이 경우 모든 열의 값을 제공해야 한다.

📍바인드 변수(Bind Variable)

INSERT INTO SCORES (SCID,SUBJECT,SCORE,STID,INDATE)
VALUES(
:V0,
:V1,
:V2,
:V3,
:V4
);

실행결과

제약변수조건에 맞게 바인드값을 입력하면 데이터가 입력된다. 바인드 변수는 SQL 쿼리의 성능을 높이고 보안을 강화하며, 코드의 가독성을 높이는 역할을 한다.

📍번호자동증가기능 - sequence 시퀀스

CREATE SEQUENCE SEQNUM;
=>Sequence SEQNUM이(가) 생성되었습니다.

SEQNUM.NEXTVAL - 사용할 번호를 가져오는 것
SEQNUM.CURRVAL - 현재 번호를 가져오는 것

단점:INSERT 실패(제약조건)할 경우 번호가 비는 상황이 발생한다.

INSERT INTO SCORES (SCID,SUBJECT,SCORE,STID,INDATE)
VALUES ( SEQNUM.NEXTVAL, '수학',  85, 4, SYSDATE);

 

DROP SEQUENCE SEQNUM; --번호자동증가기능 삭제
=>Sequence SEQNUM이(가) 삭제되었습니다.

---

🐲데이터 제거 

1)WHERE 문의 조건에 맞는 데이터만 제거   

DELETE FROM 테이블명
WHERE 조건;

행 전체를 삭제하며 삭제표시를 한다.
삭제조건: WHERE 문 조건이 참일떄 삭제하고 WHERE 문이 없으면 전체 DATA가 삭제된다.

2)구조는 남기고 데이터만 제거(속도가 빠름)

TRUNCATE TABLE 테이블명

 행의 내용을 삭제한다.

🐲데이터 수정

UPDATE 테이블명
SET 칼럼명=데이터값
WHERE 조건

대상은 WHERE 문의 조건이 참인 것만 수정되고 WHERE 문이 없으면 전체 데이터가 수정된다.

 

ROLLBACK;
SELECT * FROM 테이블명;

ROLLBACK을 하면 수정하기 전의 상태로 돌아간다.

---