제약 조건

SELECT OBJECT_NAME(parent_object_id) AS 표, name AS 제약, definition AS 정의
FROM sys.check_constraints;

SELECT OBJECT_NAME(dc.parent_object_id) AS 표, c.name AS 열,
       dc.name AS 제약, dc.definition AS 정의
FROM sys.default_constraints dc
    JOIN sys.columns c ON c.object_id = dc.parent_object_id
                        AND c.column_id = dc.parent_column_id;

INSERT INTO Board.FILES (post_num, origin_name, size_bytes, reg_date)
VALUES (1, N'빈파일.txt', 0, '2026-01-01');

CREATE TABLE Board.T_CK (
    num int IDENTITY(1,1) PRIMARY KEY,
    score int NULL,
    CONSTRAINT CK_TCK_score CHECK (score > 0)
);

INSERT INTO Board.T_CK (score) VALUES (10);     -- 들어갑니다
INSERT INTO Board.T_CK (score) VALUES (NULL);   -- 들어갑니다 (!)
INSERT INTO Board.T_CK (score) VALUES (0);      -- 막힙니다

CREATE TABLE Board.T_RANGE (
    num int IDENTITY(1,1) PRIMARY KEY,
    start_date date NOT NULL,
    end_date date NULL,
    CONSTRAINT CK_TRANGE_period
        CHECK (end_date IS NULL OR end_date >= start_date)
);

INSERT INTO Board.T_RANGE VALUES ('2026-01-01', '2026-01-31');  -- 들어갑니다
INSERT INTO Board.T_RANGE VALUES ('2026-02-01', NULL);          -- 아직 안 끝난 기간
INSERT INTO Board.T_RANGE VALUES ('2026-03-01', '2026-02-01');  -- 막힙니다

INSERT INTO Member.USERS (user_id, nickname, email, reg_date)
VALUES ('hong', N'또다른홍길동', NULL, '2026-01-01');

CREATE TABLE Board.T_UQ (
    num int IDENTITY(1,1) PRIMARY KEY,
    email varchar(100) NULL,
    CONSTRAINT UQ_TUQ_email UNIQUE (email)
);

INSERT INTO Board.T_UQ (email) VALUES ('a@x.com');  -- 들어갑니다
INSERT INTO Board.T_UQ (email) VALUES (NULL);       -- 들어갑니다
INSERT INTO Board.T_UQ (email) VALUES (NULL);       -- 막힙니다 (!)

CREATE TABLE Board.T_FI (
    num int IDENTITY(1,1) PRIMARY KEY,
    email varchar(100) NULL
);

CREATE UNIQUE NONCLUSTERED INDEX UX_TFI_email
    ON Board.T_FI(email) WHERE email IS NOT NULL;

INSERT INTO Board.T_FI (email) VALUES ('a@x.com'), (NULL), (NULL), (NULL);
INSERT INTO Board.T_FI (email) VALUES ('a@x.com');   -- 막힙니다

CREATE TABLE Board.T_MAIN (
    num int IDENTITY(1,1) PRIMARY KEY,
    board_num int NOT NULL,
    title nvarchar(50) NOT NULL,
    is_main bit NOT NULL DEFAULT 0
);

-- 대표인 행만 인덱스에 담습니다.
CREATE UNIQUE NONCLUSTERED INDEX UX_TMAIN_main
    ON Board.T_MAIN(board_num) WHERE is_main = 1;

INSERT INTO Board.T_MAIN (board_num, title, is_main) VALUES
    (1, N'공지 대표', 1), (1, N'일반1', 0), (1, N'일반2', 0),
    (2, N'자유 대표', 1), (2, N'일반3', 0);

-- 1번 게시판에 대표를 하나 더 넣어 봅니다.
INSERT INTO Board.T_MAIN (board_num, title, is_main) VALUES (1, N'공지 대표2', 1);

CREATE TABLE Board.T_DF (
    num int IDENTITY(1,1) PRIMARY KEY,
    memo nvarchar(20) NULL CONSTRAINT DF_TDF_memo DEFAULT N'기본값',
    cnt int NOT NULL CONSTRAINT DF_TDF_cnt DEFAULT 0
);

INSERT INTO Board.T_DF (cnt) VALUES (5);              -- memo 를 적지 않음
INSERT INTO Board.T_DF (memo, cnt) VALUES (NULL, 6);   -- NULL 을 명시
INSERT INTO Board.T_DF (memo, cnt) VALUES (DEFAULT, 7); -- DEFAULT 를 명시
INSERT INTO Board.T_DF DEFAULT VALUES;                -- 전부 기본값

CREATE TABLE Board.T_NM (
    num int NOT NULL PRIMARY KEY,
    cnt int NOT NULL DEFAULT 0,
    CHECK (cnt >= 0)
);

SELECT name AS 자동이름, type_desc AS 종류 FROM sys.objects
WHERE parent_object_id = OBJECT_ID('Board.T_NM') AND type IN ('D', 'C', 'PK');