SQL Server(第1章)テーブル削除テーブル作成プライマリ・キー制約、一意制約、外部キー制約、CHECK制約、デフォルト制約...
8390 ワード
1.Employees
/**
Employees
**/
USE TSQL2012
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,
lastname VARCHAR(30) NOT NULL,
hiredate DATE NOT NULL,
mgrid INT NULL,
ssn VARCHAR(20) NOT NULL,
salary MONEY NOT NULL
)
/**
**/
ALTER TABLE dbo.Employees ADD CONSTRAINT PK_Employees
PRIMARY KEY(empid);
/**
**/
ALTER TABLE dbo.Employees ADD CONSTRAINT UNQ_Employees_ssn
UNIQUE(ssn);
/**
**/
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid);
/**
CHECK
**/
ALTER TABLE dbo.Employees ADD CONSTRAINT CHK_Employees_salary
CHECK(salary>0.00)
/**
CHECK
**/
ALTER TABLE dbo.Employees DROP CONSTRAINT CHK_Employees_salary
2.Ordersオーダー表
/**
Orders
**/
USE TSQL2012
IF OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL,
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL,
orderts DATETIME2 NOT NULL,
qty INT NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
/**
**/
ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Employees FOREIGN KEY(empid)
REFERENCES dbo.Employees(empid);
/**
**/
ALTER TABLE dbo.Orders ADD CONSTRAINT DFT_Orders_orderts
DEFAULT(SYSDATETIME()) FOR orderts;
/**
CHECK
**/
ALTER TABLE dbo.Employees DROP CONSTRAINT DFT_Orders_orderts
個人的なメモで、問題があればみんなでコミュニケーションすることができます.
転載先:https://www.cnblogs.com/tiaoma888/p/8168806.html