Rails model with foreign key to itself

Question

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?

Answer 1

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.

Answer 2

I would have thought you'd remove the NOT NULL constraint (i.e. allow the first user to have NULL for creator and editor).

You can then implement constraints to make sure that all subsequent entries are not null, e.g.:

CONSTRAINT creator_required CHECK (creator IS NOT NULL OR userid = 1)
CONSTRAINT editor_required CHECK (editor IS NOT NULL OR userid = 1)