Circular reference problem

Jak-S · #1 (**permalink**) 01-15-09, 06:18

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

healdem · #2 (**permalink**) 01-15-09, 06:54

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

freeBatjko · #3 (**permalink**) 01-15-09, 07:33

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...

r937 · #4 (**permalink**) 01-15-09, 07:38

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

freeBatjko · #5 (**permalink**) 01-15-09, 07:41

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.

mike_bike_kite · #6 (**permalink**) 01-15-09, 07:46

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.

Jak-S · #7 (**permalink**) 01-15-09, 07:50

Hi everyone, thanks for all your help.

Quote:

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.

Quote:

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.

blindman · #8 (**permalink**) 01-15-09, 10:00

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.

dportas · #9 (**permalink**) 01-15-09, 14:05

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

Internet Services

Web Development

Digital Marketing

Consumer Tech