#1
  1. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18

    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.
    For example:

    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.

    -b
    PostgreSQL, it's what's for dinner...
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    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.
    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...
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo