MYSQL reference table from another table

Question

I'm new to SQL. As an example is my main table:

CREATE TABLE IF NOT EXISTS `main`.`item` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'auto incrementing item_id of each job, unique index',
  `item_title` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'item title, not-unique',
  `item_status`
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='item data';

And my reference table:

CREATE TABLE IF NOT EXISTS `main`.`status` (
  `status_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'auto incrementing status_id of each status, unique index',
  `status_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL COMMENT 'status name, unique',
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='status';

How can I reference the main table to an ID of the status table, so there is a status associated with the main item.

Also since this is my first DB, if I have made any other mistakes please feel free to highlight them.

score 2 · Answer 1 · answered Sep 05 '14 at 18:31

2

Your main table should have a status_id column. That column should be designated as a foreign key, and linked to the primary key of the status table.

This will make the item_status column of your main table unnecessary. Use the status_id value from the main table to look up the primary key of the status table on your front end.

answered Sep 05 '14 at 18:31

Mandu

157
1
10

Thank you for the reply. So should I do something like this: ````FOREIGN KEY (status_id) REFERENCES status(id) – J.Zil Sep 05 '14 at 19:16

score 0 · Answer 2 · answered Sep 05 '14 at 18:29

0

Simple:

SELECT * FROM main.item, main.status 
           where main.item.item_status = main.status.status_id

This will join all rows form item with all rows from status where there's a matching id. It will not give you any rows where there's an item_status which is unknown in table status!

You should only keep the last creation of your table item_status.

answered Sep 05 '14 at 18:29

Axel Amthor

10,980
1
25
44

2

OP should also have a look at https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html – a coder Sep 05 '14 at 18:31
Rather use joins than where syntax: http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause – Ke Vin Sep 05 '14 at 18:35

MYSQL reference table from another table

2 Answers2