2
CREATE TABLE accounts (
 account_name      VARCHAR(100) NOT NULL PRIMARY KEY
);

CREATE TABLE products (
 product_id        INTEGER NOT NULL PRIMARY KEY,
 product_name      VARCHAR(100)
);

CREATE TABLE bugs (
  bug_id            INTEGER NOT NULL PRIMARY KEY,
  bug_description   VARCHAR(100),
  bug_status        VARCHAR(20),
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  assigned_to       VARCHAR(100) REFERENCES accounts(account_name),
  verified_by       VARCHAR(100) REFERENCES accounts(account_name)
 );

CREATE TABLE bugs_products (
  bug_id            INTEGER NOT NULL REFERENCES bugs,
  product_id        INTEGER NOT NULL REFERENCES products,
  PRIMARY KEY       (bug_id, product_id)
);

if i execute 'describe bugs_products' i get:

 Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| bug_id     | int(11) | NO   | PRI | NULL    |       | 
| product_id | int(11) | NO   | PRI | NULL    |       | 
+------------+---------+------+-----+---------+-------+

how can i also get references information?

ufk
  • 30,912
  • 70
  • 235
  • 386

1 Answers1

6

On testing, the foreign keys are not created on my machine using this syntax:

CREATE TABLE bugs (
  ...
  reported_by       VARCHAR(100) REFERENCES accounts(account_name),
  ...
 ) ENGINE = INNODB;

But they are when I use this create statement:

CREATE TABLE bugs (
  ...
  reported_by       VARCHAR(100),
  ...
  FOREIGN KEY (reported_by) REFERENCES accounts(account_name)
 ) ENGINE = INNODB;

An easy way to see if foreign keys exist on a table is:

show create table bugs_products

Or you can query the information schema:

select
  table_schema
, table_name
, column_name
, referenced_table_schema
, referenced_table_name
, referenced_column_name
from information_schema.KEY_COLUMN_USAGE
where table_name = 'bugs'

Also check you're using the InnoDB storage engine. The MyISAM engine does not support foreign keys. You can find the engine like:

select table_schema, table_name, engine
from information_schema.TABLES
where table_name = 'bugs'

If you try to create a foreign key on a MyISAM table, it will silently discard the references and pretend to succeed.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • @ufk: Check if you're using the MyISAM storage engine; it does not support foreign keys – Andomar Dec 12 '09 at 12:18
  • innodb is enabled. (i don't have skip-innodb option enabled and i have several innodb configuration directives) – ufk Dec 12 '09 at 12:23
  • @ufk: It creates foreign keys for me only if I put them on a separate line in the create table. Does that work for you too? Example added at end of answer – Andomar Dec 12 '09 at 12:31
  • mysqldump doesn't show references as well. it's a db configuration problem probably. – ufk Dec 12 '09 at 12:32
  • ok since you marked my other question as duplicate, I'll ask it here. it seems that the tables are not created with references. i already showed here how i'm creating the tables. so what am i doing wrong ? in the following marked as duplicated question i show examples of what i'm trying to do and what's the result http://stackoverflow.com/questions/1893338/mysql-innodb-5-0-84-creating-column-references – ufk Dec 12 '09 at 15:19
  • @ufk: I'd say MySQL was wrong here. They should either give an error or create a foreign key. But know that we know that, just use the syntax that works? – Andomar Dec 12 '09 at 15:25
  • @ufk: The second snippet should work, with each foreign key on its own line, separated from the columns by a comma. Like `reported_by varchar(100), FOREIGN KEY (reported_by) REFERENCES accounts(account_name), assigned_to varchar(100), FOREIGN ...` – Andomar Dec 12 '09 at 15:52