MySQL vs Oracle was written in 2004 and may become outdated

What constraints are allowed?

Constraints are restrictions that are enforced on a table to prevent the database from loosing its integrity from incorrect data (REDHAT 2004). The are 3 types main types of constraints that will be compared. These are:

• Primary Key Constraints

• Unique Constraints

• Foreign Key Constraints

Primary Key Constraints

A primary key is used to uniquely define an item or row in a table and enables one item to be distinguished from another (REDHAT 2004). Both MySQL and Oracle fully support the use of primary key constraints to enforce the use of primary keys (MYSQL, 2004).

Unique Key Constraints

Unique key constraints are some what similar to a primary key constraint as the both enforce that all the items in a domain or column of a table are unique.

This is such a common constraint that both MYSQL and ORACLE support this.

Foreign Key Constraints

When a database has two or more tables, with one table containing details about the items referred to in another, a foreign key is used to join the two tables and protect referential integrity. A foreign key constraint can make sure that a table containing the details of an item, has the items details, before the item is referred to by another table. It can also prevent the deletion of the items details while the item is in other tables (REDHAT, 2004).

Out of all the types of tables that MySQL uses, only the InnoDB table type supports the use of a foreign key constraint (TUSC, 2004)(InnoDB, 2004 ). Oracle on the other hand, fully supports foreign key constraints (TUSC, 2004). It also supports cascade deleting where any fields that refer to the item that is being deleted are also deleted.

"serious application development would never consider using tables that don't support transactions or referential integrity" (TUSC, 2004)

There are so many situations that referential integrity is important and most of the MySQL table types lack in this area compared to Oracle.

Does it allow the storing of procedures and triggers?

By storing procedures on the server, less information needs to be sent between the server and the client, so processing can be faster and put less strain on the network. Oracle supports this but the storing of procedures and triggers is only supported by the newest version (version 5) of MySQL (MYSQL, 2004).