{"id":2503,"date":"2009-12-03T23:28:11","date_gmt":"2009-12-04T03:28:11","guid":{"rendered":"http:\/\/teamtutorials.com\/?p=2503"},"modified":"2013-12-07T19:35:54","modified_gmt":"2013-12-08T00:35:54","slug":"referential-integrity-with-mysql","status":"publish","type":"post","link":"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql","title":{"rendered":"Referential Integrity with MySQL"},"content":{"rendered":"
\n

You should not use this code on a production website.<\/h4>\n

Warning:<\/strong> This tutorial uses old techniques. It is insecure and will leave your server vulnerable to SQL Injection attacks<\/strong>.This tutorials also uses mysql_ functions that are no longer support. For updated tutorials look for a PDO<\/a> or MySQLi tutorial.This post will be delete or revised in the future.<\/p>\n<\/div>\n

Referential Integrity is when the rows from one table are \u201ccross-referenced\u201d and verified with those in another table. This tutorial will walk you through using MySQL to enforce this rule. For example, let\u2019s say you want to have a customer table that contains all the information about your customers and then you want to have an invoice table that will keep the information for invoices. This invoice table will need to be associated to the customer table through the customer id. This relationship would allow you to get all invoices for a customer by searching for their ID. Enforcing referential integrity also will prevent the insertion of a invoice with a customer ID that isn\u2019t in the customer table. Let\u2019s get started.<\/p>\n

First, we need to decide what our tables are going to look like. These could vary based on the application you are working with but for this tutorial, we will keep them basic. First, the customer table which doesn\u2019t need to be anything special except ensuring that the type is INNODB and that you set the primary key. <\/p>\n

\r\nCREATE TABLE customers \r\n(\r\n   customer_id INT NOT NULL AUTO_INCREMENT,\r\n   name \t\t\tVARCHAR(50),\r\n\taddress  \tVARCHAR(60),\r\n\tcity \t\t\tVARCHAR(45),\r\n\tstate\t\t\tVARCHAR(2),\r\n\tzip \t\t\tINT,\r\n   PRIMARY KEY (customer_id)\r\n) TYPE = INNODB;\r\n<\/pre>\n
This statement will create a table called customers that contains customer_id, name, address, city, state, and zip columns. It sets the customer id field to the primary key and makes it auto increment for each insert. Next we need to build the invoice table that will house the information about an invoice. (*Note: Normally an invoice would reference an item table to house the items for each invoice, but for sake of remaining simple, we will just have an items field that contains a varchar with comma separated items)<\/p>\n
\r\nCREATE TABLE invoice \r\n(\r\n   invoice_id\t\tINT NOT NULL AUTO_INCREMENT,\r\n   customer_id\t   INT,\r\n   submit_date    TIMESTAMP NOT NULL,    \r\n\ttotal\t\t\t\tDECIMAL(10,2),\r\n\ttax\t\t\t\tDECIMAL(10,2),\r\n\tshipping\t\t\tDECIMAL(10,2),\r\n\titems\t\t\t\tVARCHAR(100),\r\n   PRIMARY KEY(invoice_id),\r\n   INDEX (customer_id),\r\n   FOREIGN KEY (customer_id) REFERENCES customers (customer_id) \r\n) TYPE = INNODB;\r\n<\/pre>\n
This statement will create the invoice table containing invoice id, customer id, submit_date, total, tax, shipping, and items, columns. It sets the primary key to the invoice_id. INDEX tells it to index that column so that when we search the DB for that value it will be faster to return the results. Then the foreign key sets the customer_id of this table to look at the customer_id of the customer table to make sure it exists. <\/p>\n
Let\u2019s go ahead and make sure everything works as it is supposed to. First, we will try to insert an invoice for a customer ID that we haven\u2019t inserted yet. Use the following insert statement.<\/p>\n
\r\n insert into invoice (customer_id,submit_date,total,tax,shipping,items) values (1,current_timestamp,54.22,4.50,10.25,'RAM, Power Supply, Case');\r\n<\/pre>\n
\"referential_integrity_with_mysql_01\"<\/p>\n
You should receive an error similar to the above picture. This just means that there is no customer with that ID so you can\u2019t insert an invoice for them. Next, lets add the customer to the customer table.<\/p>\n
\r\ninsert into customers (name,address,city,state,zip) values ('Mike','100 That One Place','Anywhere','TX',22222);\r\n<\/pre>\n
Once you insert that row you should be able to successfully insert the invoice without any issues. The final thing that will referential integrity will enforce is to ensure you can\u2019t delete customers that have invoice attached to them. Let\u2019s try to ensure it works.<\/p>\n
\r\ndelete from customers where customer_id = 1;\r\n<\/pre>\n
\"referential_integrity_with_mysql_02\"<\/p>\n
You should receive the error above. If you delete the invoice from the invoice table first, you will then be able to delete the customer. That concludes this intro to referential integrity. I hope this is clear to you and thanks for reading.<\/p>\n","protected":false},"excerpt":{"rendered":"
Referential Integrity is when the rows from one table are \u201ccross-referenced\u201d and verified with those in another table. This tutorial will walk you through using MySQL to enforce this rule.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[73,82],"tags":[114,88,100,113,111,52,112],"yoast_head":"\nReferential Integrity with MySQL<\/title>\n<meta name=\"description\" content=\"Referential Integrity is when the rows from one table are \u201ccross-referenced\u201d and verified with those in another table. This tutorial will walk you through using MySQL to enforce this rule.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Mike Maguire\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/teamtutorials.com\/#website\",\"url\":\"https:\/\/teamtutorials.com\/\",\"name\":\"Team Tutorials\",\"description\":\"Learn something new today\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/teamtutorials.com\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"http:\/\/teamtutorials.com\/wp-content\/uploads\/2009\/12\/referential_integrity_with_mysql_01.jpg\",\"contentUrl\":\"http:\/\/teamtutorials.com\/wp-content\/uploads\/2009\/12\/referential_integrity_with_mysql_01.jpg\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#webpage\",\"url\":\"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql\",\"name\":\"Referential Integrity with MySQL\",\"isPartOf\":{\"@id\":\"https:\/\/teamtutorials.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#primaryimage\"},\"datePublished\":\"2009-12-04T03:28:11+00:00\",\"dateModified\":\"2013-12-08T00:35:54+00:00\",\"author\":{\"@id\":\"https:\/\/teamtutorials.com\/#\/schema\/person\/eb38d967529dbe49f7cbe082fd39105b\"},\"description\":\"Referential Integrity is when the rows from one table are \\u201ccross-referenced\\u201d and verified with those in another table. This tutorial will walk you through using MySQL to enforce this rule.\",\"breadcrumb\":{\"@id\":\"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/teamtutorials.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Referential Integrity with MySQL\"}]},{\"@type\":\"Person\",\"@id\":\"https:\/\/teamtutorials.com\/#\/schema\/person\/eb38d967529dbe49f7cbe082fd39105b\",\"name\":\"Mike Maguire\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/teamtutorials.com\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/36f2aa9a11241ca79ed05e758e36f3cb?s=96&d=mm&r=r\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/36f2aa9a11241ca79ed05e758e36f3cb?s=96&d=mm&r=r\",\"caption\":\"Mike Maguire\"},\"sameAs\":[\"http:\/\/mikemaguire.me\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Referential Integrity with MySQL","description":"Referential Integrity is when the rows from one table are \u201ccross-referenced\u201d and verified with those in another table. This tutorial will walk you through using MySQL to enforce this rule.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql","twitter_misc":{"Written by":"Mike Maguire","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebSite","@id":"https:\/\/teamtutorials.com\/#website","url":"https:\/\/teamtutorials.com\/","name":"Team Tutorials","description":"Learn something new today","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/teamtutorials.com\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#primaryimage","inLanguage":"en-US","url":"http:\/\/teamtutorials.com\/wp-content\/uploads\/2009\/12\/referential_integrity_with_mysql_01.jpg","contentUrl":"http:\/\/teamtutorials.com\/wp-content\/uploads\/2009\/12\/referential_integrity_with_mysql_01.jpg"},{"@type":"WebPage","@id":"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#webpage","url":"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql","name":"Referential Integrity with MySQL","isPartOf":{"@id":"https:\/\/teamtutorials.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#primaryimage"},"datePublished":"2009-12-04T03:28:11+00:00","dateModified":"2013-12-08T00:35:54+00:00","author":{"@id":"https:\/\/teamtutorials.com\/#\/schema\/person\/eb38d967529dbe49f7cbe082fd39105b"},"description":"Referential Integrity is when the rows from one table are \u201ccross-referenced\u201d and verified with those in another table. This tutorial will walk you through using MySQL to enforce this rule.","breadcrumb":{"@id":"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/teamtutorials.com\/database-tutorials\/referential-integrity-with-mysql#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/teamtutorials.com\/"},{"@type":"ListItem","position":2,"name":"Referential Integrity with MySQL"}]},{"@type":"Person","@id":"https:\/\/teamtutorials.com\/#\/schema\/person\/eb38d967529dbe49f7cbe082fd39105b","name":"Mike Maguire","image":{"@type":"ImageObject","@id":"https:\/\/teamtutorials.com\/#personlogo","inLanguage":"en-US","url":"https:\/\/secure.gravatar.com\/avatar\/36f2aa9a11241ca79ed05e758e36f3cb?s=96&d=mm&r=r","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/36f2aa9a11241ca79ed05e758e36f3cb?s=96&d=mm&r=r","caption":"Mike Maguire"},"sameAs":["http:\/\/mikemaguire.me"]}]}},"_links":{"self":[{"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/posts\/2503"}],"collection":[{"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/comments?post=2503"}],"version-history":[{"count":0,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/posts\/2503\/revisions"}],"wp:attachment":[{"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/media?parent=2503"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/categories?post=2503"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/teamtutorials.com\/wp-json\/wp\/v2\/tags?post=2503"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}