Chapter 01 - Background to T-SQL Querying and Programming-优快云博客

本文详细介绍了如何使用SQL Server 2008创建数据库和表，并实现数据完整性，包括主键、唯一约束、外键和默认值的设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

---------------------------------------------------------------------
-- Microsoft SQL Server 2008 T-SQL Fundamentals
-- Chapter 1 - Background to T-SQL Querying and Programming
-- ?2008 Itzik Ben-Gan
---------------------------------------------------------------------

---------------------------------------------------------------------
-- Creating Tables
---------------------------------------------------------------------

-- Create a database called testdb
IF DB_ID('testdb') IS NULL
CREATE DATABASE testdb;
GO

-- Create table Employees
USE testdb;

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
);

---------------------------------------------------------------------
-- Data Integrity
---------------------------------------------------------------------

-- Primary key
ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employees
PRIMARY KEY(empid);

-- Unique
ALTER TABLE dbo.Employees
ADD CONSTRAINT UNQ_Employees_ssn
UNIQUE(ssn);

-- Foreign key
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   DATETIME    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.Employees
ADD CONSTRAINT FK_Employees_Employees
FOREIGN KEY(mgrid)
REFERENCES Employees(empid);

-- Check
ALTER TABLE dbo.Employees
ADD CONSTRAINT CHK_Employees_salary
CHECK(salary > 0);

-- Default
ALTER TABLE dbo.Orders
ADD CONSTRAINT DFT_Orders_orderts
DEFAULT(CURRENT_TIMESTAMP) FOR orderts;