sql - Postgres : Delete rows that has foreign key while not deleting data of the foreign key table

Question

我有 postgres 数据库，我的应用程序是使用 django 构建的，我使用 south migration 来维护数据库模式。我有以下场景:

user_table与userclickstream_stream有外键关系，userclickstream_click与user_stream_table有外键关系。

我想删除userclickstream_stream 和userclickstream_click 中的所有记录。但我不想删除 user_table 中的任何记录。完成此任务的最佳方法是什么？

这是我的 user_stream_table 的样子:

                                    Table "public.userclickstream_stream"
   Column    |           Type           |                              Modifiers                              
-------------+--------------------------+---------------------------------------------------------------------
 id          | integer                  | not null default nextval('userclickstream_stream_id_seq'::regclass)
 session_key | character varying(40)    | not null
 ip_address  | character varying(40)    | not null
 referrer    | text                     | 
 create_date | timestamp with time zone | not null
 last_update | timestamp with time zone | not null
 user_id     | integer                  | 
Indexes:
    "userclickstream_stream_pkey" PRIMARY KEY, btree (id)
    "userclickstream_stream_session_key_key" UNIQUE CONSTRAINT, btree (session_key)
    "userclickstream_stream_user_id" btree (user_id)
Foreign-key constraints:
    "user_id_refs_id_773d100c" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "userclickstream_click" CONSTRAINT "stream_id_refs_id_4c08df60" FOREIGN KEY (stream_id) REFERENCES userclickstream_stream(id) DEFERRABLE INITIALLY DEFERRED

User_click_table 是这样的

                                    Table "public.userclickstream_click"
   Column    |           Type           |                             Modifiers                              
-------------+--------------------------+--------------------------------------------------------------------
 id          | integer                  | not null default nextval('userclickstream_click_id_seq'::regclass)
 stream_id   | integer                  | not null
 url         | text                     | not null
 path        | text                     | not null
 create_date | timestamp with time zone | not null
Indexes:
    "userclickstream_click_pkey" PRIMARY KEY, btree (id)
    "userclickstream_click_stream_id" btree (stream_id)
Foreign-key constraints:
    "stream_id_refs_id_4c08df60" FOREIGN KEY (stream_id) REFERENCES userclickstream_stream(id) DEFERRABLE INITIALLY DEFERRED

如果有一个好的 SQL 方法来处理这个而不是走南迁路线，那就太好了。如果不是，我正在考虑执行以下操作:

我想简单地删除 south 迁移历史表中的记录并使用 south 重新构建模式我不确定这是否是正确的方法。但要做到这一点，我需要先删除这两个表。由于外键关系，我可能无法删除表。

比方说我放弃它然后我可以执行以下操作，因为南迁移历史表没有这两个表的任何记录。

./manage.py schemamigration userclickstream --initial
./manage.py migrate userclickstream

Answer 1

看这里:

http://www.postgresql.org/docs/9.3/static/ddl-constraints.html

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well. There are two other options: SET NULL and SET DEFAULT. These cause the referencing column(s) in the referencing row(s) to be set to nulls or their default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key constraint, the operation will fail.

Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s).

Normally, a referencing row need not satisfy the foreign key constraint if any of its referencing columns are null. If MATCH FULL is added to the foreign key declaration, a referencing row escapes satisfying the constraint only if all its referencing columns are null (so a mix of null and non-null values is guaranteed to fail a MATCH FULL constraint). If you don't want referencing rows to be able to avoid satisfying the foreign key constraint, declare the referencing column(s) as NOT NULL.

A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

似乎您只需要设置一个最适合您的情况的适当的 ON DELETE 操作。