printf("ho_tari\n");
oracle sql (5) 본문
/*
5일차
제약 조건
- 테이블에 저장할 데이터를 제약하는 특수 규칙
- 데이터의 정확성을 높이기 위해 사용
- DDL에서 설정 : 테이블의 특정열에 지정
- 종류
1. NOT NULL : NULL을 허용하지 않는다
2. UNIQUE : 유일한 값을 가져야 한다
3. PRIMARY KEY : 지정한 컬럼이 유일한 값이면서 NULL을 허용하지 않는다
4. FOREIGN KEY : 다른 테이블의 컬럼을 참조하여 존재하는 값만 입력할 수 있다
5. CHECK : 실행 조건을 만족하는 데이터만 입력 가능
6. DEFAULT : 기본값 지정
- 데이터 무결성
1. 데이터베이스에 저장되는 데이터의 정확성과 일관성을 보장
2. 제약조건이 데이터의 무결성을 유지하기 위한 장치
3. 삽입, 수정, 삭제 등의 모든 과정에 유지
4. 종류
4-1. 영역무결성(domain integrity) : 컬럼에 저장되는 값의 적정 여부를 확인
4-2. 개체무결성(entity integrity) : 테이블 데이터를 유일하게 식별할 수 기본키는 반드시 값을 가지고 있어야 하며, NULL이 될 수 없다
4-3. 참조무결성(referential integrity) : 참조 테이블의 외래키 값은 참조 테이블의 기본키로써 존재해야 하며, NULL이 가능
*/
-- 1. NOT NULL
-- 테이블 생성 시 설정
drop TABLE t1;
CREATE TABLE t1 (
login_id VARCHAR2(20) NOT NULL,
login_pw VARCHAR2(20) NOT NULL,
tel VARCHAR2(20)
);
INSERT INTO t1(login_id, login_pw, tel)
VALUES ('test', NULL, '010-1111-1111');
INSERT INTO t1(login_id, login_pw, tel)
VALUES ('test', '1234', '010-1111-1111');
UPDATE t1 SET login_pw = NULL WHERE login_id = 'test';
SELECT * FROM t1;
-- 제약 조건 확인
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;
-- 제약 조건 이름 지정
drop table t2;
drop table t_con;
CREATE TABLE t_con (
login_id VARCHAR2(20) CONSTRAINT t2_login_id NOT NULL,
login_pw VARCHAR2(20) CONSTRAINT t2_login_pw NOT NULL,
tel VARCHAR2(20)
);
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;
-- 이미 생성된 테이블에 제약조건 추가
-- 이미 NULL값이 컬럼에 존재하는 경우
INSERT INTO t1(login_id, login_pw, tel)
VALUES ('test', '1234', NULL);
SELECT * FROM t1;
ALTER TABLE t1
MODIFY (tel NOT NULL);
UPDATE t1 SET tel = '010-2222-2222'
WHERE login_id = 'test1';
SELECT * FROM t1;
ALTER TABLE t1
MODIFY (tel NOT NULL);
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;
-- 제약 조건 이름을 직접 지정
ALTER TABLE t1
MODIFY (tel constraint t1_tel_con NOT NULL);
-- 생성한 제약 조건의 이름 변경
ALTER TABLE t1
RENAME constraints SYS_C007019 to t1_phone_con;
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;
-- 제약 조건 삭제
ALTER TABLE t1
DROP CONSTRAINT t1_phone_con;
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints;
-- 2. UNIQUE
DROP TABLE t1;
CREATE TABLE t1(
login_id VARCHAR2(20) UNIQUE,
login_pw VARCHAR2(20) NOT NULL,
tel VARCHAR2(20)
);
DESCRIBE t1;
-- 제약 조건 확인
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'T1'; -- U : UNIQUE(중복 허용 X)
-- 중복 허용 X
INSERT INTO t1
VALUES ('test1', 'pw1', '1111');
INSERT INTO t1
VALUES ('test1', 'pw1', '1111');
INSERT INTO t1
VALUES ('test2', 'pw1', '1111');
SELECT * FROM t1;
-- unique 지정된 컬럼에 NULL 입력
INSERT INTO t1
VALUES (NULL, 'pw1', '1111');
SELECT * FROM t1;
-- 데이터 수정
UPDATE t1 SET login_id = 'test1'
WHERE login_id IS NULL;
UPDATE t1 SET login_id = 'test10'
WHERE login_id IS NULL;
SELECT * FROM t1;
-- 테이블 생성하며 unique 지정
DROP table t1;
CREATE TABLE t1 (
login_id VARCHAR2(20) CONSTRAINT t1_login_id UNIQUE,
login_pw VARCHAR2(20) CONSTRAINT t1_login_pw NOT NULL,
tel VARCHAR2(20)
);
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'T1';
-- 3. PRIMARY KEY : NOT NULL + UNIQUE
CREATE TABLE tp(
login_id VARCHAR2(20) PRIMARY KEY,
login_pw VARCHAR2(20) NOT NULL,
tel VARCHAR2(20)
);
DESC tp;
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'TP'; -- P : PRIMARY
-- 제약 조건 이름 직접 지정 : NOT NULL방식과 동일
-- 중복 값 입력 X
-- NULL 값 입력 X
-- primary key 지정
DROP TABLE tp;
CREATE TABLE tp(
login_id VARCHAR2(20),
login_pw VARCHAR2(20),
tel VARCHAR2(20),
PRIMARY KEY(login_id),
CONSTRAINT constraint_name UNIQUE(login_pw)
);
DESC tp;
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'TP';
-- 4. FOREIGN KEY
SELECT owner, constraint_name, constraint_type, table_name,
r_owner, r_constraint_name FROM user_constraints
WHERE table_name IN ('EMP', 'DEPT');
-- FK가 참조하는 컬럼에 NULL입력
INSERT INTO emp
VALUES (999, '홍길동', 'CLERK', '7788',
TO_DATE('2023-07-28', 'YYYY-MM-DD'), 12000, NULL, 50);
-- FK 지정
DROP TABLE tf;
CREATE TABLE tf(
deptno NUMBER(2) CONSTRAINT tf_pk PRIMARY KEY,
dname VARCHAR2(20),
loc VARCHAR2(15)
);
DESC tf;
-- emp_fk 지정
DROP TABLE emp_tf;
CREATE TABLE emp_tf(
empno NUMBER(4) CONSTRAINT emp_tf_pk PRIMARY KEY,
ename VARCHAR2(20),
job VARCHAR2(20),
deptno NUMBER(2) CONSTRAINT emp_tf_fk REFERENCES tf(deptno)
);
DESC emp_tf;
SELECT owner, constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name = 'EMP_TF';
-- 5. CHECK
DROP TABLE tc;
CREATE TABLE tc(
login_id VARCHAR2(20) CONSTRAINT login_id_pk PRIMARY KEY,
login_pw VARCHAR2(20) CONSTRAINT login_pw_ck CHECK (length(login_pw) > 8),
tel VARCHAR2(20)
);
INSERT INTO tc
VALUES ('test', '123', '010-1111');
-- 6. DEFAULT
CREATE TABLE td(
login_id VARCHAR2(20) CONSTRAINT td_login_pk PRIMARY KEY,
login_pw VARCHAR2(20) DEFAULT '1234',
tel VARCHAR2(20)
);
INSERT INTO td (login_id, tel)
VALUES ('test1', '010-1111');
SELECT * FROM td;
/*
-- 제약조건 활성화 / 비활성화
ALTER TABLE 테이블명
DISABLE [NOVALIDATE/ VALIDATE] CONSTRAINT 제약조건 이름;
ALTER TABLE 테이블명
ENABLE [NOVALIDATE/ VALIDATE] CONSTRAINT 제약조건 이름;
*/
/*
사용자관리, 권한관리, 롤관리
- 데이터베이스 스키마
1. 데이터베이스에서 데이터간 관계, 구조, 제약조건 등 데이터 저장 및 관리를 하기 위해서
데이터베이스 구조의 범위를 스키마를 통해 그룹단위로 분류
2. 종류
- 외부스키마 = 사용자 뷰 : 사용자나 응용프로그래머가 각 개인의 입장에서 필요로 하는 데이터베이스의 논리적 구조를 정의
- 개념스키마 = 전체적인 뷰 : 데이터베이스의 전체적인 논리적 구조
- 내부스키마 = 저장 스키마 : 물리적 저장장치의 입장에서 본 데이터베이스 구조
- 사용자 : 데이터베이스에 접속하여 데이터를 관리하는 계정
*/
/*
-- scott 계정 생성 : system계정에서 생성
create user scott identified by TIGER;
*/
-- 생성 : create
CREATE USER study IDENTIFIED BY study;
-- 권한부여 : grant
GRANT CREATE SESSION TO study;
-- 정보 조회
SELECT * FROM ALL_USERS
WHERE USERNAME = 'study';
-- 사용자 변경과 삭제
ALTER USER study IDENTIFIED BY study;
DROP USER study;
-- 사용자와 객체 모두 삭제
DROP USER study CASCADE;
/*
권한관리
1. 시스템권한 : 사용자 생성과 정보 수정, 삭제, 데이터베이스 접근...
- 권한 부여
grant [시스템권한] to [사용자이름/롤이름/public] [with admin option];
- 권한 취소
revoke [시스템권한] from [사용자이름/롤이름/public]
2. 객체권한 : 특정 사용자가 생성한 테이블, 인덱스, 뷰, 시퀀스 등과 관련된 권한
- 권한 부여
grant [객체권한/ ALL PRIVILEGES] on [스키마.객체이름] to [사용자이름/롤/public] [with admin option];
- 권한 취소
revoke [객체권한/ ALL PRIVILEGES] on [스키마.객체이름] from [사용자이름/롤/public] [cascade constrains/force];
*/
/*
롤
- 여러 종류의 권한을 묶어놓은 그룹을 의미
- 여러 권한을 부여하고 취소 -> 관리 효율 용이
- 종류
1. 사전 정의된 롤
- CONNECT 롤 : 사용자가 데이터베이스에 접속에 필요한 create session 권한
- RESOURCE 롤 : 테이블, 시퀀스를 비롯한 여러 객체를 생성할 수 있는 권한
- DBA 롤 : 데이터베이스를 관리하는 시스템권한 대부분 소유
2. 사용자 정의 롤 : 필요에 의해 직접 권한 포함시킨 롤
- 절차
2-1. create role : 롤 생성
2-2. grant : 생성한 롤에 권한 부여
2-3. grant : 권한이 포함된 롤을 특정 사용자에게 부여
2-4. revoke : 롤 취소
*/
'SQL' 카테고리의 다른 글
oracle sql (example problem) (0) | 2023.09.02 |
---|---|
oracle sql (4) (0) | 2023.09.02 |
oracle sql (3) (0) | 2023.09.02 |
oracle sql (2) (0) | 2023.09.02 |
oracle sql (1) (0) | 2023.09.02 |