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\[code\]users=====id PKusernamepassword\[/code\]and my other tables look like this\[code\]contacts========id PKuser_id references `users`.`id`groupnameaddressgroups======id PKuser_idgroup_namegroup_contacts==============id PKgroup_id references `group`.`id`contact_id references `contacts`.`id`\[/code\]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 \[code\]group_id\[/code\] and \[code\]contact_id\[/code\] a Foreign key to \[code\]groups\[/code\].\[code\]id\[/code\] and \[code\]contacts\[/code\].\[code\]id\[/code\] 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 \[code\]group_contact\[/code\] 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:\[code\]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`);\[/code\]My database looks like this:\[code\]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`);\[/code\]
How to accurately set up a foreing key scenario in MySQL to accomplish my addressbook
- Thread starter corbin
- Start date