• 0

[MySQL] Help with my table structure


Question

Hello,

I'm trying to make an address book. And have made my tables like this:

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 ;

My logic here is that I have all the contacts in the `contacts` table, from there I have a column called `group` that I use to filter the contacts into groups.

Then I have a table called `groups` that I'll use to keep track of what groups where created by a specific user and fill a <select><option> with these groups so they can move the contacts around.

When a group is deleted I will throw back an error no letting it be deleted if the group contains contacts. I can probably query the contacts to see what group they belong to and if they belong to the group being deleted then I'll move them into a group called Uncategorized or something.

But if they choose to accept and delete ALL contacts within a group then go ahead and delete the group and all child rows that belong to that group.

I am having trouble creating my Foreing Keys in the `contacts` table. No matter what combination of index and keys I try I still can't make it work.

-- 
-- Constraints for table `contacts`
-- 
ALTER TABLE `list_`.`contacts`
 ADD CONSTRAINT `contacts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
 ADD CONSTRAINT `contacts_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`);

Also, can someone help me with the ON DELETE and ON UPDATE. To help me figure out how I can delete all child rows that reference the `name` column in `groups`

Thanks!

Link to comment
Share on other sites

0 answers to this question

Recommended Posts

There have been no answers to this question yet

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.