[MSSQL] SQL 쿼리 기초이론, 제약조건

관계형 데이터베이스 기본 개념

관계형 데이터베이스

관계란 집합, 테이블을 의미한다.

즉 관계형 데이터베이스는 테이블 형태의 데이터베이스를 의미

 

SQL의 구분

DDL(Data Definition Language) : 데이터 구조 정의 / CREATE, ALTER, DROP 

DML(Data Manipulation Language) : 데이터 조회 및 변경 / SELECT, INSERT, UPDATE, DELETE, TRUNCATE, MERGE

DCL(Data Control Language) : 데이터 접근 권한 제어 / GRANT, REVOKE

 

관계형 데이터베이스 용어

row : 행, tuple, record

column : 열, attribute, feild

 

RDBMS : SQL Server

 

기본 수학이론

집합이론 : 게오르크 칸토어

- 집합이론에서 집합(Set) = 릴레이션(Relation)

1) 고유성(중복 되지 않음)

2) 순서가 없음

 

술어 논리 : 참(TRUE), 거짓(FALSE), NULL

술어 표현식 : Predicate Expressions : SQL Query에서 제약조건, 필터조건을 표현하기 사용된다.

 

정규화

각각의 엔티티가 중복되지 않고 논리적인 모순이 없는 하나의 릴레이션에 의해 표현되는 것을 보장하는 것.

정규화 목적 : 데이터의 중복 피하고 특정 데이터 변경하더라도 모든 개체간 논리적 모순이 발생하지 않도록 하는 것.

 

MS-SQL Server 인스턴스

SQL Server 인스턴스 : 서버 컴퓨터에 설치된 SQL Server 데이터베이스 엔진을 말함, 동일한 서버 컴퓨터에 여러개의 인스턴스를 설치할 수 있다. 

 

MS-SQL Server 데이터베이스

테이블, 뷰, 저장 프로시저와 같은 개체들의 저장소

시스템 데이터베이스와 사용자 데이터베이스로 구분

시스템 데이터베이스 : master, model, tempdb, msdb, Resource

물리적인 데이터베이스 구조 (파일구조)

 

 

스키마 (Scemas) 와 개체 (Objects)

스키마 : 데이터베이스와 같이 테이블, 뷰,  저장 프로시져와 같은 개체들이 저장되는 저장소

각개체들의 이름앞에 접두사처럼 붙는 네임스페이스 역할

 

dbo?

 

MSSQL에서 테이블을 생성하면 dbo.테이블명 이런식으로 생성이 된다.

 

 [dbo]. 란 스키마이다.  

 

dbo.를 쓰지 않아도 각 개체들을 인식하지만,

이를 명시할 경우 데이터베이스가 검색하는 조건이 명확해진다.

 

즉 데이터베이스 성능 향상에 도움이 된다. 

 

 

테이블 생성

USE [데이터베이스];   IF OBJECT_ID('dbo.Employees','U') IS NOT NULL   DROP TABLE dbo.Employees; CREATE TABLE dbo.Employees (    empid  INT  NOT NULL,   firstname  VARCHAR(30)  NOT NULL,   .   .   .   salary  MONEY  NOT NULL ) -

 

여러번 테스트를 하게 될 경우 임시테이블이 이미 존재한다는 예외가 발생한다.

 

- 이를 해결하기 위해 임시 테이블이 존재할 경우 삭제하는 쿼리이다. 

 

IF OBJECT_ID('dbo.Employees','U') IS NOT NULL

  DROP TABLE dbo.Employees;

 

- 테이블 내부에 열 구조 초기화 설정

 

열이름  데이터 형식  NULL 여부

 

 

데이터 무결성

- 데이터에 오류가 없고(중복X) 논리적 모순이 없는 것.

 

EX ) 데이터의 논리적 모순 : 인사과 직원이 입력한 급여 테이블

급여 조건 : 급여 > 0  , INT형

직원	급여(원)
1	5000000
2	-5000000
3	오백만원

2의 급여는 급여조건이 0보다 작기 때문에 비정상이다.

3의 급여는 급여를 INT형으로 작성하지 않았기 때문에 비정상이다.

 

이러한 논리적 모순이 존재하지 않는것이 바로 데이터 무결성이다.

 

<데이터 무결성의 유형 3가지>

- 개체 무결성 : 반드시 기본 키(primary key)가 있어야 함, 기본키로 설정된 필드(column)는 고유해야 하며 NULL값이 허용되면 안된다.

학번 (기본키)	이름	학과
1	홍길동	피아노과
1	베트맨	서양학과
NULL	헐크	정보통신학과

>> 기본키 (primary key)로 설정된 필드인 '학번'의 값이 중복되었기 떄문에 비정상이다.

>> 기본키에는 NULL값도 허용되지 않으므로 헐크의 학번도 비정상이다.

 

- 참조 무결성 : 참조 관계에 있는 두 테이블 간의 데이터가 항상 일관된 값을 갖도록 유지해야함.

참조키(외래키) 제약조건 사용

 

>> 만약 아래와 같이 관계된 테이블이 있는데

한테이블에서 데이터를 삭제했는데 두번째 테이블에서 삭제한 데이터와 관련된 값이 있으면 안된다.

 

- 도메인 무결성 : 컬럼 값의 범위가 일관성있게 유지 되는 것.

필드 타입, null허용 등을 정의 후, 올바른 데이터 입력이 이루어지는지 확인

 

>> 주민등록 번호 필드에서 알파벳이 입력되는 것은 도메인 무결성이 깨지는 경우이다.

 

도메인 무결성을 위해 사용하는 기법 : 데이터 형식 정의, CHECK 제약조건

 

데이터 무결성을 구현하기 위해 사용되는 QUERY 제약 조건 5가지

1) Primary Key 기본키  2) Foreign Key 참조(외래)키 3) Unique Key 고유키 4) Check 제약조건 5) Defult 기본값 제약조건

 

1) 기본키 제약 조건 (PRIMARY KEY)

- 기본키 제약조건은 행의 고유성을 강화하고, NULL 표시를 허용하지 않는다.

- 각 테이블에 기본키는 무조건 하나만

-  NULL을 허용하는 열에 기본키를 정의하려는 것은 RDBMS에 의해 거부 됨.

ALTER TABLE dbo.Employees   ADD CONSTRAINT PK_Employees   PRIMARY KEY(empid);

 

2) 고유 제약 조건 (UNIQUE KEY)

- 행들의 고유성을 강화시킨다.

- 기본키와 달리 한 테이블에 여러개의 고유키를 가질 수 있다.

- 한개의 NULL 값이 허용될 수 있다.

ALTER TABLE dbo.Employees   ADD CONSTRAINT UNQ_Employees_ssn   UNIQUE(ssn);

 

3) 참조키, 외래키 제약조건 (FOREIGN KEY)

 

두 테이블 사이의 관계를 선언함으로써, 데이터의 무결성을 보장해 주는 역할이다.

 

외래 키 관계를 설정 시 테이블이 다른 테이블(기준 테이블)에 의존한다.

 

테이블에 외래키 열에 대하여 데이터를 입력할 때는 기준 테이블을 참조해서 입력하기 때문에 기준테이블에 존재하는 데이터만 입력할 수있다.

 

외래키테이블이 참조하는 기준 테이블의 열은 기본키(Primary Key) / 고유키( UNIQUE) 제약 조건이 설정되어 있어야 한다. 한 열에서 같은 데이터가 여러개 존재할 경우, 참조 할때 무엇을 참조해야할지 모르기 때문이다.

 

< 특징 >

 

- 참조키(외래키)는 참조 무결성을 강화한다.

- 참조되(외래키)는 테이블의 후보키(기본키, 고유키에 속하는 column) 특성들에 연결된다.

- 목적 : 참조키 컬럼들의 값이 참조되는 컬럼들에 존재하는 값들로만 제한하고자 하는 것.

 

- 외래키는 두 테이블을 서로 연결하는 데 사용되는 키이다.

- 외래키가 포함된 테이블 = 자식 테이블

  외래키 값을 제공하는 테이블 = 부모 테이블

- NULL이거나 부모 테이블의 기본키 값과 동일해야한다. (참조 무결성 제약조건)

- 부모 테이블의 기본키, 고유키를 외래키로 지정 가능

 

참조키는 유일할 필요가 없다 => 실제로 유일하지 않은 경우가 많음 참조키의 값으로는 부모 테이블에 존재하는 키의 값만을 넣을 수 있다 => 참조 무결성 참조키를 사용하여 부모 테이블의 유일한 값을 참조한다. ( 예를들어, 부모테이블의 기본키를 참조 ) 참조키의 값이 부모테이블의 기본키일 필요는 없지만 유일해야 한다. 출처: https://namsieon.com/36 [남시언닷컴]

 

 

예제1

Orders 테이블의 empid 컬럼에서 Employees 테이블의 empid 컬럼을 참조

ALTER TANLE dbo.Orders ADD CONSTRAINT FK_Orders_Employees FOREIGN KEY(empid) REFERENCES dbo.Employees(orderid); );

dbo.Orders :  변경하고자 하는 테이블

FK_Orders_Employees :  참조키 이름. FK를 사용하며, Orders와 Employees 테이블을 엮는다는 의미로 지음.
FOREIGN KEY(empid) :  dbo.Orders 테이블에 empid라는 컬럼을 FORIEGN KEY로 설정한다.
REFERENCES dbo.Employees(orderid);  :  참조하는 컬럼은 dbo.Employees 테이블의 orderid 라는 컬럼이다.

 

 

예제2

Employees 테이블의 mgrid 컬럼의 값을 empid 컬럼에 존재하는 값으로 제한

ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Employees FORIEGN KEY(mgrid) REFERENCES dbo.Employees(empid);

 

4) 체크 제약 조건 (CHECK)

- 테이블에 행을 저장하거나 수정하기 위해 만족해야하는 조건을 정의할 수 있도록 해줌.

 

employees 테이블의 salary컬럼은 양수만 가능하도록하는 제약조건을 추가

ALTER TABLE dbo.Employees   ADD CONSTRAINT CHK_Employees_salary   CHECK(salary > 0.00 );

 

salary value를 0 이하의 값으로 설정하였기 떄문에 행이 업데이트 되지 않음.

 

5) 기본값 제약조건 (Default)

-행이 입력될 떄, 값이 명시되지 않을 경우에 자동으로 입력될 기본값을 지정하는 것.

-기본값 제약조건은 하나의 특성에 대해서만 설정할 수 있음.

 

Orders 테이블의 orderts 컬럼의 기본값으로 현재 날짜와 시간 값을 사용하도록 하는 제약조건 추카하는 코드

ALTER TABLE dbo.Orders   ADD CONSTRAINT DFT_Orders+orderts   DEFAULT(SYSDATETIME()) FOR orderts;

FOR 대상 컬럼

  SYSDATETIME : PC 시간 값을 읽는 시스템 함수, 빌트인 함수