Database Integrity

Database Integrity: Foreign Key Constraints for MySQL Database

If you encounter an issue that doesn't allow you to add any records in the spj table, even though the foreign key values match the referenced values in the s, p, and j tables, make sure that the database ENGINE is set to InnoDB for all the fours tables, i.e., s, p, j, spj. The settings for ENGINE can be seen at the bottom of a table script.

The following is the definition of the 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 (`s_num`) REFERENCES `s` (`s_num`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `spj_ibfk_2` FOREIGN KEY (`p_num`) REFERENCES `p` (`p_num`),
  CONSTRAINT `spj_ibfk_3` FOREIGN KEY (`j_num`) REFERENCES `j` (`j_num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

Turn on/off foreign key contraints. Just type the following command under mysql prompt.
```
SET FOREIGN_KEY_CHECKS=0; //0 is off and 1 is on
```
Check if there are any records in a table that already violated any foreign key constraints defined for the table. For example, the following query checks if any "illegal" project numbers have already been inserted into the spj table. If none, the query will return empty.
```
mysql> select p_num from spj where p_num NOT IN (select p_num from p);
Empty set (0.00 sec)
```

Back up selected tables instead of all from your database by

mysqldump --no-tablespaces -u yzhang -p ZHANG s p j spj > spj_backup.sql
Enter password:

Use the information_schema database to view metadata about your table definitions.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| JDOE               |
+--------------------+
2 rows in set (0.00 sec)

mysql> use information_schema;

mysql> select table_name from tables;
+---------------------------------------+
| table_name                            |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| OPTIMIZER_TRACE                       |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
| INNODB_SYS_DATAFILES                  |
| INNODB_LOCK_WAITS                     |
| INNODB_SYS_TABLESTATS                 |
| INNODB_CMP                            |
| INNODB_METRICS                        |
| INNODB_CMP_RESET                      |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMPMEM_RESET                   |
| INNODB_FT_DELETED                     |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_COLUMNS                    |
| INNODB_SYS_INDEXES                    |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_SYS_FIELDS                     |
| INNODB_CMP_PER_INDEX_RESET            |
| INNODB_BUFFER_PAGE                    |
| INNODB_CMPMEM                         |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_FT_BEING_DELETED               |
| INNODB_SYS_TABLESPACES                |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_SYS_TABLES                     |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_FT_CONFIG                      |
| cellar                                |
| j                                     |
| p                                     |
| s                                     |
| spj                                   |
+---------------------------------------+
64 rows in set (0.00 sec)

mysql> select * from USER_PRIVILEGES;
+--------------------+---------------+----------------+--------------+
| GRANTEE            | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+---------------+----------------+--------------+
| 'jdoe'@'localhost' | def           | USAGE          | NO           |
+--------------------+---------------+----------------+--------------+
1 row in set (0.00 sec)

mysql>  select * from ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ENGINE             | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA   | SAVEPOINTS |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        | 
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)


SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME 
FROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| cellar     | bin_num     | PRIMARY         | NULL                  | NULL                   |
| j          | J_NUM       | PRIMARY         | NULL                  | NULL                   |
| p          | P_NUM       | PRIMARY         | NULL                  | NULL                   |
| s          | S_NUM       | PRIMARY         | NULL                  | NULL                   |
| spj        | s_num       | PRIMARY         | NULL                  | NULL                   |
| spj        | p_num       | PRIMARY         | NULL                  | NULL                   |
| spj        | j_num       | PRIMARY         | NULL                  | NULL                   |
| spj        | j_num       | spj_ibfk_1      | j                     | J_NUM                  |
| spj        | s_num       | spj_ibfk_2      | s                     | S_NUM                  |
| spj        | p_num       | spj_ibfk_3      | p                     | P_NUM                  |
+------------+-------------+-----------------+-----------------------+------------------------+
10 rows in set (0.00 sec)

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_SCHEMA = 'JDOE' AND REFERENCED_TABLE_NAME = 's';
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| spj        | s_num       | spj_ibfk_2      | s                     | S_NUM                  |
+------------+-------------+-----------------+-----------------------+------------------------+

select * from information_schema.REFERENTIAL_CONSTRAINTS;
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | UNIQUE_CONSTRAINT_CATALOG | UNIQUE_CONSTRAINT_SCHEMA | UNIQUE_CONSTRAINT_NAME | MATCH_OPTION | UPDATE_RULE | DELETE_RULE | TABLE_NAME | REFERENCED_TABLE_NAME |
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
| def                | JDOE              | spj_ibfk_1      | def                       | JDOE                     | PRIMARY                | NONE         | RESTRICT    | CASCADE     | spj        | s                     |
| def                | JDOE              | spj_ibfk_2      | def                       | JDOE                     | PRIMARY                | NONE         | RESTRICT    | RESTRICT    | spj        | p                     |
| def                | JDOE              | spj_ibfk_3      | def                       | JDOE                     | PRIMARY                | NONE         | RESTRICT    | RESTRICT    | spj        | j                     |
+--------------------+-------------------+-----------------+---------------------------+--------------------------+------------------------+--------------+-------------+-------------+------------+-----------------------+
3 rows in set (0.01 sec)