How to accurately set up a foreing key scenario in MySQL to accomplish my addressbook

Question

I have been making a database and learning along the way. I recently got into using InnoDB and using foreign keys to connect tables together.

But in all honestly I'm probably making my foreign keys blindly. What is the correct set and check list that I need to use when making a foreign key.

My understanding with foreign keys is that I have a Master Table, and any changes in my Master Table are reflected to any tables that hold a foreign key to a specific column in it.

So my current log-in system has a set up like this

users
=====
id PK
username
password

and my other tables look like this

contacts
========
id PK
user_id references `users`.`id`
group
name
address

groups
======
id PK
user_id
group_name

group_contacts
==============
id PK
group_id references `group`.`id`
contact_id references `contacts`.`id`

To my understanding these tables can be deleted when the Master Table is deleted using the ON DELETE CASCADE option correct?

My problem now is that I can't seem to make group_id and contact_id a Foreign key to groups.id and contacts.id with this setup. I get an error when running the SQL statements.

I'm trying to make my address book so that when a user places a contact into a group becomes all automated and I don't have to change much information. The group_contact table is what I THINK I will querying when I want to see where each contact belongs to. If I change the name of a group it will reflect across all tables right? This is where foreign keys come in and I'm confusing myself with how these keys should behave for me.

But like I said I can't seem to make a foreign key without getting an error.

I know I can Google my Foreign Key question, which I have but I can't seem to learn this way without getting feedback and input to my exact scenario ;(

Not to ask too much but because of my confusion I'm also having a hard time trying to see how I can make a PHP script to handle the group name change and query the database to pull down contacts that belong to a specific group.

This would really help me a lot guys, and I hope to learn something!

My query is this:

ALTER TABLE `list_`.`groups_contacts`
    ADD CONSTRAINT `group_id` FOREIGN KEY (`group_id`) REFERENCES `list_`.`groups` (`id`)
    ADD CONSTRAINT `contact_id` FOREIGN KEY (`contact_id`) REFERENCES `list_`.`contacts` (`id`);

My database looks like this:

CREATE TABLE `list_`.`buyer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL,
  `isClosed` tinyint(1) NOT NULL,
  `display_limit` int(1),
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `prop_address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(11) NOT NULL,
  `zip` varchar(5) NOT NULL,
  `cell_phone` varchar(16) NOT NULL,
  `home_phone` varchar(16) NOT NULL,
  `other1` varchar(16) NOT NULL,
  `other2` varchar(16) NOT NULL,
  `comments` text NOT NULL,
  `comment_exist` tinyint(1) NOT NULL,
  `comment_date` text NOT NULL,
  `date_added` date NOT NULL,
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;

CREATE TABLE `list_`.`company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL DEFAULT '0',
  `company_name` varchar(128) NOT NULL,
  PRIMARY KEY (`id`, `user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

CREATE TABLE `list_`.`contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL,
  `group` varchar(128) NOT NULL,
  `first_name` varchar(128) NOT NULL,
  `last_name` varchar(128) NOT NULL,
  `address` varchar(128) NOT NULL,
  `city` varchar(128) NOT NULL,
  `state` varchar(2) NOT NULL,
  `zip` int(5) NOT NULL,
  `phone_number` varchar(16) NOT NULL,
  `cell_number` varchar(16) NOT NULL,
  `work_number` varchar(16) NOT NULL,
  `fax_number` varchar(16) NOT NULL,
  `email` varchar(128) NOT NULL,
  `company` varchar(55) NOT NULL,
  `title` varchar(56) NOT NULL,
  `notes` text NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `group` (`group`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

CREATE TABLE `list_`.`groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL,
  `position` int(8) unsigned NOT NULL DEFAULT '0',
  `name` varchar(128) NOT NULL,
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`user_id`),
  KEY `user_id` (`user_id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;

CREATE TABLE `list_`.`prospect` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL,
  `isClosed` tinyint(1) DEFAULT '0',
  `display_limit` int(1) DEFAULT '0',
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `prop_address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(11) NOT NULL,
  `zip` varchar(5) NOT NULL,
  `cell_phone` varchar(16) NOT NULL,
  `home_phone` varchar(16) NOT NULL,
  `other1` varchar(16) NOT NULL,
  `other2` varchar(16) NOT NULL,
  `comments` text NOT NULL,
  `date_added` date NOT NULL,
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

CREATE TABLE `list_`.`seller` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL,
  `file` int(11) DEFAULT NULL,
  `isClosed` tinyint(1) NOT NULL,
  `display_limit` int(1) NOT NULL DEFAULT '0',
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `prop_address` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  `state` varchar(22) NOT NULL,
  `zip` varchar(5) NOT NULL,
  `cell_phone` varchar(16) NOT NULL,
  `home_phone` varchar(16) NOT NULL,
  `other1` varchar(16) NOT NULL,
  `other2` varchar(16) NOT NULL,
  `comments` text NOT NULL,
  `comment_exist` tinyint(1) NOT NULL,
  `comment_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_added` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

CREATE TABLE `list_`.`settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` tinyint(11) NOT NULL,
  `seller_display_limit` int(4) DEFAULT '0',
  `buyer_display_limit` int(4) DEFAULT '0',
  `prospect_display_limit` int(4) DEFAULT '0',
  `property_display_limit` int(4) DEFAULT '0',
  `date_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

CREATE TABLE `list_`.`users` (
  `id` tinyint(11) NOT NULL AUTO_INCREMENT,
  `md5_id` varchar(200) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `user_level` tinyint(1) DEFAULT '1',
  `first_name` varchar(200) NOT NULL,
  `last_name` varchar(200) NOT NULL,
  `email` varchar(200) DEFAULT NULL,
  `approved` int(1) NOT NULL,
  `banned` int(1) NOT NULL,
  `date_joined` date NOT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `activation_code` int(9) DEFAULT NULL,
  `ckey` varchar(220) NOT NULL,
  `ctime` varchar(220) NOT NULL,
  `last_logged_in` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `account_number` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

ALTER TABLE `list_`.`buyer`
  ADD CONSTRAINT `buyer_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE;

-- 
-- Constraints for table `company`
-- 
ALTER TABLE `list_`.`company`
  ADD CONSTRAINT `company_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE;

-- 
-- Constraints for table `contacts`
-- 
ALTER TABLE `list_`.`contacts`
  ADD CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `group_ibfk_2` FOREIGN KEY (`group`) REFERENCES `groups` (`name`) ON UPDATE CASCADE;

-- 
-- Constraints for table `groups`
-- 
ALTER TABLE `list_`.`groups`
  ADD CONSTRAINT `group_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

-- 
-- Constraints for table `prospect`
-- 
ALTER TABLE `list_`.`prospect`
  ADD CONSTRAINT `prospect_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

-- 
-- Constraints for table `seller`
-- 
ALTER TABLE `list_`.`seller`
  ADD CONSTRAINT `seller_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

-- 
-- Constraints for table `settings`
-- 
ALTER TABLE `list_`.`settings`
  ADD CONSTRAINT `settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);

Answer 1

I think you slightly misunderstood the concept of Foreign Keys. Changing the name of a group is not supposed to reflect on any other table, you just change your group-table.

Assuming you have this simple scenario, where one Contact can belong only to one Group:

Groups
id
group_name

Contacts
id
group_id   -> Groups.id
first_name
...

Your Contacts do not have the information about the group_name. You just store the reference to your Groups.id.

If you want to query your contacts and the name of their group, you join those two tables:

Select c.first_name, g.group_name
From contacts c
Join groups g On ( g.id = c.group_id )

---

If you want to change the name of a group, you do a simple update:

Update groups
Set group_name = 'Your new group name'
Where id = 99 --# The id of the group to rename

This only changes your Groups table, without changing your Contacts.

---

Your Foreign Key on Contacts.group_id is there to ensure the referential integrity. This means, that you are not allowed to have a contact with group_id=88 if there is no record in Groups with id=88.

Using ON DELETE CASCADE would delete all Contacts that are members of a certain group, once you delete that group.