Using Foreign Keys in database

Question

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

You have not defined any Foreign Key in the SQL script. None at all. — ypercubeᵀᴹ, Commented Mar 22, 2012 at 7:12

ypercubeᵀᴹ · Accepted Answer · 2012-03-22 07:44:29Z

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 ;

Stack Exchange Network

Using Foreign Keys in database

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mysql
database-design
database-recommendation
or ask your own question.

Hot Network Questions

Using Foreign Keys in database

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysqldatabase-designdatabase-recommendation or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mysql
database-design
database-recommendation
or ask your own question.