Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    6

    Circular reference problem

    Hi,
    I've designed a database but have a circular reference which basically stops me creating any new rows, because neither foreign key can be null. So you cant create a row in tableA without a reference to tableB anf vice versa.

    This is obviously wrong, but a) allowing nulls makes no sense in either table, and b) I can figure out how else to logically do the relationship.

    Here are the tables (simplified):

    Code:
    CREATE TABLE `site` (                                                                   
    	  `id` int(11) NOT NULL auto_increment,                                                 
    	  `errorPageId` int(11) NOT NULL,                                                                                                                                                                                           
    	  PRIMARY KEY  (`id`),                                                                  
    	  KEY `FK_site_1` (`errorPageId`),                                                                                                                                                      
    	  CONSTRAINT `FK_site_1` FOREIGN KEY (`errorPageId`) REFERENCES `site_page` (`id`)         
    	) ENGINE=InnoDB DEFAULT CHARSET=utf8
    Code:
    CREATE TABLE `site_page` (                                                                        
    	 `id` int(11) NOT NULL auto_increment,                                                           
    	 `siteId` int(11) NOT NULL,                                                                                                                               
    	 PRIMARY KEY  (`id`),                                                                            
    	 KEY `FK_site_page_1` (`siteId`),                                                                                                                     
    	 CONSTRAINT `FK_site_page_1` FOREIGN KEY (`siteId`) REFERENCES `site` (`id`) ON DELETE CASCADE  
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    Each page has to belong to a site. However each site also has to have one of its pages defined as the error page.

    I guess I could add an "isErrorPage" field to the page table, however that would (potentially) allow multiple pages to be error pages, and it should only be a 1-1 relationship.

    Any ideas?
    Thanks,
    Jack

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    the problem as I see it is trying to coerce the error page.
    as I see it site pages are 'children' of sites, so the FK to site is fine.

    consider using a trigger to validate that there is only one error page
    consider using a trigger to enforce that there must be an error page

    alternatively have a one to one relationship which defines the sites error page
    so you have an entity for sites, for site pages and a siteerroepage
    sites

    sitepages has an FK to sites

    siteerrorpages as a PK of site, and FK to site and a FK to sitepages
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2008
    Posts
    89
    How about using a "initially deferred" clause?

    Example (What was first, the chicken or the egg?):

    CREATE TABLE chicken(cID INT PRIMARY KEY,eID INT);
    CREATE TABLE egg(eID INT PRIMARY KEY,cID INT);

    Foreign keys:

    ALTER TABLE chicken ADD CONSTRAINT chickenREFegg
    FOREIGN KEY (eID) REFERENCES egg(eID)
    INITIALLY DEFERRED DEFERRABLE;
    ALTER TABLE egg ADD CONSTRAINT eggREFchicken
    FOREIGN KEY (cID) REFERENCES chicken(cID)
    INITIALLY DEFERRED DEFERRABLE;

    INITIALLY DEFERRED DEFERRABLE tells Oracle to do deferred constraint checking. For example, to insert (1, 2) into chicken and (2, 1) into egg, you use:

    INSERT INTO chicken VALUES(1, 2);
    INSERT INTO egg VALUES(2, 1);
    COMMIT;

    Ok, maybe you already knew that and I was just to lazy too read your code thoroughly , but I just copied this out of somewhere and there you go...
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by freeBatjko
    How about using a "initially deferred" clause?
    nice idea -- if he's using oracle

    but unfortunately he's using mysql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2008
    Posts
    89
    Quote Originally Posted by r937
    nice idea -- if he's using oracle

    but unfortunately he's using mysql

    oops... I had that coming for not paying attention.

    Well, too bad mySQL doesn't have that option. It saved my life before.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Jak-S
    Each page has to belong to a site. However each site also has to have one of its pages defined as the error page.

    I guess I could add an "isErrorPage" field to the page table, however that would (potentially) allow multiple pages to be error pages, and it should only be a 1-1 relationship.
    If it's 1:1 between the error page and the site then why not move the fields for the error page into the site table. Not sure how feasible that is but it's worth thinking about.

  7. #7
    Join Date
    Aug 2007
    Posts
    6
    Hi everyone, thanks for all your help.

    If it's 1:1 between the error page and the site then why not move the fields for the error page into the site table. Not sure how feasible that is but it's worth thinking about.
    The problem with that is that I still need the page table, as there are lots of other pages in the site, that would just result in duplicate fields in different entities that do the same thing.

    alternatively have a one to one relationship which defines the sites error page so you have an entity for sites, for site pages and a siteerroepage
    sites
    I think something like that would be the best option, thanks.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Jak-S
    Each page has to belong to a site. However each site also has to have one of its pages defined as the error page.
    Put a trigger on the site table to automatically create an error page when a site is added.
    Put a trigger on the page table to prevent deletion of error pages.

    Quote Originally Posted by Jak-S
    I guess I could add an "isErrorPage" field to the page table, however that would (potentially) allow multiple pages to be error pages, and it should only be a 1-1 relationship.
    This is a good idea, unless there is another way to positively identify a page as an error page.
    To prevent duplicates in SQL Server, you would create an indexed view of error pages and put a constraint on it. Not sure if you can do the same thing in MySQL or not.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Create another table to represent the 1:1 relationship:

    CREATE TABLE `site_error_page`
    ( `id` int(11) NOT NULL REFERENCES `site` (`id`),
    `errorPageId` int(11) NOT NULL REFERENCES `site_page` (`id`)
    ,PRIMARY KEY (`id`)
    ,UNIQUE (`errorPageId`)
    );

    (untested)

    Of course this is still an optional relationship because you aren't forced to populate it for every page or site. Unfortunately there is no very satisfactory way around this. The difficulty of supporting 1:1 relationships is a well-known limitation of SQL

Posting Permissions

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