SQL server(6) 제약조건

제약 조건 - 다른DB에 적용X, DB밖을 벗어 날 수 없다.
primary key - 절대 중복값X, null값X, 전체테이블에 오직한개
unuque key - PK를 대신에 사용, null값을한번허용
                   테이블에 여러개생성가능(259개),중복X
check  - ex) 나이>=20
default -
foreign key - 만들어 놓은 값 이외에 다른 값이 들어 가지 않
                  도록 해주는 키...
--------------
기본값 -
규칙

CLUSTERED - 자동으로 정렬, INDEX의 기능.
                   한번들어가면 잘 바뀌지 않는 데이터 사용 
NONCLUSTERED - 무작위 정렬
                   수시로 바뀐다.

한테이블에 CLUSTERED는 한개만... 나머지는
NONCLUSTERED형태로 해줘야 한다... 충돌....

PRIMARY키를 생성하면 자동으로 INDEX가 추가 된다....
PK_ID <- ID가 INDEX....
INDEX는 자동정렬 기능이 있다.

 

DROP DATABASE MYDB00
CREATE DATABASE MYDB00

1. 기본키를 정의 하는 예
USE MYDB00
GO
CREATE TABLE MYTABLE00
(
   ID    INT   NOT NULL  PRIMARY KEY CLUSTERED,
   NAME  CHAR(10)   NOT NULL
)
GO
EXEC SP_HELP MYTABLE00

다음 경우는
DROP TABLE MYTABLE00

CREATE TABLE MYTABLE00
(
   ID    INT   NOT NULL,
   NAME  CHAR(10)   NOT NULL
     CONSTRAINT PK_ID PRIMARY KEY(ID)
)

CREATE TABLE MYTABLE00
(
   ID    INT   NOT NULL   CONSTRAINT PK_ID PRIMARY KEY(ID),
   NAME  CHAR(10)   NOT NULL
)
GO
EXEC SP_HELP MYTABLE00

2.
INSERT MYTABLE00 (ID,NAME) VALUES(12,'김에리')
SELECT*FROM MYTABLE00

INSERT MYTABLE00 (ID,NAME) VALUES(12,'김에리')  -  PRIMARY KEY값은 중복값을 갖지 않기 때문에 실행이 되지 않는다.
INSERT MYTABLE00 (ID,NAME) VALUES('김에리')  -  PRIMARY KEY값은 NULL값을 갖지 않기 때문에 실행이 되지 않는다.

INSERT MYTABLE00 (ID,NAME) VALUES(11,'이에리')
SELECT*FROM MYTABLE00

PRIMARY KEY값을 삭제 하는 예
ALTER TABLE MYTABLE00
  DROP CONSTRAINT PK_ID

ALTER TABLE MYTABLE00
  DROP CONSTRAINT PK_MYTABLE00_619B8048

ALTER TABLE 문을 사용하여 기존 테이블에 기본 키 제약을 추가하는 예
DELETE MYTABLE00  -  안에 중복되는 정보가 있기때문에 수정이 안된다.
update MYTABLE00 set ID='21' where ID='11'  - 그렇게 때문에 안의 정보를 지우거나 수정해 주면 기본 키 제약을 추가할수 있다.

ALTER TABLE MYTABLE00
  ADD CONSTRAINT PK_ID
     PRIMARY KEY(ID)
EXEC SP_HELP MYTABLE00
SELECT*FROM MYTABLE00

DROP TABLE MYTABLE01

CREATE TABLE MYTABLE01
(
NAME CHAR(10) NOT NULL
)

--테이블 'MYTABLE01'에 ID 열을 추가하고 기본 키로 정의한다.

ALTER TABLE MYTABLE01
ADD
    ID INT IDENTITY(1,1) CONSTRAINT MYTABLE01_PK_ID PRIMARY KEY
GO
EXEC SP_HELP MYTABLE01

ALTER TABLE MYTABLE01
  DROP CONSTRAINT MYTABLE01_PK_ID

ALTER TABLE MYTABLE01
ADD
    username  varchar(10) CONSTRAINT MYTABLE01_PK_ID PRIMARY KEY

SELECT * FROM MYTABLE01
EXEC SP_HELP MYTABLE01
INSERT MYTABLE01 (username,NAME) VALUES('aa','김에리')
INSERT MYTABLE01 (username,NAME) VALUES('ku','김애리')

TEST-----------------------------------------------------
sp_help
drop table mytable00

CREATE TABLE MYTABLE00
(
NAME CHAR(10) NOT NULL
)

ALTER TABLE MYTABLE00
ADD
    ID INT IDENTITY(1,1)

ALTER TABLE MYTABLE00
ADD
    username  varchar(10) CONSTRAINT MYTABLE00_PK_ID PRIMARY KEY

INSERT MYTABLE00 (username,ID,NAME) VALUES('aa',1,'김에리')
INSERT MYTABLE00 (username,ID,NAME) VALUES('ku',1,'김애리')
SELECT * FROM MYTABLE00
EXEC SP_HELP MYTABLE00

END---------------------------------------------------------------------

2. UNIQUE 제약 조건

USE MYDB00
GO
CREATE TABLE MYTABLE03
(
  ID   INT   NOT NULL PRIMARY KEY CLUSTERED,
  NAME   CHAR(10)  NOT NULL,
  JUMIN  CHAR(10)  NULL  UNIQUE  NONCLUSTERED
)
GO
EXEC SP_HELP MYTABLE03

DROP TABLE MYTABLE03
CREATE TABLE MYTABLE03
(
  ID   INT   NOT NULL,
  NAME   CHAR(10)  NOT NULL,
  JUMIN  CHAR(13)  NULL,
      CONSTRAINT PK_MYTABLE03_ID PRIMARY KEY CLUSTERED (ID),
      CONSTRAINT UNI_MYTABLE03_JUMIN UNIQUE NONCLUSTERED (JUMIN)
)
GO
EXEC SP_HELP MYTABLE03

INSERT MYTABLE03 (ID,NAME,JUMIN) VALUES(12,'KIMYERIM','123456789')
SELECT * FROM MYTABLE03
--중복된 주민등록번호를 갖는 행을 추가하면 들어가지 않는다.
    INSERT MYTABLE03 (ID,NAME,JUMIN) VALUES(13,'김예림','123456789')

UNIQUE 제약이 정의된 열은 NULL을 허용할 수 있으면 하나의 NULL을 허용한다.
INSERT MYTABLE03 (ID,NAME) VALUES(13,'김예림')
SELECT * FROM MYTABLE03
두번의 NULL은 안된다...
INSERT MYTABLE03 (ID,NAME) VALUES(14,'김예린')

제약 조건 삭제 예
ALTER TABLE MYTABLE03
  DROP CONSTRAINT UNI_MYTABLE03_JUMIN
삭제후 NULL값이 입력 된다.
INSERT MYTABLE03 (ID,NAME) VALUES(14,'김예린')
SELECT * FROM MYTABLE03

다시 추가 하는 예
DELETE MYTABLE03
ALTER TABLE MYTABLE03
  ADD CONSTRAINT UNI_JUMIN UNIQUE(JUMIN)

3. DEFAULT 의 예
CREATE TABLE MYTABLE05
(
  ID   INT   NOT NULL  PRIMARY KEY,
  NAME   CHAR(12)   NOT NULL,
  TEL    CHAR(20)   DEFAULT '000-0000',
  AGE  INT    DEFAULT 0,
  REGDATE  DATETIME  DEFAULT GETDATE()
)
GO
EXEC SP_HELP MYTABLE05

다음은 사용자가 각 기본값 제약에 대하여 이름을 지정하는 예
DROP TABLE MYTABLE05
CREATE TABLE MYTABLE05
(
  ID   INT   NOT NULL  PRIMARY KEY,
  NAME   CHAR(12)   NOT NULL,
  TEL    CHAR(20)   CONSTRAINT MyTABLE05_DEF_TEL DEFAULT '000-0000',
  AGE  INT     CONSTRAINT MyTABLE05_DEF_AGE DEFAULT 0,
  REGDATE  DATETIME   CONSTRAINT MyTABLE05_DEF_REGDATE DEFAULT GETDATE()
)
GO
EXEC SP_HELP MYTABLE05

INSERT MYTABLE05 (ID,NAME) VALUES(12,'김에린')
INSERT MYTABLE05 (ID,NAME) VALUES(1,'홍씨')
INSERT MYTABLE05 (ID,NAME) VALUES(2,'김씨')
INSERT MYTABLE05 (ID,NAME,TEL,AGE) VALUES(3,'김C','123-4567',30)
INSERT MYTABLE05 (ID,NAME,TEL) VALUES(4,'김C','123-4567')
INSERT MYTABLE05 (ID,NAME,AGE) VALUES(5,'홍C',45)
INSERT MYTABLE05 (ID,NAME,AGE) VALUES(6,'홍김C',4)
SELECT*FROM MYTABLE05

제약조건 삭제 예
ALTER TABLE MYTABLE05
DROP CONSTRAINT
MYTABLE05_DEF_AGE, MYTABLE05_DEF_TEL

제약조건 추가 예
ALTER TABLE MYTABLE05
ADD
   CONSTRAINT MYTABLE05_DEF_AGE DEFAULT 0 FOR AGE,
   CONSTRAINT MYTABLE05_DEF_TEL DEFAULT '000-0000' FOR TEL
EXEC SP_HELP MYTABLE05

WITH VALUES를 사용하여 열을 추가할 경우 테이블에 존재하는 기존 행에 제약조건을 추가 할 수 있다...

ALTER TABLE MYTABLE05
 ADD LEVEL VARCHAR(50)
   CONSTRAINT MYTABLE05_DF_LEVEL DEFAULT '회원'  WITH VALUES
GO
SELECT*FROM MYTABLE05

 

4. CHECK의 예
CREATE TABLE MYTABLE06
(
  ID    INT  NOT NULL  PRIMARY KEY,
  NAME CHAR(10)  NOT NULL,
  ZIP   CHAR(7)   NULL   CHECK (ZIP LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]'),
  AGE  INT  NULL  CHECK (AGE>=0 AND AGE<=60),
  JOB  CHAR(20) NULL  CHECK (JOB IN('학생','회사원','공무원','기타'))
)
GO
EXEC SP_HELP MYTABLE06
SELECT*FROM MYTABLE06

INSERT MYTABLE06 (ID, NAME, ZIP, AGE, JOB) VALUES(12,'김예림','100-153',56,'기타')
SELECT*FROM MYTABLE06
INSERT MYTABLE06 (ID,NAME,ZIP,AGE,JOB) VALUES(13,'김은희','100153',70,'자영업')   ---->  숫자사이에 - 이 빠져 오류
INSERT MYTABLE06 (ID,NAME,ZIP,AGE,JOB) VALUES(13,'김은희','100-153',70,'자영업')  ---->  CHECK의 제한이 60으로 되어 있는데 70이라서 오류
INSERT MYTABLE06 (ID,NAME,ZIP,AGE,JOB) VALUES(13,'김은희','100-153',30,'자영업')  ----->  JOB CHECK 제한에 자영업이 없어서 오류
INSERT MYTABLE06 (ID,NAME,ZIP,AGE,JOB) VALUES(13,'김은희','100-153',30,'회사원')
SELECT*FROM MYTABLE06

CREATE TABLE MYTABLE07
(
   ID  INT NOT NULL  PRIMARY KEY,
   NAME  CHAR(10)   NOT NULL,
   ZIP    CHAR(7)  NULL,
   AGE   INT   NULL,
   JOB   CHAR(20)  NULL
)

INSERT MYTABLE07 (ID,NAME,ZIP,AGE,JOB) VALUES(12,'김에림','100-153',2,'학생')
INSERT MYTABLE07 (ID,NAME,ZIP,AGE,JOB) VALUES(13,'김은희','100-153',26,'연예인')

SELECT*FROM MYTABLE07

WITH NOCHECK....데이터에 대하여 유효성 검사를 하지 않고 CHECK 제약을 실행
ALTER TABLE MYTABLE07 WITH NOCHECK
ADD
  CONSTRAINT MYTABLE07_CK_ZIP
           CHECK(ZIP LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]'),
  CONSTRAINT MYTABLE07_CK_AGE
           CHECK(AGE>=20 AND AGE<=60),
  CONSTRAINT MYTABLE07_CK_JOB
           CHECK(JOB IN('학생','회사원','공무원','기타'))

INSERT MYTABLE07 (ID,NAME,ZIP,AGE,JOB) VALUES(14,'김경미','100-153',26,'회사원')

CREATE TABLE 고객정보
(
  USERID  CHAR(10)   NOT NULL   PRIMARY KEY,
  USERNAME  CHAR(20)  NOT NULL,
  AGE   INT    DEFAULT 0
)

CREATE TABLE 판매정보
(
   NUM  INT  IDENTITY(1,1)  PRIMARY KEY,
   USERID   CHAR(10)  NOT NULL  REFERENCES 고객정보(USERID),
   PRODUCTNAME  CHAR(30)  NOT NULL,
   PRICE    MONEY   DEFAULT 0,
   BUYDATE   DATETIME DEFAULT GETDATE()
)
GO
EXEC SP_HELPCONSTRAINT 판매정보

기본테이블에 먼저 데이터를 추가한다.
INSERT 고객정보 VALUES('A1234','김예린',2)
INSERT 고객정보 VALUES('B1234','김예림',32)
SELECT*FROM 고객정보
SELECT*FROM 판매정보

INSERT 판매정보 VALUES('A1234','대형냉장고',654000,DEFAULT)
SELECT*FROM 고객정보
SELECT*FROM 판매정보
INSERT 판매정보 VALUES('C1234','중형냉장고',354000,DEFAULT)  -> 고객정보에 C1234가 없기 때문에 오류

고객정보 테이블에서 아이디를 변경하면 오류가 발생
UPDATE 고객정보 SET USERID='A1234' WHERE USERID='Z1234'  --->무결성의 오류, 데이터가 이미 들어가 있기때문에...

판매정보 테이블에 관련된 행이 존재하는 고객정보 테이블의 행을 삭제 하면 바꿀 수 있다.
DELETE 판매정보 WHERE USERID='A1234'
SELECT*FROM 고객정보
SELECT*FROM 판매정보