Foreign Key Constraints not applied?

[0xCMD]

Greetings,

I have table CUSTOMER:

mysql> describe CUSTOMER;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| CUSTOMER_NUM  | char(3)      | NO   | PRI | NULL    |       |
| CUSTOMER_NAME | char(35)     | NO   |     | NULL    |       |
| STREET        | char(15)     | YES  |     | NULL    |       |
| CITY          | char(15)     | YES  |     | NULL    |       |
| STATE         | char(2)      | YES  |     | NULL    |       |
| ZIP           | char(5)      | YES  |     | NULL    |       |
| BALANCE       | decimal(8,2) | YES  | MUL | NULL    |       |
| CREDIT_LIMIT  | decimal(8,2) | YES  | MUL | NULL    |       |
| REP_NUM       | char(2)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

and table ORDERS;

mysql> describe ORDERS;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| ORDER_NUM    | char(5) | NO   | PRI | NULL    |       |
| ORDER_DATE   | date    | YES  |     | NULL    |       |
| CUSTOMER_NUM | char(3) | YES  | MUL | NULL    |       |
+--------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

In TableCUSTOMER, column CUSTOMER_NUM is the Primary Key
In Table ORDERS there's also a CUSTOMER_NUM column I attempted to add an integrity constraint (A Foreign Key) to the CUSTOMER_NUM column in the ORDERS table as follows:

mysql> alter table ORDERS
    -> add foreign key (CUSTOMER_NUM) references CUSTOMER;
Query OK, 7 rows affected (0.11 sec)
Records: 7  Duplicates: 0  Warnings: 0

The command looks like it worked fine but when I intentionally try to violate the integrity constraint of the foreign key as follows (by adding a row that contains a CUSTOMER_NUM '850' that is not contained in the CUSTOMER_NUM column in the CUSTOMER table):

mysql> insert into ORDERS
    -> values
    -> ('21625', '2007-10-23', '850');
Query OK, 1 row affected (0.00 sec)

The DBMS does not complain and it let me add that row even though it violates the foreign key constraint, WHY?

There is no CUSTOMER_NUM = '850' in the CUSTOMER table so this should have gave an error similar to what I received in Oracle: ORA-02291:integrity constarint ... violated - parent key not found.

[hielo]

Make sure your tables are using InnoDB Storage engine and then try:

ALTER TABLE `ORDERS` ADD FOREIGN KEY(`CUSTOMER_NUM`) REFERENCES `CUSTOMER`(`CUSTOMER_NUM`);

[0xCMD]

Make sure your tables are using InnoDB Storage engine and then try:

ALTER TABLE `ORDERS` ADD FOREIGN KEY(`CUSTOMER_NUM`) REFERENCES `CUSTOMER`(`CUSTOMER_NUM`);

I thought Innodb is enabled by default?Starting with version 3.23 (first released in May 12, 2001), binary and source downloads of MySQL contain InnoDB.

According to this, it says it is enabled, but still the foreign key constraint is not enforced.

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

[hielo]

When you create a table, you need to specify the engine - ex:

CREATE TABLE `dbName`.`tableName` (
 ...
)
ENGINE = INNODB
CHARACTER SET utf8 COLLATE utf8_general_ci;

By default, it is MyIsam. As a matter of fact, line 7 of your post CLEARLY shows that MyISAM IS the default engine on your db server.

Your post simply confirms that your DB "supports" InnoDB IF you choose to use it on your tables, but you have to specify which engine to use on the tables. If you don't it will assume you meant MyISAM, which does NOT support constraints.

All the tables involved in the relationship constraints will need the InnoDB storage engine.

[0xCMD]

That solved the issue, thank you for all the help.
I was wondering, what would be better: setting InnoDB as the default engine or keep the default MyISAM and only set those tables that involve relationships to InnoDB?