Database Tutorial

Course Tutorial Site

Site Admin

Lesson #2: Creating Tables

with 15 comments

This tutorial shows you how to create tables in Oracle, MySQL, and SQL Server (future version). While the general guidelines of SQL are the same, implementation drives differences. Like ordinary languages, these variations are known as dialects.

SQL dialects can be subtle or distinct. MySQL has made a conscious effort to stay very close to the ANSI SQL stantards. As of MySQL 5.1, the product is now ANSI SQL:92 compliant. Oracle also makes an effort to be compliant with the ANSI SQL standards. As of Oracle 11g, the product is ANSI SQL:2003 compliant. However, Oracle includes components that aren’t defined in the standard, like hierarchical queries. Microsoft SQL Server likewise tries to meet standards but also simultaneous supports a proprietary set of products. As a result, Microsoft delivers T-SQL (known as Transact-SQL, which is a distinct accent apart from the other two. Part of this distinct flavor is due to the legacy Sybase code, while others have a long history within Microsoft development approaches and products.

Database Constraints

This explains the five types of database constraints in relational databases. Click on the link above to see the details of the check, foreign key, not null, primary key, and unique constraints.

The balance of examples for creating, modifying, and dropping tables are found in the DDL web pages.

Written by michaelmclaughlin

September 22nd, 2009 at 11:24 pm

Posted in

15 Responses to 'Lesson #2: Creating Tables'

Subscribe to comments with RSS or TrackBack to 'Lesson #2: Creating Tables'.

  1. What exactly is meant by “Drops a structure from the database” as used by the DROP statement?

    Andrew Thimmig

    12 Jan 10 at 12:29 pm

  2. I’ve modified it above. Do you find the explanation clearer?

    michaelmclaughlin

    12 Jan 10 at 12:43 pm

  3. If you receive this error:

    ERROR 1005 (HY000): Can’t create table ‘sampledb.tweetie_bird’ (errno: 150)

    use this command to disable foreign key checks:

    SET foreign_key_checks = 0

    Found in:
    http://www.phwinfo.com/forum/comp-databases-mysql/344594-error-1005-hy000-line-1-cant-create-table.html
    post#2

    Dan Ricks

    12 Jan 10 at 3:27 pm

  4. Dan, this is already covered here in the MySQL re-runnable script section.

    michaelmclaughlin

    13 Jan 10 at 12:29 am

  5. in the second paragraph the link says “stantards” should say “standards”.

    Marcus Norton

    4 Mar 10 at 2:13 pm

  6. Brother McLaughlin, a number of us found the short paragraph describing the purpose of the ‘set foreign_key_checks=0’ command really confusing. Can you please rephrase it?

    Kara

    3 May 10 at 1:00 pm

  7. I’ve been reading this paragraph from the MySQL section and suggest the following change:

    Sometimes you don’t want to allow your scripts to ignore the referential integrity rules between primary and foreign keys. This means you can’t simply issue the following command to the InnoDB engine to ignore the constraints:

    Referential Integrity rules in a database preserve primary and foreign key relationships between tables. Sometimes we need to ignore these rules to perform actions that usually would not be allowed, for example dropping two tables that have mutual references in their primary and foreign keys.

    However this behavior should be used sparingly, as it can compromise the integrity of the database. The following command will tell the engine to ignore Referential integrity rules:

    mysql> SET FOREIGN_KEY_CHECKS = 0;

    You should remember to re-enforce these rules after performing the required commands, by using the following.

    mysql> SET FOREIGN_KEY_CHECKS = 1;

    Daniel

    4 May 10 at 5:22 pm

  8. I took some liberty with your remarks but adopted all of it. Thanks!

    michaelmclaughlin

    21 Jun 10 at 10:23 pm

  9. I’ve adopted a change suggested by Daniel.

    michaelmclaughlin

    21 Jun 10 at 10:24 pm

  10. Just an FYI, the comment link goes directly back to this tutorial. Is there an example code of the comment command?

    Doug Kelley

    11 Jan 11 at 8:41 pm

  11. Just curious on the BOOLEAN type for MySQL. Your book states there is no Boolean data type in MySQL. This link states that BOOL or BOOLEAN are synonymous with TINYBIT(1)…so does this mean there are BOOLEAN data types for MySQL? http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

    Dean

    6 Oct 12 at 2:09 pm

  12. The link under the DDL list for COMMENT leads back to this same page.

    Brennan T

    17 Jan 14 at 8:37 pm

  13. Brennan T, Thanks for the note, it was missing the URL in the href attribute, it’s fixed. Michael

    michaelmclaughlin

    18 Mar 14 at 11:06 am

  14. Dean, As stated in the web page you cite, the BOOL and BOOLEAN data types are synonyms for the TINYINT data type.

    michaelmclaughlin

    22 Oct 14 at 1:15 am

  15. Doug, I added this COMMENT Statement web page as an outgrowth of your request.

    michaelmclaughlin

    22 Oct 14 at 2:01 am

Leave a Reply