DB Build

index<--

DB Build

Building a database is an implementation phase that follow after design. The result of this activity is to make the database creation script. This script will create the database structure that has tables, views, stored procedures and initial data. We use DDL for this script.

DDL: CREATE

Remember our first article? We have already show and example for DDL CREATE command. This command can build almost any object type that a database server has suport for. That include data storage: Tables and support objects: Views, Indexes and Constraints.

Advice: You must create database objects in a specific order. If the order is not logical but random, you will not be able to understand and maintain the scripts. You must start with tables. First the main tables then the detail tables and last the associative tables.

Example:

To make all sense we will start with creation of main tables for Agile project we have just designed in our previous page.

-- build script for main tables
CREATE TABLE project (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    owner_id INT DEFAULT NULL,
    name VARCHAR(20),
    description VARCHAR(2000),
    language VARCHAR(100),
    INDEX project_owner_ind (owner_id)
);

CREATE TABLE developer (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    team_id INT NOT NULL,
    name VARCHAR(50),
    skill VARCHAR(200),
    INDEX dev_team_id (id)
);

CREATE TABLE team (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    master_id INT DEFAULT NULL,
    project_id INT DEFAULT NULL,
    name VARCHAR(30),
    INDEX team_master_id (master_id),
    INDEX team_project_id (project_id),
    FOREIGN KEY (master_id)  REFERENCES developer(id),
    FOREIGN KEY (project_id) REFERENCES project(id)
);

DDL: ALTER

In previous example we have created 3 tables but we are able to create only two foreign keys for table team. Now we can add inderdependent foreign keys to tables: developer and project.. These foreign keys will be created using ALTER command. Let's do it:

-- build script for interdependent objects
ALTER TABLE project
    ADD CONSTRAINT fk_project_owner, 
    FOREIGN KEY (owner_id), 
    REFERENCES developer(id),
    ON DELETE SET NULL;
    
ALTER TABLE developer
    ADD CONSTRAINT fk_developer_project,
    FOREIGN KEY (project_id), 
    REFERENCES project(id),
    ON DELETE SET NULL;

DDL: DROP

You can use DROP command to remove an object from database. If the object is a table, all data stored in that table will be removed. Removing a table has other consequences. The dependent objects will either be removed or will become invalid.

Some objects can be safely removed and re-created. For example if we drop a view, data is not lost. Though some dependent objects can become invalid. After we re-create the view we can recompile the invalid objects.

Example:

Let's remove some of the objects we have created previously.

-- cleanup script
DROP CONSTRAINT fk_project_owner;
DROP CONSTRAINT fk_developer_project;

DROP TABLE team;
DROP TABLE developer;

DDL: TRUNCATE

TRUNCATE command empties a table completely. It requires the DROP privilege. This statement is high performance. It bypass anu constrain and do not fire triggers. A similar effect you obtain by dropping and recreating a table except that dependent objects do not become invalid.

Example:

Let's clean potential data from the last table we should still have at this point.

--clean data from a table
TRUNCATE TABLE project;

Caution: DML commands are dangerous actions that can not be roll-back easly. If you make a mistake you can loose valuable business data. Therefore before you run any DML script make sure you save (export) your data. You should know that in production only administrators are authorized to run DML commands.

Read next: Operate