2

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

asked Mar 22, 2012 at 6:33
2
  • 1
    You have not defined any Foreign Key in the SQL script. None at all. Commented Mar 22, 2012 at 7:12
  • so how to define the foreign keys in this? Commented Mar 22, 2012 at 7:31

1 Answer 1

6

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 ;
answered Mar 22, 2012 at 7:35

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.