Foreign KEY, VIEW

DROP DATABASE if EXISTS tabledb;
CREATE DATABASE tabledb;
 
 
 
 
CREATE TABLE `usertbl` (
    `userid` CHAR(8) NOT NULL,           -- 사용자 ID, 기본 키로 사용
    `name` VARCHAR(10) NOT NULL,         -- 이름, 10자까지 허용
    `birthyear` INT(11) NOT NULL,        -- 출생 연도, 11자리까지
    `addr` CHAR(2) NOT NULL,             -- 주소, 2자까지 허용
    `mobile1` CHAR(3) DEFAULT NULL,      -- 휴대전화1, 3자리까지
    `mobile2` CHAR(8) DEFAULT NULL,      -- 휴대전화2, 8자리까지
    `height` SMALLINT(6) DEFAULT NULL,   -- 키, 작은 정수로 설정
    `mDate` DATE DEFAULT NULL,           -- 날짜 필드
    CONSTRAINT pk_usertbl_userid PRIMARY KEY (`userid`)  -- 기본 키 제약 조건
);
 

 

tabledb를 삭제하고 다시 데이터베이스를 만든다. 

 

CREATE TABLE `usertbl` (
    `userid` CHAR(8) NOT NULL,           -- 사용자 ID, 기본 키로 사용
    `name` VARCHAR(10) NOT NULL,         -- 이름, 10자까지 허용
    `birthyear` INT(11) NOT NULL,        -- 출생 연도, 11자리까지
    `addr` CHAR(2) NOT NULL,             -- 주소, 2자까지 허용
    `mobile1` CHAR(3) DEFAULT NULL,      -- 휴대전화1, 3자리까지
    `mobile2` CHAR(8) DEFAULT NULL,      -- 휴대전화2, 8자리까지
    `height` SMALLINT(6) DEFAULT NULL,   -- 키, 작은 정수로 설정
    `mDate` DATE DEFAULT NULL,           -- 날짜 필드
    CONSTRAINT pk_usertbl_userid PRIMARY KEY (`userid`)  -- 기본 키 제약 조건
);

NOT NULL은 NULL값을 허용하지 않는다는 것, NULL은 NULL값이 들어와도 괜찮다는 것이다.

 

1. 기본키 pk 제약 조건

   unique(중복 x), NOT NUll 두개의 조건을 만족
   auto_increment 설정하면 insert 할 때 null을 넣는다.
   pk로 설정하면 자동으로 클러스터형 인덱스를 생성한다. (클러스터형 인덱스란 한 테이블에서 하나만 존재한다는 것이다. primary key로 등록된 열에 대해서 자동으로 인덱스를 생성한다.)

 

CREATE TABLE usertbl2( userid CHAR(8) NOT NULL,
                              NAME VARCHAR(10) NOT NULL,
                              birthyear INT NOT NULL);
 
 
ALTER TABLE usertbl2
        ADD CONSTRAINT pk_usertbl_userid PRIMARY KEY (userid);
        
        

 

 ALTER TABLE  "테이블명" ~ 구문을 활용해 이미 만들어진 테이블에서 primary key를 설정할 수 있다.

 

 복합키 : 2개이상의 컬럼을 조합해서 기본키로 설정
 PIRMARY KEY(컬럼 1, 컬럼 2)

 외래키 FK : 두 테이블간에 관계형성, 중복가능
  반드시 참조 테이블에 컬럼이 pk로 설정이 되어있어야 한다.
 부모테이블의 참조컬럼을 삭제하려면
 자식테이블의 fk값부터 삭제한다.

DROP TABLE if EXISTS buytbl, usertbl;
    
 
 
CREATE TABLE `usertbl` (
   `userid` CHAR(8) ,
   `name` VARCHAR(10) ,
   `birthyear` INT(11) ,
   `addr` CHAR(2)  ,
   `mobile1` CHAR(3) ,
   `mobile2` CHAR(8) ,
   `height` SMALLINT(6) ,
   `mDate` DATE 
);
 
CREATE TABLE `buytbl` (
   `num` INT(11) AUTO_INCREMENT PRIMARY KEY,
   `userid` CHAR(8) ,
   `prodname` CHAR(6) ,
   `groupname` CHAR(4)  ,
   `price` INT(11) ,
   `amount` SMALLINT(6)
);

 

DROP DATABASE문과 마찬가지로 DROP TABLE문을 활용해 테이블을 삭제할 수도 있다.

 

ALTER TABLE usertbl ADD CONSTRAINT pk_usertbl_userid PRIMARY KEY (userid); DELETE FROM buytbl WHERE userid = 'BBK'; INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200,5); SHOW KEYS FROM buytbl;

코드 상에는 없지만 다음과 같이 외래키를 이용할 수 있다. 

 

 

 

 

 

2. View

 

뷰 사용하기 : 가상의 테이블 개념 
사용범위 : 조회기능 외에 추가, 수정, 삭제 가능하다.--> 원본 테이블을 대상으로
보안강화 효과 : 사용자가 원본테이블에는 접근하지 못하고 뷰에만 접근하도록 권한을 부여할 수 있음.

 

 

CREATE VIEW v_usertbl
AS SELECT userid, NAME, addr FROM usertbl;
 
SELECT *
FROM v_usertbl
WHERE userid = 'JYP';

뷰를 만들고 이미 만들어진 테이블에서 필요한 내용을 복사해서 뷰에서 필요한 조건을 콘솔에 출력한다.

 

 

CREATE TABLE tb2(
a INT PRIMARY KEY,
b INT UNIQUE,
c INT UNIQUE ,
d INT 
 
);

 

a를 기본키(클러스터 인덱스)로 b, c는 보조 인덱스로 활용하는 것이다. 클러스터 인덱스는 기본키로 인덱스를 설정한다.

 

클러스터 인덱스는 하나만 있어야 하며 보조 인덱스는 여러개 있어도된다.

 

CREATE TABLE tb4(
a INT UNIQUE,
b INT UNIQUE NOT NULL, -- b 가 맨 위로 올라옴, -> 유니크 _ 낫널 = 클러스터형 인덱스 생성
c INT UNIQUE ,
d INT 
 
);
SHOW INDEX FROM tb4;
 
 
 
CREATE TABLE tb5(
a INT UNIQUE,
b INT UNIQUE NOT NULL, -- b 가 맨 위로 올라옴, -> 유니크 _ 낫널 = 클러스터형 인덱스 생성
c INT UNIQUE ,
d INT PRIMARY KEY
 
);

NOT NULL로 설정하면 클러스터 인덱스로 만들 수 있지만 PRIMARY KEY라고 명시를 해둔 변수가 있으면 PRIMARY 키가 우선해서 클러스터형 인덱스가 된다.