Alex Rivera | Logout

In SQL, is it OK for two tables to refer to each other?

Asked 2012-05-04T09:58:01.353
52

In this system, we store products, images of products (there can be many image for a product), and a default image for a product. The database:

CREATE TABLE  `products` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NOT NULL,
  `DESCRIPTION` text NOT NULL,
  `ENABLED` tinyint(1) NOT NULL DEFAULT '1',
  `DATEADDED` datetime NOT NULL,
  `DEFAULT_PICTURE_ID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `Index_2` (`DATEADDED`),
  KEY `FK_products_1` (`DEFAULT_PICTURE_ID`),
  CONSTRAINT `FK_products_1` FOREIGN KEY (`DEFAULT_PICTURE_ID`) REFERENCES `products_pictures` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8;


CREATE TABLE  `products_pictures` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IMG_PATH` varchar(255) NOT NULL,
  `PRODUCT_ID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_products_pictures_1` (`PRODUCT_ID`),
  CONSTRAINT `FK_products_pictures_1` FOREIGN KEY (`PRODUCT_ID`) REFERENCES `products` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

as you can see, products_pictures.PRODUCT_ID -> products.ID and products.DEFAULT_PICTURE_ID -> products_pictures.ID, so a cycle reference. Is it OK?

Edit
Report

1 Answer

-2

that is not a cyclic ref, that is pk-fk

answered 2012-05-04T10:00:02.350

Your Answer