SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    Amersfoort, the Netherlands
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Foreign key on same table?

    I've Googled around, but I whatever I try, I keep getting "error 150" messages.

    Code:
    CREATE TABLE pages (
    	body TEXT NOT NULL,
    	id INT(11) NOT NULL AUTO_INCREMENT,
    	parent_id INT(11) NOT NULL,
    	title VARCHAR(255) NOT NULL,
    	PRIMARY KEY (id),
    	FOREIGN KEY (parent_id) REFERENCES pages(id)
    	    ON DELETE RESTRICT,
    ) ENGINE = INNODB;
    What am I doing wrong? Any help would be much appreciated...

  2. #2
    SitePoint Zealot basicwebus's Avatar
    Join Date
    Jul 2003
    Location
    Missouri
    Posts
    127
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try adding:

    INDEX id,

    add that line between your PRIMARY KEY line and the FOREIGN KEY line.
    INNODB won't add a foreign key without it being indexed first.

    BasicWebUS
    After the third time of reading the instructions,
    it finally soaks in to my thick skull.



  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2005
    Location
    Amersfoort, the Netherlands
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, but still no luck. Here's the full query. The users table is created before the pages table, so that shouldn't cause any problems. What does, though?

    Code:
    CREATE TABLE pages (
    	body TEXT NOT NULL,
    	id INT(11) NOT NULL AUTO_INCREMENT,
    	is_active TINYINT(1) NOT NULL DEFAULT 1,
    	parent_id INT(11) DEFAULT NULL,
    	title VARCHAR(255) NOT NULL,
    	updated_at DATETIME NOT NULL,
    	url_title VARCHAR(255) NOT NULL,
    	user_id INT(11) NOT NULL,
    	PRIMARY KEY (id),
    	INDEX (id),
    	FOREIGN KEY (parent_id) REFERENCES pages(id)
    	    ON DELETE SET NULL,
    	FOREIGN KEY (user_id) REFERENCES users(id)
    	    ON DELETE SET NULL
    ) ENGINE = INNODB;

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd say you can't create it because you are trying to add the foreign key before the table exists.
    you could probably add the key afterwards


    a more important question is why do you think you need a foreign key to reference a table that information is already in? by definition of course it then isn't a foreign key.

    if you are trying to relate an id and a parent id then the parent id does not belong in this table. for normalization, you want a separate table with id and parentid only in that table. then the pages table does not store the parent id information.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try placing the PK first in the table (you could be getting that wacky "subsidiary auto_increment" thingie)

    you do not need INDEX (id) since you have PRIMARY KEY (id)

    daveman, your comments don't sound right, there is nothing wrong with the id/parent_id structure in this table, and of course the user_id FK to another table is fine too

    splitting the two keys off into a separate table by themselves is done only when it's a many-to-many relationship, and in this case, i don't think it is

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nope, i was wrong, TEXT column in front of PK auto_increment is fine

    i just tested your syntax in 4.1, it's okay
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    your first create table statement has a dangling comma.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •