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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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. Beginners Guide to MySQL Data Types
  2. How to Troubleshoot InnoDB Lock Issues
  3. how to capture the SQL statements of a binary log and edit them before restoring the data (Point-in-time Recovery for MySQL)
  4. How to find Which Process Is Killing mysqld With SIGKILL or SIGTERM on Linux
  5. How to Add New Nodes To an Existing MySQL Cluster Setup
  6. What are the various types of locking used in the MySQL Server
  7. How to find the size of a MySQL database
  8. What is the purpose of “mysql.sys@localhost” user
  9. How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment
  10. Multi-Versioning in MySQL Database

You May Also Like

Primary Sidebar

Recent Posts

  • protonvpn-cli Command Examples in Linux
  • protonvpn-cli connect Command Examples
  • procs Command Examples in Linux
  • prlimit: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright