SQL - CONSTRAINT to link two tables

Question

I don't understand how to link two tables together. This is an example:

CREATE TABLE IF NOT EXISTS itemStatus (
  id int(11) AUTO_INCREMENT PRIMARY KEY,
  name varchar(64) NOT NULL UNIQUE KEY
);

CREATE TABLE IF NOT EXISTS itemData (
  id int(11) AUTO_INCREMENT PRIMARY KEY,
  title varchar(64) NOT NULL,
  status_id int(11) DEFAULT NULL,
  CONSTRAINT `fk_id` FOREIGN KEY (`id`) REFERENCES `itemStatus` (`id`),
  );

I'm calling the row "status_id" but I don't reference this anywhere, so it can't link the two. For this example, should should my "CONSTRAINT" line read in order to be correct?

score 1 · Answer 1 · answered Sep 07 '14 at 19:22

1

Looks like you're very close. Try this instead:

CONSTRAINT `fk_id` FOREIGN KEY (`status_id`) REFERENCES `itemStatus` (`id`)

answered Sep 07 '14 at 19:22

Cᴏʀʏ

105,112
20
162
194

Felipe M · Accepted Answer · 2014-09-07T19:30:51.447

1

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Take a look at: http://www.w3schools.com/sql/sql_foreignkey.asp

So it should be:

CREATE TABLE IF NOT EXISTS itemStatus (
  id int(11) AUTO_INCREMENT PRIMARY KEY,
  name varchar(64) NOT NULL UNIQUE KEY
);

CREATE TABLE IF NOT EXISTS itemData (
  id int(11) AUTO_INCREMENT PRIMARY KEY,
  title varchar(64) NOT NULL,
  status_id int(11) DEFAULT NULL,
  CONSTRAINT `fk_id` FOREIGN KEY (`status_id`) REFERENCES `itemStatus` (`id`)
);

FOREIGN KEY (status_id) => Field in the table will REFERENCES itemStatus (id)

Constraint can't have "," when it's the last:
(...) REFERENCES itemStatus (id),

So the structure should be:

CONSTRAINT <<CONSTRAINT_NAME>> FOREIGN KEY (<<COLUMN_IN_THE_TABLE>>) REFERENCES `<<ANOTHER_TABLE>>` (`<<ANOTHER_TABLE_COLUMN_ID>>`)

edited Sep 07 '14 at 19:30

answered Sep 07 '14 at 19:24

Felipe M

449
1
3
14

In my example I just have one CONSTRAINT with the value fk_id. However when I have two lines it tells me I can only use fk_id. What do I do in this case? – Jimmy Sep 07 '14 at 19:34
You're trying to name two Constraints with the same name (fk_id)? That's not possible. – Felipe M Sep 07 '14 at 19:36
Yes, so what is a sensible naming convention for each. Should it be fk_status_id? – Jimmy Sep 07 '14 at 19:37
**A Constraint name must be unique**. You can have 1000 Constraints on your table, but each of them must have one different name about the others. You don't have a rule to name them, just use a name that you'll find easy about know the connects. – Felipe M Sep 07 '14 at 19:39
1

Take a look about some links telling us about naming them! Maybe it will help you, @Jimmy http://stackoverflow.com/questions/2240929/what-is-a-proper-naming-convention-for-mysql-fks http://www.toadworld.com/platforms/mysql/w/wiki/6109.naming-foreign-key-constraints.aspx http://www.databasedesign-resource.com/constraint-naming-standard.html http://stackoverflow.com/questions/4836391/naming-convention-for-unique-constraint – Felipe M Sep 07 '14 at 19:42

score 1 · Answer 3 · answered Sep 07 '14 at 19:41

Your constraint is linking two primary keys (id of table 1 with id of table 2). It should be something like this:

CREATE TABLE IF NOT EXISTS itemData (
  id int(11) AUTO_INCREMENT PRIMARY KEY,
  title varchar(64) NOT NULL,
  status_id int(11) DEFAULT NULL,
  CONSTRAINT `fk_id` FOREIGN KEY (`status_id`) REFERENCES `itemStatus` (`id`)
  );

SQL - CONSTRAINT to link two tables

3 Answers3