当前位置:Gxlcms > 数据库问题 > SQL Server(第一章) 创建表 删除表 创建主键约束、唯一约束、外键约束、CHECK约束、默认约束

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

时间:2021-07-01 10:21:17 帮助过:10人阅读

  1. <span style="color: #008080">/*</span><span style="color: #008080">*
  2. 创建Employees员工表
  3. *</span><span style="color: #008080">*/</span>
  4. <span style="color: #0000ff">USE</span><span style="color: #000000"> TSQL2012
  5. </span><span style="color: #0000ff">IF</span> <span style="color: #ff00ff">OBJECT_ID</span>(<span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.Employees</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">U</span><span style="color: #ff0000">‘</span>) <span style="color: #0000ff">IS</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span>
  6. <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Employees
  7. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Employees
  8. (
  9. empid </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  10. firstname </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  11. lastname </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">30</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  12. hiredate DATE </span><span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  13. mgrid </span><span style="color: #0000ff">INT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  14. ssn </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  15. salary </span><span style="color: #0000ff">MONEY</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
  16. )
  17. </span><span style="color: #008080">/*</span><span style="color: #008080">*
  18. 添加主键约束
  19. *</span><span style="color: #008080">*/</span>
  20. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> PK_Employees
  21. </span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">(empid);
  22. </span><span style="color: #008080">/*</span><span style="color: #008080">*
  23. 添加唯一约束
  24. *</span><span style="color: #008080">*/</span>
  25. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> UNQ_Employees_ssn
  26. </span><span style="color: #0000ff">UNIQUE</span><span style="color: #000000">(ssn);
  27. </span><span style="color: #008080">/*</span><span style="color: #008080">*
  28. 添加外键约束
  29. *</span><span style="color: #008080">*/</span>
  30. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> FK_Employees_Employees
  31. </span><span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span>(mgrid) <span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> dbo.Employees(empid);
  32. </span><span style="color: #008080">/*</span><span style="color: #008080">*
  33. 添加CHECK约束
  34. *</span><span style="color: #008080">*/</span>
  35. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> CHK_Employees_salary
  36. </span><span style="color: #0000ff">CHECK</span>(salary<span style="color: #808080">></span><span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000">)
  37. </span><span style="color: #008080">/*</span><span style="color: #008080">*
  38. 删除CHECK约束
  39. *</span><span style="color: #008080">*/</span>
  40. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">CONSTRAINT</span> CHK_Employees_salary

 

2.Orders订单表

  1. <span style="color: #008080">/*</span><span style="color: #008080">*
  2. 创建Orders订单表
  3. *</span><span style="color: #008080">*/</span>
  4. <span style="color: #0000ff">USE</span><span style="color: #000000"> TSQL2012
  5. </span><span style="color: #0000ff">IF</span> <span style="color: #ff00ff">OBJECT_ID</span>(<span style="color: #ff0000">‘</span><span style="color: #ff0000">dbo.Orders</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘</span><span style="color: #ff0000">U</span><span style="color: #ff0000">‘</span>) <span style="color: #0000ff">IS</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span> <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Orders;
  6. </span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Orders
  7. (
  8. orderid </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  9. empid </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  10. custid </span><span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">10</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  11. orderts DATETIME2 </span><span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  12. qty </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  13. </span><span style="color: #0000ff">CONSTRAINT</span> PK_Orders <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">(orderid)
  14. );
  15. </span><span style="color: #008080">/*</span><span style="color: #008080">*
  16. 添加主键约束
  17. *</span><span style="color: #008080">*/</span>
  18. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Orders <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span> FK_Orders_Employees <span style="color: #0000ff">FOREIGN</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">(empid)
  19. </span><span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> dbo.Employees(empid);
  20. </span><span style="color: #008080">/*</span><span style="color: #008080">*
  21. 添加默认约束
  22. *</span><span style="color: #008080">*/</span>
  23. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Orders <span style="color: #0000ff">ADD</span> <span style="color: #0000ff">CONSTRAINT</span><span style="color: #000000"> DFT_Orders_orderts
  24. </span><span style="color: #0000ff">DEFAULT</span>(SYSDATETIME()) <span style="color: #0000ff">FOR</span><span style="color: #000000"> orderts;
  25. </span><span style="color: #008080">/*</span><span style="color: #008080">*
  26. 删除CHECK约束
  27. *</span><span style="color: #008080">*/</span>
  28. <span style="color: #0000ff">ALTER</span> <span style="color: #0000ff">TABLE</span> dbo.Employees <span style="color: #0000ff">DROP</span> <span style="color: #0000ff">CONSTRAINT</span> DFT_Orders_orderts

 

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

标签:int   pos   creat   gpo   ble   删除   time   arc   主键约束   

人气教程排行