Database Tutorial

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;
/

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;