Foreign key from table A on table A

I have a table with users

users
+------+
 - user_id
 ...
 ...
 - updated_by

as I understand column updated_by should 开发者_JAVA技巧reference this table on column user_id. I'm not sure, that this would be correct, but I don't see any other way. My database uses 3NF.

Thank you all for help and suggestions.

---

Yes, it's entirely appropriate and correct to have a table have a foreign key relationship back to itself, if that's what's appropriate for your usage.

---

There's nothing wrong with this, it's just a self-referential "foreign" key. The problem is priming the database - there has to be either a root record in the table before you apply the foreign key, or there has to be some way of disabling the foreign key checks when inserting new root records into an otherwise empty table.

The other danger is a mis-applied cascading update or delete. If you start it at the wrong end of the chain, you could wipe out/update large chunks of the table by accident. e.g... you delete the root node, and the delete cascades itself down to all child records.

---

Yes, this is very common. It's how (usually?) parent/child relationships are implemented.

Another option (if you want to avoid having one or more "root" records with NULL in the updated_by fields), is to move updated_by to a second table where both user_id and updated_by reference users.user_id :

users
--------
 - user_id (PK)
 ...
 ...


updates
--------
 - user_id (PK)
 - updated_by
 - FK user_id REFERENCES users(user_id)
 - FK updated_by REFERENCES users(user_id)