Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/articles/mysql/)
-   -   Setting-Up a Relational Database in MySQL (http://www.go4expert.com/articles/setting-relational-database-mysql-t13386/)

hurricanesoftwares 29Aug2008 12:09

Setting-Up a Relational Database in MySQL
 
Relational Database Design is one of the most powerful ways to ensure data integrity and a great way to kick-off any project. Very often the first thing developers do when starting a new project, or stub-project, is to design the database. This way the structure of the application is already in place and we just have to fill in the pieces with some server-side code. Iíve found when adding relational constraints to your database design you add in a very powerful error reporting tool that will let you know during the development process that you have allowed something to happen that shouldnít have. In this article, I go through, step by step, showing how to set up a simple relational database and discuss the benefits that are enjoyed.

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


http://www.go4expert.com/images/arti...relation-1.png

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.

http://www.go4expert.com/images/arti...relation-2.png

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.

http://www.go4expert.com/images/arti...relation-3.png

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.
ON UPDATE:
  • 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.
  • 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 :)
  • 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
Once we have our simple relational database configured try to add a book with an author_id that doesnít exist. MySQL should give you an error like this:

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 :happy:

pradeep 11Sep2008 01:44

Re: Setting-Up a Relational Database in MySQL
 
Nice article.

hurricanesoftwares 12Sep2008 17:35

Re: Setting-Up a Relational Database in MySQL
 
Good to know that you liked it. Did it helped ?

codestorm 18Sep2008 17:15

Re: Setting-Up a Relational Database in MySQL
 
Simple and nice tutorial, but this is only helpful to those who use phpMyAdmin.

Oyetunde 19Sep2008 20:05

Re: Setting-Up a Relational Database in MySQL
 
i like your work.
i just started to learn mysql using the phpmyadmin and i'm a little confussed.
can you help me with materials on phpmyadmin for relatioal database
thanks

SAint 20Sep2008 21:49

Re: Setting-Up a Relational Database in MySQL
 
Hello house please i need help on how to setup a Web server. i have tried the help menu on the operating system and the front page.........

thanks

hurricanesoftwares 23Sep2008 10:32

Re: Setting-Up a Relational Database in MySQL
 
Hi,

I would recommend you go by mysql tutorials first. Then create simple databases and perform operations like insert, update, delete, rename, drop, alter etc. from phpmyadmin and sql queries both.
You can find various mysql tutorials on Google.

jspguy 8Oct2008 13:28

Re: Setting-Up a Relational Database in MySQL
 
The same article is also found here - ht*p://www.mikebernat.com/blog/Setting-Up_a_Relational_Database_in_MySQL


All times are GMT +5.5. The time now is 19:40.