MySQL list all foreign key and other constraint names

SELECT  TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, 
        REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME

FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE

WHERE   REFERENCED_TABLE_SCHEMA = 'database_name'

How to Set up a Foreign Key Constraint in MySQL

The following is a simple example that illustrates Foreign Key constraints:

Creating a table for manager:
CREATE TABLE manager (id integer primary key auto_increment, name text) ENGINE=InnoDB;

Creating a table for club and creating a foreign key constraint for manager_id with key 'manager_id_reference':
CREATE TABLE club (id integer primary key auto_increment, is_accessible char, manager_id integer NOT NULL) ENGINE=InnoDB;
ALTER TABLE club ADD CONSTRAINT manager_id_reference FOREIGN KEY(manager_id) REFERENCES manager (id);

Inserting some managers:
INSERT INTO manager(name) VALUES('Pritom');
INSERT INTO manager(name) VALUES('Kumar');

Inserting some clubs:
INSERT INTO club(is_accessible, manager_id) VALUES(true, 1);
INSERT INTO club(is_accessible, manager_id) VALUES(false, 2);

Let see manager table data (SELECT * FROM manager):

ID	NAME
1	Pritom
2	Kumar

Let see club table data (SELECT * FROM club):

ID	IS_ACCESSIBLE	MANAGER_ID
1	1	1
2	0	2

Let see manager table structure (DESCRIBE manager):

FIELD	TYPE	NULL	KEY	DEFAULT	EXTRA
id	int(11)	NO	PRI	(null)	auto_increment
name	text	YES		(null)

Let see club table structure (DESCRIBE club):

FIELD	TYPE	NULL	KEY	DEFAULT	EXTRA
id	int(11)	NO	PRI	(null)	auto_increment
is_accessible	char(1)	YES		(null)
manager_id	int(11)	NO	MUL	(null)

Now try to delete manager:
DELETE FROM USER WHERE id = 1;

will error as:
Schema Creation Failed: Cannot delete or update a parent row: a foreign key constraint fails (`db_2_8d4a1`.`club`, CONSTRAINT `manager_id_reference` FOREIGN KEY (`manager_id`) REFERENCES `manager` (`id`)):

This happens because the data in the manager table depends on the data in the club table.

Full sqlFiddle