April 29th, 2003, 11:39 AM
Foreign Keys to same table
I was experimenting with some table structures and noticed that I could have a foreign key to another field within the same table.
CREATE TABLE "userstest" (
"emp_id" varchar(15) NOT NULL,
"last_editor" varchar(15) NOT NULL REFERENCES "userstest" ("emp_id") ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY ("emp_id")
Now I'm not actually using this for anything but I was just wondering if this is allowed, how you'd actually insert an entry into this table when it's empty.
PostgreSQL, it's what's for dinner...
April 29th, 2003, 01:46 PM
I'm pretty sure that if you enclose multiple inserts inside a transaction, you can do this. You just have to make sure that every entry either meets an existing foreign key, or you add another entry to satisfy the requirement. Of course, you can get into a recursion nightmare if you're not careful. Your first two rows would have to refer to each other. Hey, that actually might have its uses...