MySQL Forums :: Foreign Keys (Referential Integrity) :: 2 Columns as PK, Refering by FK to one of them and over another table to the other...

Hi Chad,

once again, thank you for the example.

I've set it up the first approach you've shown.

http://img828.imageshack.us/img828/3631/ermsecondtry.png


CREATE TABLE IF NOT EXISTS `vat` (
`vat_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`start_date` DATETIME NOT NULL ,
`end_date` DATETIME NULL ,
`rate` DECIMAL(6,2) NULL ,
`replaced_vat` INT UNSIGNED NULL ,
PRIMARY KEY (`vat_id`) ,
CONSTRAINT `vat_replaced_vat`
FOREIGN KEY (`replaced_vat` )
REFERENCES `vat` (`vat_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `country_vat` (
`iso` CHAR(2) NOT NULL ,
`vat` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`iso`, `vat`) ,
CONSTRAINT `country_vat_vat`
FOREIGN KEY (`vat` )
REFERENCES `vat` (`vat_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `country_vat_iso`
FOREIGN KEY (`iso` )
REFERENCES `country` (`iso` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


... and added a "replaced_vat" field to see the history which vat gets replace by a new one. So on a certain date the system can recognize the vat switch and notify the user before he starts to write any invoices.

Looks good?! :)

Daniel