8

I've looked at the MySQL 5.1 docs, and still can't figured this out -- that being I noticed a difference between the code I input into MySQL and output code by the system.

What is the difference between the code sample 01 and 02, meaning 02 has added CONSTRAINT before FOREIGN KEY -- why, and what's it do?

CODE_SAMPLE_01:

FOREIGN KEY (TABLE_02_nID_FK__TABLE_01_sID_PK) REFERENCES TABLE_01(TABLE_01_sID_PK),

CONTEXT:

CREATE TABLE `TABLE_02` (
  `TABLE_02_sID_PK` int(8) NOT NULL,
  `TABLE_02_nID_FK__TABLE_01_sID_PK` int(8) NOT NULL,
  `TABLE_02_cID` int(8) NOT NULL,
  `TABLE_02_data01` varchar(128) default NULL,
  `TABLE_02_data02` varchar(128) NOT NULL,
  `create_timestamp` DATETIME DEFAULT NULL,
  `update_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TABLE_02_sID_PK`),
  FOREIGN KEY (TABLE_02_nID_FK__TABLE_01_sID_PK) REFERENCES TABLE_01(TABLE_01_sID_PK),
  INDEX `TABLE_02_nID_FK__TABLE_01_sID_PK` (`TABLE_02_nID_FK__TABLE_01_sID_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CODE_SAMPLE_02:

CONSTRAINT `table_02_ibfk_1` FOREIGN KEY (`TABLE_02_nID_FK__TABLE_01_sID_PK`) REFERENCES `table_01` (`TABLE_01_sID_PK`)

CONTEXT:

CREATE TABLE `table_02` (
  `TABLE_02_sID_PK` int(8) NOT NULL,
  `TABLE_02_nID_FK__TABLE_01_sID_PK` int(8) NOT NULL,
  `TABLE_02_cID` int(8) NOT NULL,
  `TABLE_02_data01` varchar(128) DEFAULT NULL,
  `TABLE_02_data02` varchar(128) NOT NULL,
  `create_timestamp` datetime DEFAULT NULL,
  `update_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TABLE_02_sID_PK`),
  KEY `TABLE_02_nID_FK__TABLE_01_sID_PK` (`TABLE_02_nID_FK__TABLE_01_sID_PK`),
  CONSTRAINT `table_02_ibfk_1` FOREIGN KEY (`TABLE_02_nID_FK__TABLE_01_sID_PK`) REFERENCES `table_01` (`TABLE_01_sID_PK`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
blunders
  • 3,619
  • 10
  • 43
  • 65

1 Answers1

11

The optional CONSTRAINT keyword allows you to specify a name for the foreign key. Without it, a name will be generated automatically.

This name can be seen in the INFORMATION_SCHEMA TABLE_CONSTRAINTS table.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Thanks, nice clear answer in regards to what it is, though I have no idea what the INFORMATION_SCHEMA is; I look at the link you provided, and it's parent links in the breadcrumbs on MySQL. Is that a database for the database? Again, thanks! – blunders Dec 03 '10 at 22:52
  • 2
    @blunders: INFORMATION_SCHEMA is a virtual database in MySQL. `SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS` – Joe Stefanelli Dec 03 '10 at 22:56
  • Very cool, thanks -- your answers were a huge help! (+2, plus selected as the answer) – blunders Dec 04 '10 at 00:43
  • 1
    @blunders: +2? You must be a fellow Chicagoan voting twice like that! :-) – Joe Stefanelli Dec 04 '10 at 01:22
  • +1 one more... too funny, and Chicago's freaking cold, bundle up. – blunders Dec 04 '10 at 02:19