具有外键的Rails模型 [英] Rails model with foreign key to itself
问题描述
模式转储如下所示：
create_tableusers ，：force => true do | t |
t.integercreator_id，：precision => 38，scale => 0，：null => false
t.integereditor_id，：precision => 38，scale => 0，：null => false
end
add_foreign_keyusers，users，：column => creator_id，：name => r_user_creatorid，：dependent => ：nullify
add_foreign_keyusers，users，：column => editor_id，：name => r_user_editorid，：dependent => ：nullify
我的用户模型如下所示：
class User< ActiveRecord :: Base
belongs_to：creator，：class_name => User
belongs_to：editor，：class_name => User
validates_presence_of：creator，：editor
end
当我尝试保存第一个用户时出现问题。还没有其他用户，但是我不能有一个空的editor_id或者creator_id。如果我尝试将编辑器和创建者设置为自己，我会收到一个堆栈溢出。
理论上说，所有用户（除了第一个）都有一个创建者和编辑。有没有办法完成这个而不暂时删除非空约束？
所以问题是，必须有层次结构顶部的用户，没有管理员的用户（您的示例中的编辑器）。这就是为什么这种结构的经典解决方案是允许空值。您在结束段落中确认：
理论上说，所有
用户（第一个除外）都有一个
的创建者和编辑器。有没有办法
完成这个没有临时
删除非空约束？
踢球者是，如果第一个用户没有CREATOR或EDITOR，那么没有临时：您必须淹没强制约束。如果这样做，递归外键约束的问题就会消失。
另一种方法是介绍亚里士多德所谓的主移动器，即创建者本身的用户。给定此表：
创建表t72
（用户标识号不为null
，创建者号不为null
，编辑器号不为空
，约束t72_pk主键（userid）
，约束t72_cr_fk外键（创建者）
引用t72（userid）
，约束t72_ed_fk外键（编辑）
引用t72（userid）
）
/
创建这样的用户很简单：
SQL>插入t72值（1,1,1）
2 /
1行创建。
SQL>承诺;
提交完成。
SQL>
那么为什么这不是规范的解决方案。那么它导致一个稍微古怪的数据模型，一旦我们添加了更多的用户，它可能会导致分层查询的破坏。
SQL>选择lpad（''，level-1）|| u.userid as userid
2，u.name
3，u.editor
4从t72 u
5连接
6 before userid = editor
7从userid = 1
8 /
错误：
ORA-01436：用户数据中的CONNECT BY循环
否行
SQL>
基本上，数据库不喜欢USERID是自己的编辑器。但是，有一个解决方法，它是 NOCYCLE 关键字（用10g引入）。这告诉数据库忽略层次结构中的循环引用：
SQL>选择lpad（''，level-1）|| u.userid as userid
2，u.name
3，u.editor
4从t72 u
5 connect by nocycle
6 previous userid = editor
7从userid = 1开始
8 /
用户名编辑
---------- ---------- - ---------
1 ONE 1
2 TWO 1
3 THREE 2
4 FOUR 2
5 FIVE 2
6 SIX 2
7 SEVEN 6
选择7行。
SQL>
这里没有关系，因为数据仍然是正确的层次结构。但是如果我们这样做会发生什么：
SQL>更新t72 set editor = 7
2其中userid = 1
3 /
1行更新。
SQL>
我们失去了一种关系（1 - > 7）。我们可以使用CONNECT_BY_ISNOCYCLE伪列来查看正在循环的行。
SQL>选择lpad（''，level-1）|| u.userid as userid
2，u.name
3，u.editor
4，connect_by_iscycle
5从t72 u
6 connect by nocycle
用户id =编辑器
8以userid = 1开始
9 /
用户名NAME编辑器CONNECT_BY_ISCYCLE
---------- --- ------- ---------- ------------------
1 ONE 7 0
2两个1 0
3 THREE 2 0
4 FOUR 2 0
5 FIVE 2 0
6 SIX 2 0
7 SEVEN 6 1
7已选择行。
SQL>
Oracle具有大量附加功能，可以更轻松地在纯SQL中使用分层数据。这些都在文档中。 了解更多
I have an Oracle DB schema which includes a "users" table. This table has two non-null foreign keys to an editor and creator which are also users.
The schema dump looks like this:
create_table "users", :force => true do |t|
t.integer "creator_id", :precision => 38, :scale => 0, :null => false
t.integer "editor_id", :precision => 38, :scale => 0, :null => false
end
add_foreign_key "users", "users", :column => "creator_id", :name => "r_user_creatorid", :dependent => :nullify
add_foreign_key "users", "users", :column => "editor_id", :name => "r_user_editorid", :dependent => :nullify
My User model looks like the following:
class User < ActiveRecord::Base
belongs_to :creator, :class_name => "User"
belongs_to :editor, :class_name => "User"
validates_presence_of :creator, :editor
end
The problem comes when I try to save the first User. No other users exist yet, but I cannot have a null editor_id or creator_id. If I try to set the editor and creator to itself, I get a stack overflow.
In theory, it makes sense that all users (except the first) have a creator and editor. Is there any way to accomplish this without temporarily removing the non-null constraint?
So the problem is, there has to be a user at the top of the hierarchy, a user for whom there is no manager (editor in your example). That is why the classic solution to this sort of structure is to allow null values. You acknowledge this in your closing paragraph:
"In theory, it makes sense that all users (except the first) have a creator and editor. Is there any way to accomplish this without temporarily removing the non-null constraint?"
The kicker is, if the first user doesn't have a CREATOR or an EDITOR then there is no "temporary": you have to ditch the mandatory constraint. If you do this, the problem with the recursive foreign key constraint will disappear.
The alternative is to introduce what Aristotle called a Prime Mover, a User whose Creator is itself. Given this table:
create table t72
( userid number not null
, creator number not null
, editor number not null
, constraint t72_pk primary key (userid)
, constraint t72_cr_fk foreign key (creator)
references t72 (userid)
, constraint t72_ed_fk foreign key (editor)
references t72 (userid)
)
/
it's pretty simple to create such a user:
SQL> insert into t72 values (1,1,1)
2 /
1 row created.
SQL> commit;
Commit complete.
SQL>
So why isn't this the canonical solution. Well it leads to a slightly wacky data model which can create havoc with hierarchical queries once we add a few more users.
SQL> select lpad(' ', level-1)|| u.userid as userid
2 , u.name
3 , u.editor
4 from t72 u
5 connect by
6 prior userid = editor
7 start with userid=1
8 /
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL>
Basically the database doesn't like USERID being its own editor. However, there is a workaround, which is the NOCYCLE keyword (introduced with 10g). This tells the database to ignore circular references in the hierarchy:
SQL> select lpad(' ', level-1)|| u.userid as userid
2 , u.name
3 , u.editor
4 from t72 u
5 connect by nocycle
6 prior userid = editor
7 start with userid=1
8 /
USERID NAME EDITOR
---------- ---------- ----------
1 ONE 1
2 TWO 1
3 THREE 2
4 FOUR 2
5 FIVE 2
6 SIX 2
7 SEVEN 6
7 rows selected.
SQL>
Here it doesn't matter because the data is still correctly hierarchical. But what happens if we do this:
SQL> update t72 set editor = 7
2 where userid = 1
3 /
1 row updated.
SQL>
We lose a relationship ( 1 -> 7). We can use the CONNECT_BY_ISNOCYCLE pseudo-column to see which row is cycling.
SQL> select lpad(' ', level-1)|| u.userid as userid
2 , u.name
3 , u.editor
4 , connect_by_iscycle
5 from t72 u
6 connect by nocycle
7 prior userid = editor
8 start with userid=1
9 /
USERID NAME EDITOR CONNECT_BY_ISCYCLE
---------- ---------- ---------- ------------------
1 ONE 7 0
2 TWO 1 0
3 THREE 2 0
4 FOUR 2 0
5 FIVE 2 0
6 SIX 2 0
7 SEVEN 6 1
7 rows selected.
SQL>
Oracle has lots of additional functionality to make it easier to work with hierarchical data in pure SQL. It is all in the documentation. Find out more.
这篇关于具有外键的Rails模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
