[MS-SQL] 필수 쿼리

테이블 생성 (PK와 같이)

CREATE TABLE Person (
    Id INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT CONSTRAINT DF_Person_Age DEFAULT 0,
    CONSTRAINT PK_Persons PRIMARY KEY (Id, LastName)
);

 

테이블 생성 (PK 없이)

CREATE TABLE Person (
    Id INT NOT NULL,
    LastName VARCHAR(255) NOT NULL,
    FirstName VARCHAR(255),
    Age INT
);

 

PK 추가

ALTER TABLE Person ADD CONSTRAINT PK_Person PRIMARY KEY (Id);

 

PK, FK 제거

ALTER TABLE Person DROP CONSTRAINT PK_Person;

 

FK 추가

ALTER TABLE Person ADD CONSTRAINT FK_Person FOREIGN KEY (ParentIdx) REFERENCES Parent ([Idx])

 

테이블 삭제

DROP TABLE Person;

 

컬럼 추가

ALTER TABLE Person ADD Email VARCHAR(255) NOT NULL CONSTRAINT DF_Person_Email DEFAULT '';

 

컬럼 변경

ALTER TABLE Person ALTER COLUMN Email VARCHAR(100) NOT NULL CONSTRAINT DF_Person_Email DEFAULT '';

 

컬럼명 변경

EXEC sp_rename 'dbo.Person.Email', 'EmailAddress', 'COLUMN';

 

컬럼 삭제

ALTER TABLE Person DROP COLUMN Email;

 

인덱스 추가

CREATE INDEX IX_Person_LastName_FirstName ON Person (LastName, FirstName);

 

인덱스 제거

DROP INDEX IX_Person_LastName_FirstName ON Person;

 

DEFAULT 제약조건 제거

ALTER TABLE Person DROP CONSTRAINT DF_Person_Email;