Lesson Obsolete
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.
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.
Data Definition Language (DDL) ↓
The Data Definition Language (DDL) subsystem provides you with the ability to create, alter, drop, rename, and truncate structures in a database. Structures may be tables or other types of objects, where those other types of objects are the subject of product variation and often drivers of SQL dialect nuances. The supporting pages in this lesson support DDL commands, which you can see by clicking the drop down label above.
- CREATE – Creates an object, which can be a table, view, or other structure supported by the DBMS implementation.
- ALTER – Alters the structure of an object, which can be a table, view, or other structure supported by the DBMS implementation.
- DROP – Drops a structure (e.g., a table, view, sequence, et cetera) from the database, which means that the definition of the table is removed from the database catalog (or metadata). The drop may be constrained when there are other objects that depend on an object. For example, in Oracle you append the
CASCADE CONSTRAINTS
clause to instruct the database to also remove any dependencies on the object before dropping a structure.
- RENAME – Renames a structure, which can be a table, view, or subcomponent like a column name or data type of a table.
- TRUNCATE – Truncate deletes data like the
DELETE
statement with a twist. It preserves the structure of the table but deletes the contents without logging the deletes for recovery. Implementations vary between products, and Oracle’s flashback technology does provide for recovery of truncated objects.
- COMMENT – Lets you add a comment to a table or a column in a table. It is very handy for labeling foreign key columns that aren’t constrained by a database constraint because developers can then check the valid list of values. It is also very handy for letting you qualify the list of acceptable values imposed by a
CHECK
constraint.
Re-runnable Referential Integrity Scripts ↓
A re-runnable script is one that anticipates problems and performs conditionally actions. For example, when a TABLE
doesn’t exist, it shouldn’t attempt to drop it because that raises an exception that distracts and potentially misleads those running it to think there’s a meaningfull error. Referential integrity describes the unique relationship between primary and foreign keys. You must drop FOREIGN KEY
constraints before you drop the table that holds their referenced PRIMARY KEY
when you implement these database level constraints.
The values in rows of a PRIMARY KEY
are the list of valid values that may be entered in a FOREIGN KEY
. This relationship makes the FOREIGN KEY
dependent on the PRIMARY KEY
. The deletion of rows from the table holding the PRIMARY KEY
abandons the FOREIGN KEY
of any dependent rows, which are often called orphaned keys rather than abandoned keys because of the parent-child analogy surrounding referential integrity.
The following drop down segments show you how to write re-runnable scripts with referential integrity restrictions on primary and foreign keys (just click the preceding title).
Oracle ↓
This script creates a small referential integrity model with GRANDMA
and TWEETIE_BIRD
tables. Each row in the TWEETIE_BIRD
table is wholly dependent on a row in the GRANDMA
table.
Writing re-runnable scripts differs for each database management system because of their implementation choices. Oracle’s SQL*Plus environment is an interactive and batch SQL statement processor and a reporting environment. This means it includes a number of nice features for organizing and displaying content. The ECHO
environment variable is very useful for displaying statements as they’re processed.
Unfortunately, Oracle’s SQL dialect doesn’t support a conditional drop statement. You must write PL/SQL to perform a conditional evaluation. Likewise, you write a database trigger to mimic automatic numbering found in MySQL and SQL Server. You must also manually define sequence values that you may use when populating tables.
We can liken the Oracle approach to a manual transmission and the others to an automatic transmission. In a day-to-day situation an automatic transmission makes most peoples lives easier, but in a performance racing situation the automatic transmission may likely cause us to lose the race. Oracle is configurable at this level to provide scalability and performance optimization.
The following re-runnable script shows you how to implement these components when creating tables that enforce referential integrity through database-level constraints. The alternative to using database-level constraints would be to build the referential integrity into the application software, which is often done to make solutions more scalable by reducing the load on the database server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | -- Enable echoing all SQL commands. SET ECHO ON -- Conditionally drop a table and sequence. BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('GRANDMA','GRANDMA_SEQ')) LOOP IF i.object_type = 'TABLE' THEN -- Use the cascade constraints to drop the dependent constraint. EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / -- Create the table. CREATE TABLE GRANDMA ( grandma_id NUMBER CONSTRAINT grandma_nn1 NOT NULL , grandma_house VARCHAR2(30) CONSTRAINT grandma_nn2 NOT NULL , CONSTRAINT grandma_pk PRIMARY KEY (grandma_id) ); -- Create the sequence. CREATE SEQUENCE grandma_seq; -- Create a trigger to mimic automatic numbering. CREATE OR REPLACE TRIGGER BI_GRANDMA BEFORE INSERT ON GRANDMA FOR EACH ROW WHEN (NEW.grandma_id IS NULL) -- Only fire when no ID value exists. BEGIN SELECT grandma_seq.nextval INTO :NEW.GRANDMA_ID FROM dual; END; / -- Conditionally drop a table and sequence. BEGIN FOR i IN (SELECT object_name , object_type FROM user_objects WHERE object_name IN ('TWEETIE_BIRD','TWEETIE_BIRD_SEQ')) LOOP IF i.object_type = 'TABLE' THEN EXECUTE IMMEDIATE 'DROP TABLE '||i.object_name||' CASCADE CONSTRAINTS'; ELSE EXECUTE IMMEDIATE 'DROP SEQUENCE '||i.object_name; END IF; END LOOP; END; / -- Create the table with primary and foreign key out-of-line constraints. CREATE TABLE TWEETIE_BIRD ( tweetie_bird_id NUMBER CONSTRAINT tweetie_bird_nn1 NOT NULL , tweetie_bird_house VARCHAR2(30) CONSTRAINT tweetie_bird_nn2 NOT NULL , grandma_id NUMBER CONSTRAINT tweetie_bird_nn3 NOT NULL , CONSTRAINT tweetie_bird_pk PRIMARY KEY (tweetie_bird_id) , CONSTRAINT tweetie_bird_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (GRANDMA_ID) ); -- Create sequence. CREATE SEQUENCE tweetie_bird_seq; -- Create trigger to mimic automatic numbering. CREATE OR REPLACE TRIGGER BI_TWEETIE_BIRD BEFORE INSERT ON TWEETIE_BIRD FOR EACH ROW WHEN (NEW.tweetie_bird_id IS NULL) -- Only fire when no ID value exists. BEGIN SELECT tweetie_bird_seq.nextval INTO :NEW.tweetie_bird_id FROM dual; END; / |
MySQL ↓
This script creates a small referential integrity model with GRANDMA
and TWEETIE_BIRD
tables. Each row in the TWEETIE_BIRD
table is wholly dependent on a row in the GRANDMA
table.
Writing re-runnable scripts differs between database management system. The differences exists because of their implementation choices. MySQL is an interactive and batch SQL statement processor and that’s it. There aren’t any nice reporting features like those provided in the Oracle SQL*Plus interface.
The limitations of the MySQL interface means that you may need to leverage SQL queries to provide log files with meaningful descriptions before command implementation. MySQL also supports various database engines and as a result of that has different behaviors related to each engine. The options related to dropping tables with dependents is most likely an outgrowth of that engine flexibility.
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 InnoDB 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; |
Another trick relies on knowing that foreign keys depend on a primary key. If you drop the table without any dependents first and work your way up the inverted dependency tree, you can drop all tables like this:
mysql> DROP TABLE IF EXISTS tweetie_bird, grandma; |
The only problem with this method is that it fails when two tables have mutual dependencies. A mutual dependency relation involves one table that holds a primary key that the other references as a foreign key while also holding a foreign key column that references that other table.
BEST PRACTICE:
You must specifically drop foreign key constraints before you drop the table that contains them. The syntax for that follows below in the sample code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | -- Drop Constraint. SELECT 'GRANDMA_FK' AS "DROP FOREIGN KEY"; ALTER TABLE tweetie_bird DROP FOREIGN KEY grandma_fk; -- Conditionally drop a table without dependents. SELECT 'GRANDMA' AS "DROP TABLE"; DROP TABLE IF EXISTS grandma; -- Create table. SELECT 'GRANDMA' AS "CREATE TABLE"; CREATE TABLE grandma ( grandma_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , grandma_house VARCHAR(30) NOT NULL) ENGINE = innodb; -- Conditionally drop a table without dependents. SELECT 'TWEETIE_BIRD' AS "DROP TABLE"; DROP TABLE IF EXISTS tweetie_bird; -- Create table. SELECT 'TWEETIE_BIRD' AS "CREATE TABLE"; CREATE TABLE tweetie_bird ( tweetie_bird_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , tweetie_bird_house VARCHAR(30) NOT NULL , grandma_id INT UNSIGNED NOT NULL , CONSTRAINT grandma_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (grandma_id)) ENGINE= innodb; |
There is also a longer syntax option that you should know. It involves a separate line that defines the name of a KEY
column before the constraint. You can see an example of this on line 25 below. It really adds little to the clarity of the code, but you should know it because you may find it in production scripts.
21 22 23 24 25 26 27 | CREATE TABLE tweetie_bird ( tweetie_bird_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , tweetie_bird_house VARCHAR(30) NOT NULL , grandma_id INT UNSIGNED NOT NULL , KEY grandma_fk (grandma_id) , CONSTRAINT grandma_fk FOREIGN KEY (grandma_id) REFERENCES GRANDMA (grandma_id)) ENGINE= innodb; |
The balance of examples for creating, modifying, and dropping tables are found in the DDL web pages.