-
Notifications
You must be signed in to change notification settings - Fork 425
/
Copy pathkey.html
453 lines (417 loc) · 25.3 KB
/
key.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>5.6. 主键/外键</title><link rel="stylesheet" type="text/css" href="/docbook.css" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="home" href="index.html" title="Netkiller PostgreSQL 手札" /><link rel="up" href="ddl.html" title="第 5 章 数据定义（DDL）" /><link rel="prev" href="constraints.html" title="5.5. 约束" /><link rel="next" href="dml.html" title="第 6 章 DML" /></head><body><a xmlns="" href="//www.netkiller.cn/">Home</a> |
<a xmlns="" href="//netkiller.github.io/">简体中文</a> |
<a xmlns="" href="http://netkiller.sourceforge.net/">繁体中文</a> |
<a xmlns="" href="/journal/index.html">杂文</a> |
<a xmlns="" href="//www.netkiller.cn/home/donations.html">打赏(Donations)</a> |
<a xmlns="" href="https://github.com/netkiller">Github</a> |
<a xmlns="" href="http://my.oschina.net/neochen/">OSChina 博客</a> |
<a xmlns="" href="https://cloud.tencent.com/developer/column/2078">云社区</a> |
<a xmlns="" href="https://yq.aliyun.com/u/netkiller/">云栖社区</a> |
<a xmlns="" href="https://www.facebook.com/bg7nyt">Facebook</a> |
<a xmlns="" href="http://cn.linkedin.com/in/netkiller/">Linkedin</a> |
<a xmlns="" href="https://zhuanlan.zhihu.com/netkiller">知乎专栏</a> |
<a xmlns="" href="//www.netkiller.cn/home/video.html">视频教程</a> |
<a xmlns="" href="//www.netkiller.cn/home/about.html">About</a><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">5.6. 主键/外键</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="constraints.html">上一页</a> </td><th width="60%" align="center">第 5 章 数据定义（DDL）</th><td width="20%" align="right"> <a accesskey="n" href="dml.html">下一页</a></td></tr></table><hr /></div><table xmlns=""><tr><td><iframe src="//ghbtns.com/github-btn.html?user=netkiller&repo=netkiller.github.io&type=watch&count=true&size=large" height="30" width="170" frameborder="0" scrolling="0" style="width:170px; height: 30px;" allowTransparency="true"></iframe></td><td><iframe src="//ghbtns.com/github-btn.html?user=netkiller&repo=netkiller.github.io&type=fork&count=true&size=large" height="30" width="170" frameborder="0" scrolling="0" style="width:170px; height: 30px;" allowTransparency="true"></iframe></td><td><iframe src="//ghbtns.com/github-btn.html?user=netkiller&type=follow&count=true&size=large" height="30" width="240" frameborder="0" scrolling="0" style="width:240px; height: 30px;" allowTransparency="true"></iframe></td></tr></table><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="key"></a>5.6. 主键/外键</h2></div></div></div>
<div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="idp41"></a>5.6.1. 主键</h3></div></div></div>
<pre class="screen">
下面书写方式，推荐第二种，比较清晰。
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
</pre>
</div>
<div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="idp42"></a>5.6.2. 外键约束</h3></div></div></div>
<pre class="screen">
下面第前两种写法不推荐。第三、四种写法较清晰。
1. 第一种书写方式
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products,
quantity integer
);
2. 第二种书写方式
CREATE TABLE orders (
order_id integer PRIMARY KEY,
product_no integer REFERENCES products (product_no),
quantity integer
);
3. 第三种书写方式
CREATE TABLE table1 (
a integer PRIMARY KEY,
b integer,
c integer,
FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);
4. 第四种书写方式，在SQL脚本最后面添加外键约束
Alter table "groupmember" add foreign key ("uid") references "user" ("id") on update restrict on delete restrict;
Alter table "groupmember" add foreign key ("gid") references "group" ("id") on update restrict on delete restrict;
Alter table "rolemember" add foreign key ("uid") references "user" ("id") on update restrict on delete restrict;
Alter table "rolemember" add foreign key ("rid") references "role" ("id") on update restrict on delete restrict;
3.9.3 PostgreSQL 7.3.x 新增功能
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
order_id integer REFERENCES orders ON DELETE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
类似 ON DELETE，还有 ON UPDATE 选项，它是在主键被修改（更新）的时候调用的。
以前我们删除其它表中受外键约束的记录，使用规则或触发器来完成。现可以用CASCADE
</pre>
</div>
<div class="section"><div class="titlepage"><div><div><h3 class="title"><a id="idp43"></a>5.6.3. 案例</h3></div></div></div>
<p>层次递归-分类目录</p>
<pre class="screen">
实现一个无限向下分类的目录，例如：
计算机与互联网
免费资源
软件下载(3431)
壁纸/屏保/桌面(109)
免费电子贺卡(197)
代理服务器(33)
免费电子邮箱(73)
免费主页空间(75)
免费聊天室(11)
免费论坛(36)
软件
XXXXXXXX
XXXXXXXX
XXXXXXXX
XXXXXXXX
硬件
互联网
编程
</pre>
<p>数据结构定义</p>
<pre class="screen">
Drop table "directory" CASCADE;
Create table "directory"
(
"id" Serial NOT NULL,
"root_id" Integer NOT NULL Default 0,
"name" Varchar(20)NOT NULL ,
"status" boolean Default 'true',
"created" Timestamp Default current_timestamp,
"modified" Timestamp Default current_timestamp,
UNIQUE (id,root_id),
PRIMARY KEY ("id")
-- FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE
);
INSERT INTO directory (id,root_id,name) VALUES (0,0,'/');
Alter table "directory" add FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE;
Create index "directory_index" on "directory" using btree ("id","root_id","name");
</pre>
<p>演示</p>
<pre class="screen">
数据存储状态：
Id
Root_id
Name
0
0
/
1
0
计算机
2
1
显示器
3
1
鼠标
4
1
主板
5
2
Samsung 显示器
6
2
LG显示器
7
2
SONY显示器
上图是一个分类目录，当删除子目录时如果子目录中有目录或数据，将删除这些数据和目录
说明：
id 目录根
root_id REFERENCES id ON DELETE CASCADE当pk删除时关联的fk自动删除
name 目录名
status 状态true可用，false不可用
created 创建时间
modified 修改时间
注意：
因为使用了关联字段，所以不能在create table 中使用
FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE
因为插入记录做参考表中的“id”字段，创建表的中没有数据，所以无法插入数据。
先创建表，不定义FOREIGN KEY，然后初始化插入第一条数据：
INSERT INTO directory (id,root_id,name) VALUES (0,0,'/');
再定义外建：
Alter table "directory" add FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE;
postgres=# Create table "directory"
postgres-# (
postgres(# "id" Serial NOT NULL,
postgres(# "root_id" Integer NOT NULL Default 0,
postgres(# "name" Varchar(20)NOT NULL ,
postgres(# "status"boolean Default 'true',
postgres(# "created" Timestamp Default current_timestamp,
postgres(# "modified" Timestamp Default current_timestamp,
postgres(# UNIQUE (id,root_id),
postgres(# PRIMARY KEY ("id")
postgres(# -- FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE
postgres(# );
NOTICE: CREATE TABLE will create implicit sequence 'directory_id_seq' for SERIAL column 'directory.id'
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'directory_pkey' for table 'directory'
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'directory_id_key' for table 'directory'
CREATE TABLE
postgres=# INSERT INTO directory (id,root_id,name) VALUES (0,0,'/');
INSERT 17110 1
postgres=# Alter table "directory" add FOREIGN KEY (root_id) REFERENCES directory (id) ON DELETE CASCADE;
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
ALTER TABLE
postgres=# Create index "directory_index" on "directory" using btree ("id","root_id","name");
CREATE INDEX
postgres=# INSERT INTO directory (root_id,name) VALUES (0,'计算机');
INSERT 17116 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+-----------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
(2 rows)
postgres=# INSERT INTO directory (root_id,name) VALUES (0,'金融');
INSERT 17117 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+-----------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
(3 rows)
postgres=# INSERT INTO directory (root_id,name) VALUES (1,'显示器');
INSERT 17118 1
postgres=# INSERT INTO directory (root_id,name) VALUES (1,'鼠标');
INSERT 17119 1
postgres=# INSERT INTO directory (root_id,name) VALUES (1,'主板');
INSERT 17120 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+-----------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
3 | 1 | 显示器 | t | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
4 | 1 | 鼠标 | t | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
5 | 1 | 主板 | t | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
(6 rows)
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'Samsung 显示器');
INSERT 17121 1
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'LG显示器');
INSERT 17122 1
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'SONY显示器');
INSERT 17123 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+-------------------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
3 | 1 | 显示器 | t | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
4 | 1 | 鼠标 | t | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
5 | 1 | 主板 | t | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
6 | 3 | Samsung 显示器 | t | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
7 | 3 | LG显示器 | t | 2003-11-12 17:01:03.736121 | 2003-11-12 17:01:03.736121
8| 3 | SONY显示器 | t | 2003-11-12 17:01:18.257337 | 2003-11-12 17:01:18.257337
(9 rows)
postgres=# INSERT INTO directory (root_id,name) VALUES (7,'CRT显示器');
INSERT 17124 1
postgres=# INSERT INTO directory (root_id,name) VALUES (7,'液晶显示器');
INSERT 17125 1
postgres=# INSERT INTO directory (root_id,name) VALUES (8,'液晶显示器');
INSERT 17126 1
postgres=# INSERT INTO directory (root_id,name) VALUES (8,'特利隆显示器');
INSERT 17127 1
postgres=# INSERT INTO directory (root_id,name) VALUES (7,'钻石隆显示器');
INSERT 17128 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+--------------------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
3 | 1 | 显示器 | t | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
4 | 1 | 鼠标 | t | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
5 | 1 | 主板 | t | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
6 | 3 | Samsung 显示器 | t | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
7 | 3 | LG显示器 | t | 2003-11-12 17:01:03.736121 | 2003-11-12 17:01:03.736121
8 | 3 | SONY显示器 | t | 2003-11-12 17:01:18.257337 | 2003-11-12 17:01:18.257337
9 | 7 | CRT显示器 | t | 2003-11-12 17:03:05.594891 | 2003-11-12 17:03:05.594891
10 | 7 | 液晶显示器 | t | 2003-11-12 17:03:21.793674 | 2003-11-12 17:03:21.793674
11 | 8 | 液晶显示器 | t | 2003-11-12 17:03:30.688531 | 2003-11-12 17:03:30.688531
12 | 8 | 特利隆显示器 | t | 2003-11-12 17:03:57.697321 | 2003-11-12 17:03:57.697321
13 | 7 | 钻石隆显示器 | t | 2003-11-12 17:04:28.61153 | 2003-11-12 17:04:28.61153
(14 rows)
测试：
1. 删除子目录：计算机/显示器/ LG显示器/ CRT显示器
CRT显示器的id是9
SQL：DELETE FROM directory WHERE id=9;
postgres=# DELETE FROM directory WHERE id=9;
DELETE 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+--------------------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
3 | 1 | 显示器 | t | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
4 | 1 | 鼠标 | t | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
5 | 1 | 主板 | t | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
6 | 3 | Samsung 显示器 | t | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
7 | 3 | LG显示器 | t | 2003-11-12 17:01:03.736121 | 2003-11-12 17:01:03.736121
8 | 3 | SONY显示器 | t | 2003-11-12 17:01:18.257337 | 2003-11-12 17:01:18.257337
10 | 7 | 液晶显示器 | t | 2003-11-12 17:03:21.793674 | 2003-11-12 17:03:21.793674
11 | 8 | 液晶显示器 | t | 2003-11-12 17:03:30.688531 | 2003-11-12 17:03:30.688531
12 | 8 | 特利隆显示器 | t | 2003-11-12 17:03:57.697321 | 2003-11-12 17:03:57.697321
13 | 7 | 钻石隆显示器 | t | 2003-11-12 17:04:28.61153 | 2003-11-12 17:04:28.61153
(13 rows)
postgres=#
2. 删除子目录：计算机/显示器/ LG显示器
LG显示器目录下的子目录：液晶显示器、钻石隆显示器也将被删除
postgres=# DELETE FROM directory WHERE id=7;
DELETE 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+--------------------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
3 | 1 | 显示器 | t | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
4 | 1 | 鼠标 | t | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
5 | 1 | 主板 | t | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
6 | 3 | Samsung 显示器 | t | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
8 | 3 | SONY显示器 | t | 2003-11-12 17:01:18.257337 | 2003-11-12 17:01:18.257337
11 | 8 | 液晶显示器 | t | 2003-11-12 17:03:30.688531 | 2003-11-12 17:03:30.688531
12 | 8 | 特利隆显示器 | t | 2003-11-12 17:03:57.697321 | 2003-11-12 17:03:57.697321
(10 rows)
3. 再删除：计算机/显示器/ SONY显示器
postgres=# DELETE FROM directory WHERE id=8;
DELETE 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+-------------------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
3 | 1 | 显示器 | t | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
4 | 1 | 鼠标 | t | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
5 | 1 | 主板 | t | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
6 | 3 | Samsung 显示器 | t | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
(7 rows)
4. 删除子目录：计算机/显示器
显示器目录下的子目录：
下有目录LG显示器/ CRT显示器、SONY显示器/……、LG显示器/……
删除显示器目录后，下的所有子目录将被删除。
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'LG显示器');
INSERT 17129 1
postgres=# INSERT INTO directory (root_id,name) VALUES (3,'SONY显示器');
INSERT 17130 1
postgres=# INSERT INTO directory (root_id,name) VALUES (6,'CRT显示器');
INSERT 17131 1
postgres=# INSERT INTO directory (root_id,name) VALUES (14,'CRT显示器');
INSERT 17132 1
postgres=# INSERT INTO directory (root_id,name) VALUES (15,'CRT显示器');
INSERT 17133 1
postgres=# INSERT INTO directory (root_id,name) VALUES (15,'特利隆显示器');
INSERT 17134 1
postgres=# INSERT INTO directory (root_id,name) VALUES (15,'钻石隆显示器');
INSERT 17135 1
postgres=# INSERT INTO directory (root_id,name) VALUES (6,'液晶显示器');
INSERT 17136 1
postgres=# INSERT INTO directory (root_id,name) VALUES (14,'液晶显示器');
INSERT 17137 1
postgres=# INSERT INTO directory (root_id,name) VALUES (15,'液晶显示器');
INSERT 17138 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+--------------------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
3 | 1 | 显示器 | t | 2003-11-12 16:59:15.911196 | 2003-11-12 16:59:15.911196
4 | 1 | 鼠标 | t | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
5 | 1 | 主板 | t | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
6 | 3 | Samsung 显示器 | t | 2003-11-12 17:00:45.964053 | 2003-11-12 17:00:45.964053
14 | 3 | LG显示器 | t | 2003-11-12 17:28:03.927651 | 2003-11-12 17:28:03.927651
15 | 3 | SONY显示器 | t | 2003-11-12 17:28:15.235316 | 2003-11-12 17:28:15.235316
16 | 6 | CRT显示器 | t | 2003-11-12 17:28:49.586084 | 2003-11-12 17:28:49.586084
17 | 14 | CRT显示器 | t | 2003-11-12 17:28:55.290861 | 2003-11-12 17:28:55.290861
18 | 15 | CRT显示器 | t | 2003-11-12 17:28:59.731191 | 2003-11-12 17:28:59.731191
19 | 15 | 特利隆显示器 | t | 2003-11-12 17:29:10.747115 | 2003-11-12 17:29:10.747115
20 | 15 | 钻石隆显示器 | t | 2003-11-12 17:29:30.770079 | 2003-11-12 17:29:30.770079
21 | 6 | 液晶显示器 | t | 2003-11-12 17:29:47.006177 | 2003-11-12 17:29:47.006177
22 | 14 | 液晶显示器 | t | 2003-11-12 17:29:51.904914 | 2003-11-12 17:29:51.904914
23 | 15 | 液晶显示器 | t | 2003-11-12 17:29:57.355213 | 2003-11-12 17:29:57.355213
(17 rows)
postgres=# DELETE FROM directory WHERE id=3;
DELETE 1
postgres=# SELECT * from directory ;
id | root_id | name | status | created | modified
----+---------+-----------+--------+----------------------------+----------------------------
0 | 0 | / | t | 2003-11-12 16:55:39.727365 | 2003-11-12 16:55:39.727365
1 | 0 | 计算机 | t | 2003-11-12 16:56:39.663584 | 2003-11-12 16:56:39.663584
2 | 0 | 金融 | t | 2003-11-12 16:57:50.509436 | 2003-11-12 16:57:50.509436
4 | 1 | 鼠标 | t | 2003-11-12 16:59:30.646916 | 2003-11-12 16:59:30.646916
5 | 1 | 主板 | t | 2003-11-12 16:59:44.400317 | 2003-11-12 16:59:44.400317
(5 rows)
不再举例了，删除id=0将删除计算机包括下面的所有目录被删除。
注意，千万不要删除id=0。
</pre>
<p>总结: 分类目录的例子中使用了ON DELETE CASCADE，方便了操作，但也有危险。如果不用ON DELETE CASCADE而用程序来实现，需要使用递归算法，非常麻烦。</p>
</div>
</div><div xmlns="" id="disqus_thread"></div><script xmlns="">
var disqus_config = function () {
this.page.url = "http://www.netkiller.cn"; // Replace PAGE_URL with your page's canonical URL variable
this.page.identifier = 'netkiller'; // Replace PAGE_IDENTIFIER with your page's unique identifier variable
};
(function() { // DON'T EDIT BELOW THIS LINE
var d = document, s = d.createElement('script');
s.src = '//netkiller.disqus.com/embed.js';
s.setAttribute('data-timestamp', +new Date());
(d.head || d.body).appendChild(s);
})();
</script><noscript xmlns="">Please enable JavaScript to view the <a href="https://disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript><br xmlns="" /><script xmlns="" type="text/javascript" id="clustrmaps" src="//cdn.clustrmaps.com/map_v2.js?u=r5HG&d=9mi5r_kkDC8uxG8HuY3p4-2qgeeVypAK9vMD-2P6BYM"></script><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="constraints.html">上一页</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html">上一级</a></td><td width="40%" align="right"> <a accesskey="n" href="dml.html">下一页</a></td></tr><tr><td width="40%" align="left" valign="top">5.5. 约束 </td><td width="20%" align="center"><a accesskey="h" href="index.html">起始页</a></td><td width="40%" align="right" valign="top"> 第 6 章 DML</td></tr></table></div><script xmlns="">
(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','//www.google-analytics.com/analytics.js','ga');
ga('create', 'UA-11694057-1', 'auto');
ga('send', 'pageview');
</script><script xmlns="" async="async">
var _hmt = _hmt || [];
(function() {
var hm = document.createElement("script");
hm.src = "https://hm.baidu.com/hm.js?93967759a51cda79e49bf4e34d0b0f2c";
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(hm, s);
})();
</script><script xmlns="" async="async">
(function(){
var bp = document.createElement('script');
var curProtocol = window.location.protocol.split(':')[0];
if (curProtocol === 'https') {
bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
}
else {
bp.src = 'http://push.zhanzhang.baidu.com/push.js';
}
var s = document.getElementsByTagName("script")[0];
s.parentNode.insertBefore(bp, s);
})();
</script><script xmlns="" type="text/javascript" src="/js/q.js" async="async"></script></body></html>