Schema maintenance scripts

As you are developing a database schema, there may be occasions on which you have to change your mind about a table or column definition. For example, you may have underestimated the column size for a primary key field. In that case, you would need to update the table definition - possibly using an ALTER TABLE statement. However, if that column is referenced in a foreign key in another table, you will need to update that column definition as well. Similarly, if you decide to change the name of a column, you will need to change it wherever it is referenced. One change may have consequences like this which can cause errors to creep into your structures.

Rather than making each change individually and directly on the database, keeping a database maintenance script allows you to make changes quickly and easily. A maintenance script is a series of SQL statements in a text file which recreates the database every time it is run. First it removes any existing database objects (see below), and then it goes on to create the required objects and finally to insert the data. Having all of your maintenance statements in a single file means that you can make changes easily by using the standard global search and replace functions of a text editor.

The code below shows a simple example of a schema maintenance script.

-- Drop tables if they currently exist
-- in the correct order. Tables with
-- foreign keys must be dropped before
-- the tables they refer to.

DROP TABLE IF EXISTS order_line;     
DROP TABLE IF EXISTS `order`;         
DROP TABLE IF EXISTS cust;           
DROP TABLE IF EXISTS product;        

-- Create the tables in the correct order
-- Tables with foreign keys must be created
-- after the tables they refer to.

CREATE TABLE cust (                  
  id varchar(5) NOT NULL,            
  name varchar(10) DEFAULT NULL,     
  addr varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE product (
  id INTEGER NOT NULL,
  description varchar(25) DEFAULT NULL,
  price decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE `order` (
  id INTEGER NOT NULL AUTO_INCREMENT,
  cust varchar(5) NOT NULL,
  sdate date NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (cust) REFERENCES cust(id)
);

CREATE TABLE order_line (
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id,product_id),
  FOREIGN KEY (order_id) REFERENCES `order`(id),
  FOREIGN KEY (product_id) REFERENCES product(id)
);

-- Insert the data in the correct order
INSERT INTO product VALUES ('10','Widgit',10.00);   
INSERT INTO product VALUES ('20','Gadget',15.00);
INSERT INTO cust VALUES ('1','Jones','2 George Street');
INSERT INTO cust VALUES ('2','MacDonald','3 Queen Street');
INSERT INTO cust VALUES ('3','Smith','1 High Street');
INSERT INTO `order` VALUES (1,'3','2017-09-26');
INSERT INTO `order` VALUES (2,'1','2017-09-26');
INSERT INTO `order` VALUES (3,'2','2017-09-26');
INSERT INTO `order` VALUES (4,'1','2017-09-26');
INSERT INTO order_line VALUES (1,10,1);
INSERT INTO order_line VALUES (2,10,1);
INSERT INTO order_line VALUES (3,20,1);
INSERT INTO order_line VALUES (4,20,1);