TDDD37 Database Technology

Tips for the labs

• If you get syntax error, first check the error message which normally points to the place of the error. The message will say something like "error around ...".
• If it is still difficult to find the error, one way is to start from a simple statement, and then add more and more operations. It is not easy to write a perfect complicated statement at once. For instance, when creating a table, first just create a table with some attributes. If this works, then drop the table and create it now again with the attributes and also the primary key. When this works, drop the table and add the foreign keys. Another possibility is to create the tabel with some attributes. When it works, use ALTER table to add the primary key, and finally when this works, use ALTER table to add the foreign keys.
• Finding errors is not so easy, so it may take a quite while to figure out some errors. This is very common in programming. (It happens quite often in our everyday life :-|).
• MySQL does not have the exact same syntax as the Standard SQL on the book. Check Differences between SQL in the book and MySQL and MySQL Statement Syntax

• Always create InnoDB tables. To create a InnoDB table, you just have to add "engine=InnoDB" after the usual create table statement. That is, CREATE TABLE customers (a INT, b CHAR (20)) ENGINE=InnoDB; If you do not explicitly create your tables as InnoDB, they will be created as MyISAM by default and you may not be able to create foreign keys. To read more about the different storage engines in mysql, visit this page.
• Error messages when you create a table, primay key or foreign key. If errors are not syntax errors , e.g. ERROR 1005: Can't create table './test/fkey_exmpl.frm' (errno: 150), there is a high possibility that the error is because your statement breaks a key constraint, entity integrity constraint or referential integrity constraint . Understand the constraints!
• Foreign key definitions in MySQL also need be subject to the following conditions:
  • Both tables must be InnoDB tables and they must not be TEMPORARY tables. (see examples in the FAQ - company schema.)
  • Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same.
  • If the CONSTRAINT symbol clause is given (giving a name to the constraint), the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

Page responsible: Fang Wei-Kleiner
Last updated: 2010-08-19