CODEWITHSUNDEEP
mysql
Jimmy
Jimmy

Reputation: 12497

Foreign ID in SQL tables to link one table to another

This is my SQL code which links users to items based on tutorials:

CREATE TABLE IF NOT EXISTS `users` (
 `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `user_password_hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `user_email` varchar(254) COLLATE utf8_unicode_ci NOT NULL,
 `user_access_level` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `user_active` tinyint(1) NOT NULL DEFAULT '0',
 `user_activation_hash` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `user_password_reset_hash` char(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `user_password_reset_timestamp` bigint(20) DEFAULT NULL,
 `user_failed_logins` tinyint(1) NOT NULL DEFAULT '0',
 `user_last_failed_login` int(10) DEFAULT NULL,
 `user_registration_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `user_registration_ip` varchar(39) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0.0.0.0',
 PRIMARY KEY (`user_id`),
 UNIQUE KEY `user_name` (`user_name`),
 UNIQUE KEY `user_email` (`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `item` (
 `item_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `item_title` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `item_location` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `item_description` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `item_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `item_status` int(1) unsigned NOT NULL,
 PRIMARY KEY (`item_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

http://sqlfiddle.com/#!9/fd011

I'm getting a bit confused about how to link the items to the user. It seems like I need something called a foreign key on the items, a bit like this in my item table:

FOREIGN KEY (user_id) REFERENCES user(ID)

I can't seem to get it to compile and query successfully. Can anyone please show me the right way to associate the items with the user.

Upvotes: 0

Views: 54

Answers (2)

DBug
DBug

Reputation: 2566

@GordonLindoff's solution is one method, but that assumes that each item belongs to exactly one user, and an item cannot be referenced by multiple users. If you have a many-to-many relationship, where a user can have multiple items and an item can be referenced by multiple users, then you need a third table that links them together:

CREATE TABLE IF NOT EXISTS `user_item` (
  `user_id` int(11) unsigned NOT NULL,
  `item_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`user_item`,`item_id`),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (item_id) REFERENCES items(item_id)
)ENGINE=Innodb DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The Foreign Key constraints enforce that for every row in user_item that the user_id exists in users, and the item_id exists in items. And as was mentioned in a previous comment, that you will need Innodb to have the foreign key constraints enforced.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270723

You need to add the user_id column to the items table along with the constraint:

CREATE TABLE IF NOT EXISTS `users` (
 `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `user_password_hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `user_email` varchar(254) COLLATE utf8_unicode_ci NOT NULL,
 `user_access_level` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `user_active` tinyint(1) NOT NULL DEFAULT '0',
 `user_activation_hash` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `user_password_reset_hash` char(40) COLLATE utf8_unicode_ci DEFAULT NULL,
 `user_password_reset_timestamp` bigint(20) DEFAULT NULL,
 `user_failed_logins` tinyint(1) NOT NULL DEFAULT '0',
 `user_last_failed_login` int(10) DEFAULT NULL,
 `user_registration_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `user_registration_ip` varchar(39) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0.0.0.0',
 PRIMARY KEY (`user_id`),
 UNIQUE KEY `user_name` (`user_name`),
 UNIQUE KEY `user_email` (`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE IF NOT EXISTS `item` (
 `item_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  user_id int unsigned,
 `item_title` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `item_location` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `item_description` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
 `item_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 `item_status` int(1) unsigned NOT NULL,
 PRIMARY KEY (`item_id`),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The SQL Fiddle is here.

I should point out a few other things:

  • Pay attention to the database engine you are using. MyISAM doesn't actually enforce the relationship.
  • Having weird dates as the default value is probably less useful than just using NULL.
  • I'm not sure if there is a value to having explicit collations for every character definition, unless your database is going to be supporting a wide variety of collations.
  • Don't use single quotes for numeric constants. So, if a value is declared as a tinyint, set the default ot 0 not '0' (this doesn't affect performance in a CREATE TABLE statement; it is just misleading).

Upvotes: 1

Related Questions