I have two tables called "company" and "employees." The company table has a primary key attribute called "company name," and the employee table has a primary key attribute called "employee number" and a foreign key attribute, "company". I managed to link both tables, but the problem I seem to be having is the fact that when I try to add an employee to the employee table with a company name that is not stored in the company table, it allows me to add the detail of the employee to the employee table.
This should not happen if the company name does not exist in the company table. It should not allow the employee details to be imported to the employee table. I would very much appreciate if you could point me the right direction, and any help would be greatly appreciated.
It sounds to me like you are using a table handler other than InnoDB. By default, a CREATE TABLE statement will create a MyISAM type table. While MyISAM is a very good table handler with high performance and some excellent features, one thing it does not support is the enforcement of foreign keys. To enforce referential integrity with foreign keys, you will need to change you tables to the InnoDB table type. This is done by specifying TYPE=InnoDB at the end of the CREATE TABLE statement:
CREATE TABLE test ( id INT UNSIGNED NOT NULL PRIMARY KEY ) TYPE = InnoDB;As you can see the TYPE argument comes after the table definition. The full CREATE TABLE syntax can be found at http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html.
More information on the various table handlers can be found at http://dev.mysql.com/doc/mysql/en/Table_types.html. Please note that in the most recent versions TYPE has been superceded by HANDLER, as in HANDLER = InnoDB, but the TYPE keyword is still valid for the time being.
For more information on foreign key handling in MySQL see http://dev.mysql.com/doc/mysql/en/ANSI_diff_Foreign_Keys.html, and for more information on InnoDB see http://dev.mysql.com/doc/mysql/en/InnoDB.html.
This was first published in October 2004