Setting-Up a Relational Database in MySQL
Author
hurricanesoftwares ( Light Poster )
Yet to provide details about himself
Recent Articles
- Replace Statement in MySQL, Started by pradeep in MySQL / PostGRESQL
- Why Prepare SQL Queries??, Started by pradeep in MySQL / PostGRESQL
- Database Transaction Auditing With MySQL and PostgreSQL Using Triggers, Started by pete_bisby in MySQL / PostGRESQL
- Reset MySQL password for Windows Vista, Started by shabbir in MySQL / PostGRESQL
- Foreign Keys in MySQL, Started by pradeep in MySQL / PostGRESQL
Similar Articles
- Database Transaction Auditing With MySQL and PostgreSQL Using Triggers, Started by pete_bisby in MySQL / PostGRESQL
- Steps To Secure Your MySQL Databases, Started by Mary in MySQL / PostGRESQL
- SQL Server, Oracle and MySQL basic questions and answers, Started by Shree Limbkar in Database
- MySQL Storage Engines, Started by Janu in MySQL / PostGRESQL
Let’s take a step back and describe what a relational database looks like. In any normal database design there are fields in one table that reference another table. For example, a books table might have a field labeled author_id which is meant to come from a table named authors. Creating hard-coded relations solidifies these associations and actually returns a MySQL error if violated.
As I hinted in the opening I have found this to be invaluable during the development and testing process as MySQL will immediately let me know that I have made a glaring error that otherwise may not have been noticed until after the service has launched. At that point the data could be irreparably corrupt and forced to start from scratch.
So let’s get right to it. For the purposes of this article, I’m going to pretend I’m creating a simple Books and Authors website with a simple 2-table setup. The first step is to create our tables.
Code: SQL
CREATE TABLE `library`.`books` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR( 150 ) NOT NULL ,
`author_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY ( `id` ) ,
INDEX ( `author_id` )
) ENGINE = InnoDB
Code: SQL
CREATE TABLE `authors` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(50) collate utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
Nothing too fancy here. Couple things to notice:
1. Each table MUST be using the InnoDB storage engine. InnoDB is currently the only main-stream storage engine offered by MySQL to support relational design. More on this in my article: MyISAM vs InnoDB
2. The `author_id` field in the `books` table MUST be indexed and the same datatype as the `id` field in `authors`.
The next step is to set up the relations. Open the `authors` table and take a look at the view. Under the table there should be a link titled ‘Relation View’ - Click it.
phpMyAdmin has a great gui for setting up relations and actions. If the `author_id` row below doesn’t look like mine, make sure you have it indexed.
Here, I’ve setup a link on the `books` table and the `author_id` field. This will enforce the fact that any value inserted in this field MUST be present in the `authors.id` table as well. But what about these other settings?
ON DELETE:
- CASCADE:
- This means if an author is deleted from the authors table, all of his books will also be automatically deleted.
- This option is great to keep your data clean and reduce the number of delete quieries required when deleting an author.
- SET NULL:
- Instead of deleting the book record when an author is deleted, books.author_id is set to NULL, effectively orphaning the book.
- This feature is great if you want to be able to keep the books and come back at a later time to reassign them. Otherwise, without this feature, the books would still be referencing an author_id that doesn’t exist.
- Note: If you try to set this option and phpMyAdmin tells you to check your datatypes, make sure the field is allowing null values.
- NO ACTION:
- When a delete query is issued on an author that has books, MySQL will not allow this and return a Foreign_Key Constraint error.
- It could be nice to identify this and re-word it to let the user know that if they would like to delete this author they need to re-assign his books or delete them all-together.
- Note: If you use this option please remember to re-word the MySQL error to something the user can easily understand.
- RESTRICT:
- Same as NO ACTION
- From MySQL Manual: Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION and RESTRICT are the same.
- For the most part the options described above are going to act in the same manner they did for ON DELETE as they will with ON UPDATE. I’ll just run through some examples real quick.
- CASCADE:
- If, for some reason, an author’s id gets updated than CASCADE will update all his corresponding books with the new value. Extremely handy.
- If, for some reason, an author’s id gets updated than CASCADE will update all his corresponding books with the new value. Extremely handy.
- SET NULL
- Same as CASCADE except instead of updating it with the new value, it will set it to null. I’m sure there is a perfectly good use for this but I haven’t run into it yet. If anyone can enlighten me please do
- Same as CASCADE except instead of updating it with the new value, it will set it to null. I’m sure there is a perfectly good use for this but I haven’t run into it yet. If anyone can enlighten me please do
- NO ACTION / RESTRICT:
- Same as ON DELETE, will throw an error if you try to update an author_id. I’m also having trouble finding a real-world example of when this could be useful
- Same as ON DELETE, will throw an error if you try to update an author_id. I’m also having trouble finding a real-world example of when this could be useful
Cannot add or update a child row: a foreign key constraint fails (`library/books`, CONSTRAINT `books_ibfk_1` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
I hope it will help
Linear Mode
