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.
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) );
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;
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.
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;
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.
Let's clean potential data from the last table we should still have at this point.
--clean data from a table TRUNCATE TABLE project;
Read next: Operate