查看: 2137|回复: 0

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

发表于 2018-1-8 08:00:02
  1. <br>1.Employees员工表
复制代码
  1. /**
  2. 创建Employees员工表
  3. **/
  4. USE TSQL2012
  5. IF OBJECT_ID('dbo.Employees','U') IS NOT NULL
  6. DROP TABLE dbo.Employees
  7. CREATE TABLE dbo.Employees
  8. (
  9. empid INT NOT NULL,
  10. firstname VARCHAR(30) NOT NULL,
  11. lastname VARCHAR(30) NOT NULL,
  12. hiredate DATE NOT NULL,
  13. mgrid INT NULL,
  14. ssn VARCHAR(20) NOT NULL,
  15. salary MONEY NOT NULL
  16. )
  17. /**
  18. 添加主键约束
  19. **/
  20. ALTER TABLE dbo.Employees ADD CONSTRAINT PK_Employees
  21. PRIMARY KEY(empid);
  22. /**
  23. 添加唯一约束
  24. **/
  25. ALTER TABLE dbo.Employees ADD CONSTRAINT UNQ_Employees_ssn
  26. UNIQUE(ssn);
  27. /**
  28. 添加外键约束
  29. **/
  30. ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Employees
  31. FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid);
  32. /**
  33. 添加CHECK约束
  34. **/
  35. ALTER TABLE dbo.Employees ADD CONSTRAINT CHK_Employees_salary
  36. CHECK(salary>0.00)
  37. /**
  38. 删除CHECK约束
  39. **/
  40. ALTER TABLE dbo.Employees DROP CONSTRAINT CHK_Employees_salary
复制代码

2.Orders订单表

  1. /**
  2. 创建Orders订单表
  3. **/
  4. USE TSQL2012
  5. IF OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;
  6. CREATE TABLE dbo.Orders
  7. (
  8. orderid INT NOT NULL,
  9. empid INT NOT NULL,
  10. custid VARCHAR(10) NOT NULL,
  11. orderts DATETIME2 NOT NULL,
  12. qty INT NOT NULL,
  13. CONSTRAINT PK_Orders PRIMARY KEY(orderid)
  14. );
  15. /**
  16. 添加主键约束
  17. **/
  18. ALTER TABLE dbo.Orders ADD CONSTRAINT FK_Orders_Employees FOREIGN KEY(empid)
  19. REFERENCES dbo.Employees(empid);
  20. /**
  21. 添加默认约束
  22. **/
  23. ALTER TABLE dbo.Orders ADD CONSTRAINT DFT_Orders_orderts
  24. DEFAULT(SYSDATETIME()) FOR orderts;
  25. /**
  26. 删除CHECK约束
  27. **/
  28. ALTER TABLE dbo.Employees DROP CONSTRAINT DFT_Orders_orderts
复制代码

纯属个人笔记,如有问题大家可以一起沟通。



回复

使用道具 举报