• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to use foreign keys to attain referential integrity in MySQL

By admin

Referential Integrity

Referential integrity means that relationships between tables are consistent. MySQL enforces referential integrity by using foreign key constraints. When one table (the child table) has a foreign key to another table (the parent table), MySQL prevents you from adding a record to the child table if there is no corresponding record in the parent table. It also facilitates cascading updates and deletes to ensure that changes made to the child table are reflected in the parent table.

Relationships determine the results of SQL statements:

  • An INSERT into a child table (without a foreign key to the parent table) does not complete.
  • Using foreign keys on joined columns improves performance.
  • When you UPDATE or DELETE data, related data in the linked table can be automatically updated or deleted with the CASCADE option.

Using Foreign Keys

Specify a key for every table in the relationship by using the most frequently queried column or columns, or an auto-increment value if there is no obvious primary key. A primary key is not mandatory, but there must be an index in the parent table which lists the referenced columns first, in the same order. For fast join performance, define foreign keys on the join columns, and declare those columns with the same data type in each table.

Referential Actions

If you attempt to insert or update data in a child column, and that value does not exist in the parent column, the operation fails. If you attempt to change a value in the parent column such that a dependant child column would be affected by that change, the result depends on the referential action.

Specify the referential action within ON UPDATE and ON DELETE subclauses of the FOREIGN KEYclause.

  • By default, the insert or delete operation fails and you receive an error. This is the same as specifying RESTRICT or NO ACTION.
  • If you use CASCADE, foreign keys propagate deletes or updates to all affected tables.
  • If you use SET NULL, values in the foreign key column(s) of the child table are set to NULL instead of the new parent key value.

The following example relates parent and child tables through a single-column primary key. The foreign key contains an ON DELETE CASCADE subclause to ensure that if you delete a record in the parent table, MySQL deletes any corresponding records in the child table:

CREATE TABLE parent ( 
id INT NOT NULL, 
PRIMARY KEY (id) 
) ENGINE=INNODB;

CREATE TABLE child (
 id INT, 
 parent_id INT, 
 INDEX par_ind (parent_id), 
 FOREIGN KEY (parent_id) 
     REFERENCES parent(id) 
	 ON DELETE CASCADE 
) ENGINE=INNODB;

Filed Under: mysql, MySQL Cluster

Some more articles you might also be interested in …

  1. How To Create a Local Yum Repository for MySQL Enterprise Packages
  2. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  3. MySQL : how to set (change) user password
  4. Understanding MySQL Privileges
  5. MySQL Cluster Point-In-Time Recovery (PITR)
  6. How to List and Set SELinux Context for MySQL Server
  7. How to Migrate from Oracle to MySQL
  8. How to Install MySQL Cluster on a single server for testing purposes
  9. System Tuning for MySQL Server
  10. Understanding the REVOKE statement in MySQL

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ICMP redirects on CentOS/RHEL
  • What are Oracle Key Vault Roles
  • What Is Oracle Key Vault
  • Auditing with Oracle Database Vault Reports
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary