Database Integrity

Tips: If you encountered an issue that you could not add any record in spj table, even though the foreign key values match the referenced values in s, p, and j tables. If you had the issue, make sure the ENGINE is set to InnoDB for all fours tables, i.e., s, p, j, spj. Some useful tips for working on the integrith homework:

  1. Turn on/off foreign key contraints. Just type the following under mysql prompt.
    SET FOREIGN_KEY_CHECKS=0; //0 is off and 1 is on
    
  2. Check if the tables already violate foreign key constraints by the following query.
    mysql> select p_num from spj where p_num NOT IN (select p_num from p);
    Empty set (0.00 sec)
    
  3. Back up selected tables instead of all from your database by
    mysqldump -u yzhang -p ZHANG s p j spj > spj_backup.sql
    Enter password:
    

The following is the definition of spj table:

DROP TABLE IF EXISTS `spj`;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `spj` (
  `s_num` char(5) NOT NULL default '',
  `p_num` char(5) NOT NULL default '',
  `j_num` char(5) NOT NULL default '',
  `qty` decimal(9,0) default NULL,
  PRIMARY KEY  (`s_num`,`p_num`,`j_num`),
  KEY `s_num` (`s_num`),
  KEY `p_num` (`p_num`),
  KEY `j_num` (`j_num`),
  CONSTRAINT `spj_ibfk_1` FOREIGN KEY (`j_num`) REFERENCES `j` (`J_NUM`),
  CONSTRAINT `spj_ibfk_2` FOREIGN KEY (`s_num`) REFERENCES `s` (`s_num`),
  CONSTRAINT `spj_ibfk_3` FOREIGN KEY (`p_num`) REFERENCES `p` (`p_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;