Discussion on:

An introduction to foreign keys and referential integrity in MySQL

The downloadable version of this article is available here:
http://techrepublic.com.com/5138-9592-6035433.html

Foreign keys are relatively new in MySQL ? has their inclusion simplified your database designing?

INT(4) and TINYINT(4) are different data types. Cannot create second table with foreign key unless they are both the same data type...

Short But Sweet

Very nice article, I found the necessity of knowing foreign keys when I was not able to delete a column in a table of my database which are referenced as foreign key in another table. I got more simpler explanation on http://sulabhjain.wordpress.com/2010/02/01/foreign-keys-and-referential-integrity-in-database/

i get this every time i try to update the second table which is business,i opt for default null and it added jus null please help
-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: May 03, 2011 at 05:19 AM
-- Server version: 5.1.37
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `dir`
--

-- --------------------------------------------------------

--
-- Table structure for table `bussiness`
--

CREATE TABLE IF NOT EXISTS `bussiness` (
`bus_id` int(11) NOT NULL AUTO_INCREMENT,
`bus_name` varchar(20) DEFAULT NULL,
`bus_regNo` varchar(20) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`bus_id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `bussiness`
--


-- --------------------------------------------------------

--
-- Table structure for table `bus_contacts`
--

CREATE TABLE IF NOT EXISTS `bus_contacts` (
`cont_id` int(11) NOT NULL AUTO_INCREMENT,
`address` varchar(20) DEFAULT NULL,
`postal_code` varchar(10) DEFAULT NULL,
`mobile1` varchar(12) DEFAULT NULL,
`mobile2` varchar(12) DEFAULT NULL,
`landline1` varchar(12) DEFAULT NULL,
`landline2` varchar(12) DEFAULT NULL,
`email` varchar(30) DEFAULT NULL,
`url` varchar(30) DEFAULT NULL,
`cont_person` varchar(30) DEFAULT NULL,
`cont_personTitle` varchar(30) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`cont_id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

--
-- Dumping data for table `bus_contacts`
--


-- --------------------------------------------------------

--
-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
`cat_id` int(11) NOT NULL AUTO_INCREMENT,

`uid` int(11) DEFAULT NULL,
`cat_name` varchar(30) DEFAULT NULL,
`cat_desc` varchar(100) DEFAULT NULL,
PRIMARY KEY (`cat_id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

--
-- Dumping data for table `category`
--


-- --------------------------------------------------------

--
-- Table structure for table `location`
--

CREATE TABLE IF NOT EXISTS `location` (
`loc_id` int(11) NOT NULL AUTO_INCREMENT,
`city` varchar(30) DEFAULT NULL,
`suburb` varchar(30) DEFAULT NULL,
`street_name` varchar(30) DEFAULT NULL,
`building_name` varchar(30) DEFAULT NULL,
`floor_name` varchar(30) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`loc_id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `location`
--


-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
`prod_id` int(11) NOT NULL AUTO_INCREMENT,
`prod_desc` varchar(30) DEFAULT NULL,
`url` varchar(30) DEFAULT NULL,
`url2` varchar(30) DEFAULT NULL,
`url3` varchar(30) DEFAULT NULL,
`url4` varchar(30) DEFAULT NULL,
`url5` varchar(30) DEFAULT NULL,
`url6` varchar(30) DEFAULT NULL,
`uid` int(11)DEFAULT NULL ,
PRIMARY KEY (`prod_id`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

--
-- Dumping data for table `products`
--


-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) DEFAULT NULL,
`second_name` varchar(20) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`mobile` varchar(20) DEFAULT NULL,
`alt_mobile` varchar(20) DEFAULT NULL,
`telephone` varchar(20) DEFAULT NULL,
`address` varchar(20) DEFAULT NULL,
`username` varchar(20) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user`
--

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bussiness`
--
ALTER TABLE `bussiness`
ADD CONSTRAINT `bussiness_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`);

--
-- Constraints for table `bus_contacts`
--
ALTER TABLE `bus_contacts`
ADD CONSTRAINT `bus_contacts_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`);

--
-- Constraints for table `category`
--
ALTER TABLE `category`
ADD CONSTRAINT `category_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`);

--
-- Constraints for table `location`
--
ALTER TABLE `location`
ADD CONSTRAINT `location_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`);

--
-- Constraints for table `products`
--
ALTER TABLE `products`
ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`);