SQL Server(第一章) 创建表 删除表 创建主键约束、唯一约束、外键约束、CHECK约束、默认约束

2018-01-02 09:07:27来源:cnblogs.com作者:渴死的鱼0521人点击

分享

1.Employees员工表
/**创建Employees员工表**/USE TSQL2012IF OBJECT_ID('dbo.Employees','U') IS NOT NULLDROP TABLE dbo.EmployeesCREATE 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_EmployeesPRIMARY KEY(empid);/**添加唯一约束**/ALTER TABLE dbo.Employees ADD CONSTRAINT UNQ_Employees_ssnUNIQUE(ssn);/**添加外键约束**/ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_EmployeesFOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid);/**添加CHECK约束**/ALTER TABLE dbo.Employees ADD CONSTRAINT CHK_Employees_salaryCHECK(salary>0.00)/**删除CHECK约束**/ALTER TABLE dbo.Employees DROP CONSTRAINT CHK_Employees_salary

2.Orders订单表

/**    创建Orders订单表**/USE TSQL2012IF 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_ordertsDEFAULT(SYSDATETIME()) FOR orderts;/**删除CHECK约束**/ALTER TABLE dbo.Employees DROP CONSTRAINT DFT_Orders_orderts

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台