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

<span style="color: #008080">/*</span><span style="color: #008080">*
创建Employees员工表
*</span><span style="color: #008080">*/</span>
<span style="color: #0000ff">USE</span><span style="color: #000000"> TSQL2012
</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>
<span style="color: #0000ff">DROP</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Employees
</span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Employees
(
    empid    </span><span style="color: #0000ff">INT</span>    <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    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">,
    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">,
    hiredate    DATE    </span><span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    mgrid    </span><span style="color: #0000ff">INT</span>    <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    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">,
    salary    </span><span style="color: #0000ff">MONEY</span>    <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">
)
</span><span style="color: #008080">/*</span><span style="color: #008080">*
添加主键约束
*</span><span style="color: #008080">*/</span>
<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
</span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000">(empid);
</span><span style="color: #008080">/*</span><span style="color: #008080">*
添加唯一约束
*</span><span style="color: #008080">*/</span>
<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
</span><span style="color: #0000ff">UNIQUE</span><span style="color: #000000">(ssn);
</span><span style="color: #008080">/*</span><span style="color: #008080">*
添加外键约束
*</span><span style="color: #008080">*/</span>
<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
</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);
</span><span style="color: #008080">/*</span><span style="color: #008080">*
添加CHECK约束
*</span><span style="color: #008080">*/</span>
<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
</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">)
</span><span style="color: #008080">/*</span><span style="color: #008080">*
删除CHECK约束
*</span><span style="color: #008080">*/</span>
<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订单表

<span style="color: #008080">/*</span><span style="color: #008080">*
    创建Orders订单表
*</span><span style="color: #008080">*/</span>
<span style="color: #0000ff">USE</span><span style="color: #000000"> TSQL2012
</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;
</span><span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> dbo.Orders
(
    orderid    </span><span style="color: #0000ff">INT</span>    <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    empid    </span><span style="color: #0000ff">INT</span> <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    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">,
    orderts    DATETIME2    </span><span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    qty    </span><span style="color: #0000ff">INT</span>    <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
    </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)
);
</span><span style="color: #008080">/*</span><span style="color: #008080">*
添加主键约束
*</span><span style="color: #008080">*/</span>
<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)
</span><span style="color: #0000ff">REFERENCES</span><span style="color: #000000"> dbo.Employees(empid);
</span><span style="color: #008080">/*</span><span style="color: #008080">*
添加默认约束
*</span><span style="color: #008080">*/</span>
<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
</span><span style="color: #0000ff">DEFAULT</span>(SYSDATETIME()) <span style="color: #0000ff">FOR</span><span style="color: #000000"> orderts;
</span><span style="color: #008080">/*</span><span style="color: #008080">*
删除CHECK约束
*</span><span style="color: #008080">*/</span>
<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   主键约束