1. 主键约束(PRIMARY KEY)
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键,并且每个表只能有一个主键。
如果需要命名 PRIMARY KEY 约束,以及为多个列定义 PRIMARY KEY 约束,请使用下面的 SQL 语法:
CONSTRAINT pk_name PRIMARY KEY (item1,item2)
如果在表已存在的情况下为 “item1” 列创建 PRIMARY KEY 约束,请使用下面的 SQL:
ALTER TABLE Persons
ADD PRIMARY KEY (item1)
如果需要命名 PRIMARY KEY 约束,以及为多个列定义 PRIMARY KEY 约束,请使用下面的 SQL 语法:
ALTER TABLE Persons
ADD CONSTRAINT pk_name PRIMARY KEY (item1,item2)
注释:如果您使用 ALTER TABLE 语句添加主键,必须把主键列声明为不包含 NULL 值(在表首次创建时)。
撤销 PRIMARY KEY 约束
如需撤销 PRIMARY KEY 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT pk_name
2. 外键约束(Foreign Keys)
紧接着上篇关于数据库的博客,我们修改城市以及天气数据表为以下结构:
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
然后我们试着向天气表中插入一个不存在与城市数据表中的城市天气:
INSERT INTO weather VALUES (’Berkeley’, 45, 53, 0.0, ’1994-11-28’);
以上操作会得到下面提示:
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".
外键的行为表现能够很好地调整我们的应用,使得一些不相干信息被排除在外.
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
如果需要命名 FOREIGN KEY 约束,以及为多个列定义 FOREIGN KEY 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)
SQL FOREIGN KEY Constraint on ALTER TABLE
如果在 “Orders” 表已存在的情况下为 “Id_P” 列创建 FOREIGN KEY 约束,请使用下面的 SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
如果需要命名 FOREIGN KEY 约束,以及为多个列定义 FOREIGN KEY 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
撤销 FOREIGN KEY 约束
如需撤销 FOREIGN KEY 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
3. 事务(Transactions)
事务是所有数据库系统中一个基础概念,事务的基本点是指绑定多个步骤到一个操作中去,这个操作要么执行,要么不执行。中间状态对于其他的并发事务是不可视的,如果有阻止事务完整性的错误发生,那么事务中所有的会对数据库产生影响的步骤都不会被执行。
例如有以下场景:
假设银行数据库中包含每一个用户的结余/差额以及总的存款结余的分支,如果我们从Alice的账户中向Bob的账户中转了100美刀,不合理的操作如下:
UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Alice’;
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = ’Alice’);
UPDATE accounts SET balance = balance + 100.00
WHERE name = ’Bob’;
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = ’Bob’);
我们可以看出在这个简单的操作中需要执行好几次分离的更新操作,作为一个银行管理者希望的是这些操作要么都发生,要么都不发生,将所有的操作归到一个事务中保证了这一点。
事务具有原子性:从其他事务来看,一个事务要么完全成功,要么不要发生(all-or-nothing).
一个事务数据库保证了在一个事务被报告为完成之前所有的事务更新被永久地记录在了存储中。
事务数据库的另外一个重要的特性是原子更新:当多事务并发的时候,每一个事务都不能看到被其他为完整事务进行的改变或者操作。
在PostgreSQL数据库中,一个事务的建立是由多条在BEGIN和COMMIT之间的SQL语句,所以上边场景的最终事务处理如下:
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = ’Alice’;
-- etc etc
COMMIT;
如果在事务执行的过程中我们决定不做提交了(或者说我们发现Alice的账户中余额不足),我们可以执行ROLLBACK命令而不是COMMIT,所有此事务中的步骤都会取消。
PostgreSQL实际上将每一条SQL语句都当作时在一个事务中执行的.如果你不想执行BEGIN,那么每一个单独的命令都会被一个默认的BEGIN和(如果成功的话)COMMIT包围.一组被BEGIN和COMMIT包围的语句一般被称作是一个事务块.
我们也可以通过使用保存点来控制一个事务的语句以更细化(粒状)的方式运行,保存点允许你选择性的丢弃事务的一些部分,然后提交另外一部分。在使用SAVEPOINT定义了一个保存点后 ,我们呢就可以使用ROLLBACK TO回滚到保存点. 所有事务数据库中在保存点和回滚之间的定义都会被丢弃,但是保存点之前的操作被保存了下来。在回滚到保存点后,保存点之前的定义继续生效,所以你可以多次回滚。相反的,如果你确定不需要再回滚到某个保存点,这个保存点就会被释放,这样系统就能够释放一些资源.请记住不管是释放还是回滚都会自动的释放之后定义的所有保存点。
所有这一切都在事务块中进行,所以对于其他的数据库会话都是不可见的。当你提交一个事务的时候,提交动作就会作为一个单元被其他会话所见,但是回滚操作对其他会话或者事务是从来都不可见的。
例如我们从ALice的账户扣除$100.00然后向Bob的账户转入相同金额,但是我们后来发现钱转入了其他人的账户,那么我们就可以使用保存点来解决这个错误:
BEGIN;
UPDATE accounts SET balance
WHERE name = ’Alice’;
SAVEPOINT my_savepoint;
UPDATE accounts SET balance
WHERE name = ’Bob’;
-- oops ... forget that and
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance
WHERE name = ’Wally’;
COMMIT;
= balance - 100.00
= balance + 100.00
use Wally’s account
= balance + 100.00
ROLLBACK TO是唯一获得事务块控制的操作,可以丢弃数据库系统中发生的一些错误操作,然后继续进行正确地操作。