In MySQL, with FKs what's "CONSTRAINT" do?

Question

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;

Maestoso

Answer 1

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.

Discordancy

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! – Maestoso

@blunders: INFORMATION_SCHEMA is a virtual database in MySQL. SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS – Discordancy

Very cool, thanks -- your answers were a huge help! (+2, plus selected as the answer) – Maestoso

@blunders: +2? You must be a fellow Chicagoan voting twice like that! :-) – Discordancy

+1 one more... too funny, and Chicago's freaking cold, bundle up. – Maestoso