练习SQL语句的键和约束

最新推荐文章于 2022-07-01 17:20:27 发布

oathevil

最新推荐文章于 2022-07-01 17:20:27 发布

阅读量1.3k

点赞数

分类专栏： SQL 文章标签： sql null table action sql server insert

SQL 专栏收录该内容

28 篇文章 0 订阅

订阅专栏

1 创建表的时候一起创建主键,没有为主键命名

CREATE TABLE Customers
(
CustomerNo   int IDENTITY NOT NULL
   PRIMARY KEY,
CustomerName varchar(30)   NOT NULL,
Address1   varchar(30)   NOT NULL,
City    varchar(20)   NOT NULL,
[State]    char(20)   NOT NULL,
Zip     varchar(10)   NOT NULL,
Contact    varchar(25)   NOT NULL,
Phone    char(15)   NOT NULL,
)

2 先创建好表,然后修改增加主键,为主键命名

CREATE TABLE Employees
(
EmployeeID   int IDENTITY NOT NULL,
FirstName   varchar(25)   NOT NULL,
MiddleInitial char(1)    NULL,
LastName   varchar(25)   NOT NULL,
Title    varchar(25)   NOT NULL,
SSN     varchar(11)   NOT NULL,
Salary    money    NOT NULL,
PriorSalary   money    NOT NULL,
LastRaise AS Salary - PriorSalary,
HireDate   smalldatetime NOT NULL,
TerminationDate smalldatetime NULL,
ManagerEmpID int     NOT NULL,
Department   varchar(25)   NOT NULL,
)

ALTER TABLE Employees
ADD CONSTRAINT PK_EmployeeID
PRIMARY KEY (EmployeeID)

3 查看表中键的存贮过程

EXEC sp_helpconstraint 表

4 创建表的时候同时创建外键,没有为外键命名

CREATE TABLE Orders
(
OrderID   int IDENTITY NOT NULL
   PRIMARY KEY,
CustomerNo int     NOT NULL
   FOREIGN KEY REFERENCES Customers(CustomerNo),
OrderDate smalldatetime NOT NULL,
EmployeeID int     NOT NULL
)

5 为已有的表增加外键,为外键命名

ALTER TABLE Orders
ADD CONSTRAINT FK_EmployeeCreatesOrder
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)

6 使用表的自引用添加外键

先向表中添加一行

INSERT INTO Employees
(
FirstName,
LastName,
Title,
SSN,
Salary,
PriorSalary,
HireDate,
ManagerEmpID,
Department
)
VALUES
(
'Billy Bob',
'Boson',
'Head Cook & Bottle Washer',
'123-45-6789',
100000,
80000,
'1990-01-01',
1,
'Cooking and Bottling'
)

然后添加外键

ALTER TABLE Employees
ADD CONSTRAINT FK_EmployeeHasManager
FOREIGN KEY (ManagerEmpID) REFERENCES Employees(EmployeeID)

当自引用约束引用的是基于标识列的必须的(非空)字段时,在实际创建自引用约束之前,很重要的一点事是,在添加外键前至少要向表中加入一个行.

实际上是在检查和执行了外键约束之后,才会选定并填入标识值,也就是说,当检查发生时,第一行尚无值可以引用.

另一个方案(也是唯一的)是,创建外键,但是在插入第一行时禁用它.

7 级联操作,这里没有把创建主键的语句直接放置在主键列之后,而将其声明为一个单独的约束项.

CREATE TABLE OrderDetails
(
OrderID    int     NOT NULL,
PartNo    varchar(10)   NOT NULL,
[Description] varchar(25)   NOT NULL,
UnitPrice   money    NOT NULL,
Qty     int     NOT NULL,
CONSTRAINT PKOrderDetails
   PRIMARY KEY (OrderID, PartNo),
CONSTRAINT FKOrderContainsDetails
   FOREIGN KEY (OrderID)
    REFERENCES Orders(OrderID)
   ON UPDATE NO ACTION
    ON DELETE CASCADE
)

ON UPDATE ,ON DELETE后面只有两个可选参数NO ACTION,CASCADE,

NO ACTION是默认选项,SQL SERVER 2000才开始支持这个关键字,如果无需向后兼容,建议明确包含NO ACTION,

CASCADE是级联

通常不要使用级联,级联虽然省事,但对数据库来说,省事不一定是好事.

8 UNIQUE

备用键,同主键一样要求表中指定的列(或者列的组合)具有唯一值.

不会把UNIQUE约束看作表中唯一标识符(即便事实上可以这样使用它们),因而能够有不止一个UNIQUE约束.

UNIQUE约束与主键不同,它不会自动阻止NULL值.是否允许NULL值取决于表中该列上NULL选项的设置,即使允许列中有NULL值,也仅仅能插入一个NULL值.虽然一个NULL值不等于另一个NULL值,但是,从UNIQUE约束的观点来看,它们依然是重复的,这很奇怪.

新建UNIQUE,在原有的表上添加UNIQUE,和查看UNIQUE

CREATE TABLE Shippers
(
ShipperID   int   IDENTITY NOT NULL
   PRIMARY KEY,
ShipperName   varchar(30)    NOT NULL,
[Address]   varchar(30)    NOT NULL,
City    varchar(30)    NOT NULL,
[State]    char(2)     NOT NULL,
Zip     varchar(10)    NOT NULL,
PhoneNo    varchar(14)    NOT NULL
   UNIQUE
)

ALTER TABLE Employees
ADD CONSTRAINT AK_EmployeeSSN
UNIQUE (SSN)

EXEC sp_helpconstraint Employees

AK备用键,也有用UQ或者U的,PK主键,FK外键

9 向现有表中添加列

ALTER TABLE Customers
ADD DateInSystem datetime

10 CHECK约束

ALTER TABLE Customers
ADD CONSTRAINT CN_CustomerDateInSystem
CHECK
(DateInSystem <= GETDATE())

EXEC sp_helpconstraint Customers

11 在CREATE TABLE语句中定义DEFAULTE约束

如果插入的新行在定义了默认值约束的列上没有给出值,则将按照约束的定义来处理

CREATE TABLE Shippers
(
ShipperID   int   IDENTITY NOT NULL
   PRIMARY KEY,
ShipperName   varchar(30)    NOT NULL,
DateInSystem smalldatetime   NOT NULL
   DEFAULT GETDATE()
)

INSERT INTO Shippers(ShipperName)
VALUES('United Parcel Service')

SELECT * FROM Shippers

12 在现有的表上添加DEFAULT约束

ALTER TABLE Customers
ADD CONSTRAINT CN_CustomerDefaultDateInSystem
DEFAULT GETDATE() FOR DateInSystem

ALTER TABLE Customers
ADD CONSTRAINT CN_CustomerAddress
DEFAULT 'UNKNOWN' FOR Address1

可以把默认值定义为常量值,例如字符串UNKNOWN

EXEC sp_helpconstraint Customers

13 创建约束时忽略不正确的数据

要添加约束,但是不把约束应用到现有的数据上,可以在执行ALTER TABLE语句添加约束时使用WITH NOCHECK选项来实现.

为了向后引用,旧的数据得以保留,但是,任何新加入的数据必须满足新的标准.

ALTER TABLE Customers
WITH NOCHECK
ADD CONSTRAINT CN_CustomerPhoneNo
CHECK
(Phone LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')

14 临时禁用现有约束