Mysql – Using Foreign Keys in database

database-designdatabase-recommendationMySQL

I am doing a small application for Invoice.In that the tables will be like this

=== invoice ===
id (pk)
customer_id (fk)
invoice_title
invoice_issue_date
due_date
description
created_by
updated_by
created_at
updated_at

=== invoice_items ===
id (pk)
invoice_id (fk)
customer_id (fk)
product_name 
unit_cost 
quantity 
apply_tax1 
apply_tax2 
discount 
description 
created_by 
updated_by 
created_at 
updated_at 

=== customers ===
id (pk)
business_email 
business_name 
customer_name 
business_address 
town/city 
state/province 
postalzip_code 
country 
phone 
mob 
fax 
created_by 
updated_by 
created_at 
updated_at 

=== estimates ===
id (pk)
address 
estimate_title 
estimate_no 
purchase_order_no 
estimate_date 
description 
created_by 
updated_by 
created_at 
updated_at 

=== estimate_items ===
id (pk)
estimate_id (fk)
customer_id (fk)
product/service 
unit_cost 
quantity 
apply_tax1 
apply_tax2 
discount 
description 
created_by 
updated_by 
created_at
updated_at 

=== projects ===
id (pk)
customer_id (fk)
project_name 
description 
purchase_order_no 
budget float 
billing_method 
flat_amount 
created_by 
updated_by 
created_at 
updated_at 

=== tasks ====
id (pk)
task_name 
description 
billable 
task_rate 
start_date 
end_date 
estimated_hours 
billing_method 
flat_amount
created_by 
updated_by 
created_at 
updated_at

To do all this in MySQL daabase I made this SQL query

-- phpMyAdmin SQL Dump
-- version 3.3.10deb1
-- http://www.phpmyadmin.net
--
-- Host: localhost

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `nt_invoice`
--

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

--
-- Table structure for table `nt_customers`
--

CREATE TABLE IF NOT EXISTS `nt_customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `business_email` varchar(80) NOT NULL DEFAULT '',
  `business_name` varchar(80) NOT NULL DEFAULT '',
  `customer_name` varchar(80) NOT NULL DEFAULT '',
  `business_address` text,
  `town/city` varchar(80) NOT NULL DEFAULT '',
  `state/province` varchar(80) NOT NULL DEFAULT '',
  `postalzip_code` varchar(25) NOT NULL DEFAULT '',
  `country` varchar(80) NOT NULL DEFAULT '',
  `phone` varchar(25) DEFAULT NULL,
  `mob` varchar(25) DEFAULT NULL,
  `fax` varchar(25) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_customers`
--


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

--
-- Table structure for table `nt_estimates`
--

CREATE TABLE IF NOT EXISTS `nt_estimates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `address` text NOT NULL,
  `estimate_title` varchar(80) NOT NULL,
  `estimate_no` varchar(25) NOT NULL,
  `purchase_order_no` varchar(25) NOT NULL,
  `estimate_date` date NOT NULL,
  `description` text NOT NULL,
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_estimates`
--


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

--
-- Table structure for table `nt_estimate_items`
--

CREATE TABLE IF NOT EXISTS `nt_estimate_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `estimate_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `product/service` varchar(45) NOT NULL,
  `unit_cost` varchar(45) NOT NULL,
  `quantity` varchar(45) NOT NULL,
  `apply_tax1` varchar(25) NOT NULL,
  `apply_tax2` varchar(25) NOT NULL,
  `discount` varchar(25) NOT NULL,
  `description` text NOT NULL,
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `estimate_id` (`estimate_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_estimate_items`
--


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

--
-- Table structure for table `nt_invoices`
--

CREATE TABLE IF NOT EXISTS `nt_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `invoice_title` varchar(80) NOT NULL,
  `invoice_issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_invoices`
--


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

--
-- Table structure for table `nt_invoice_items`
--

CREATE TABLE IF NOT EXISTS `nt_invoice_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `invoice_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `product_name` varchar(45) NOT NULL,
  `unit_cost` float DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `apply_tax1` float NOT NULL,
  `apply_tax2` float NOT NULL,
  `discount` float NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_invoice_items`
--


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

--
-- Table structure for table `nt_projects`
--

CREATE TABLE IF NOT EXISTS `nt_projects` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `project_name` varchar(100) NOT NULL,
  `description` varchar(100) NOT NULL,
  `purchase_order_no` varchar(80) NOT NULL,
  `budget` float NOT NULL DEFAULT '0',
  `billing_method` varchar(80) NOT NULL,
  `flat_amount` float NOT NULL DEFAULT '0',
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_projects`
--


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

--
-- Table structure for table `nt_tasks`
--

CREATE TABLE IF NOT EXISTS `nt_tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_id` int(11) NOT NULL,
  `task_name` varchar(100) NOT NULL,
  `description` varchar(100) NOT NULL,
  `billable` varchar(80) NOT NULL,
  `task_rate` float NOT NULL DEFAULT '0',
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `estimated_hours` varchar(25) NOT NULL,
  `billing_method` varchar(80) NOT NULL,
  `flat_amount` float NOT NULL DEFAULT '0',
  `created_by` varchar(80) NOT NULL,
  `updated_by` varchar(80) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `nt_tasks`
--

So I want to know is this the correct?I have the doubt in ForeignKeys(fk), are they properly defined in this query?please help me to do this as I am newbie to MySQL.Any help and suggestions are highly appreciable

Best Answer

No, you have not defined any Foreign key constraints.

The MySQL documentation page about FOREIGN KEY constraints has more details and possible options.

Here's an example for the invoices -> customers Foreign Key:

CREATE TABLE IF NOT EXISTS `nt_invoices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `invoice_title` varchar(80) NOT NULL,
  `invoice_issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `description` text NOT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),

  KEY customer_id_index (customer_id),        --- index to be used by the FK

  CONSTRAINT customer_invoice_fk              --- the FK constraint's name
    FOREIGN KEY (customer_id)
      REFERENCES nt_customers(id)
      ON UPDATE CASCADE                       --- or other action
      ON DELETE RESTRICT                      --- here, too  

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Related Question