Chapter 9 – SQL CREATE, DROP, and ALTER
Section I – Usage
When to Use
So far we’ve spent our time discovering how SQL can be used to aggregate, sort, and manipulate data for analysis and presentation purposes. But everything we’ve done is under the assumption the data was already available. In this lesson we’ll learn about the other part of SQL, architecture!
SQL Architecture consists of creating, deleting (dropping), and altering tables, views, and indexes. While most DBMSs will come with an out-of-the-box SQL administration tool (such as SQL Server Management Studio), SAS does not. SAS does allow you to create a table manually using the GUI, however it is slow and cumbersome, so we’ll focus on the actual SQL syntax.
Items
Before we get into the syntax of SQL Architecture, let’s discuss the difference between a table, view, and index. As mentioned in Chapter 2, a table is a SAS dataset that consists of columns and rows. A table is a permanent collection of organized data.
A View is very similar to a table, except it does not contain permanent data. A view is a virtual table and consists of nothing more than a permanent query. Each time you open a View the View’s query will run and present the results in a table format. Views are best utilized when dealing with a normalized database. It’s easier to create output tables for users rather than require them to learn the complex structure of the database.
An index is used to sort data in a more logical manner than how it is initially entered into a table. This is done to speed up the processing involved in SELECT statements. The best way to think of how an index works is to envision an index in the back of a book. Querying a table using a WHERE statement, if the table is indexed on that column, will result in SAS reading the index instead of the table and will find the rows directly. A good index can increase the speed by dozens of multiples.
Section II – Our Sample Database
In the following samples, we’ll re-create the database our examples were created in. Since we’re starting from scratch, make sure you create a folder called C:\Temp\Chapt09\. Before you begin, assign your librefs and set your options.
Option Source Notes Source2 MPRINT XWait;
Libname Chapt09 “C:\Temp\Chapt09\”;
CREATE Tables
Let’s start out with a basic CREATE table statement. We’ll create the WeatherStations table and include a handful of basic constraints.
Proc SQL;
Create Table Chapt09.Obj_WeatherStations
(
ID char(3) format=$3. ,
City varchar(50) format=$50. ,
State char(2) format=$3. ,
Constraint pk_ID Primary Key(ID) ,
Constraint nn_City Not Null(City) ,
Constraint nn_State Not Null(State)
);
Quit;
When you look at the above statement, we’ve created a table with three (3) columns (ID, City, and State) and three (3) constraints. The first constraint is called a Primary Key. A Primary Key is a unique version of an index. It is unique in a sense that every value in the ID column must be….unique! The Primary Key also works as an index, so every value in the ID column will be sorted.
The second constraint is called a Not Null constraint. What this basically means is, for a row to be entered and saved in the table the values in Columns City and State cannot have a null (blank) value. In future examples, we’ll see other types of constraints, but for now, the basics!
Now, let’s create an index for this table.
Proc SQL;
Create Index City On Chapt09.Obj_Weatherstations(City);
Quit;
In the previous statement, we created an index called City on the table Obj_Weatherstations on the column City. When building an index on a table that only utilizes a single column, the index name MUST be the same as the column name.
Now, if you wanted to delete both the table and the index, you would need to use a DROP statement. A DROP statement will work the same for indexes, views, and tables, but not constraints (we’ll see this later).
Proc SQL;
Drop Index Name On Chapt09.Obj_Weatherstations;
Drop Table Chapt09.OBJ_WeatherStations;
Quit;
Now, let’s get a little more advanced! In this example, we’re going to add two new types of constraints, including one that will throw you for a loop come DROP Table time!
Proc SQL;
Create Table Chapt09.Obj_Recorders
(
ID varchar(14) format=$14. ,
Premise char(9) format=$9. ,
Name varchar(50) format=$50. ,
OpCo char(3) format=$3. ,
State char(2) format=$2. ,
MV90 char(3) format=$3. ,
Active char(1) format=$1. ,
Interval smallint format=2. ,
StartDate date format=mmddyy10. ,
LastUploadDate date format=mmddyy10. ,
LastFinaledDate date format=mmddyy10. ,
WeatherStationID char(3) format=$3. ,
Constraint pk_ID Primary Key(ID) ,
Constraint fk_WeatherStationID Foreign Key(WeatherStationID) References Chapt09.Obj_WeatherStations On Update Cascade On Delete Set Null,
Constraint ck_Active Check((Active = “T”) or (Active = “F”)),
Constraint nn_Opco Not Null(OpCo) ,
Constraint nn_State Not Null(State) ,
Constraint nn_Name Not Null(Name) ,
Constraint nn_MV90 Not Null(MV90) ,
Constraint nn_Interval Not Null(Interval)
);
Quit;
In the above example, the two new constraints were called Check and Foreign Key. The Check constraint uses boolean logic to prevent rows from being added to the table that would violate said constraint. In the above example, we are only allowing a ‘T’ or ‘F’ to enter into the Active column. The Foreign Key constraint, known as a referential integrity constraint, occurs when we limit the available entries into the column based on values in a different table. In the above example, we are forcing all entries of the WeatherStationID column to exist in the ID column in the Obj_Weatherstations table. Whenever you declare a foreign key constraint, the columns you include in the parentheses (in this case, WeatherStationID) must relate directly to the Primary Key column in the referenced table. The statements following the table name (On Update Cascade On Delete Set Null) tell SQL that if we change a value in the parent table (Obj_Weatherstations), it will change the corresponding value in the child table (Obj_Recorders). If we delete a value in the parent table, we set the corresponding value in the child table to null.
Proc SQL;
Create Index idx_query On Chapt09.Obj_Recorders(MV90, Active, LastUploadDate);
Quit;
In this example, we’re creating a compound index. It is called a compound index because the index occurs on more than one column. Also, because we’re using more than one column, we can call the index whatever we wish. Now try and drop the index and table.
Proc SQL;
Drop Index idx_query On Chapt09.Obj_Recorders;
Drop Table Chapt09.OBJ_Recorders;
Quit;
Get any error messages?
ALTER Table
No doubt you got the following error:
ERROR: A rename/delete/replace attempt is not allowed for a data set involved in a referential integrity constraint.
WARNING: Table CHAPT09.OBJ_RECORDERS has not been dropped.
To drop the table entirely, we need to first drop the foreign key constraint. To do this, we need to ALTER the table. An ALTER table statement allows us to add, edit, and remove columns, and drop constraints. Knowing this, let’s try to drop the constraint THEN drop the table and see if we have any better luck.
Proc SQL;
Alter Table Chapt09.Obj_Recorders Drop Constraint fk_WeatherStationID;
Drop Table Chapt09.OBJ_Recorders;
Quit;
Success!
Views
We’re not going to spend too much time on Views because there not all that valuable in SAS. However, if you ever get involved in a larger RDBMS you will find them VERY helpful.
In the below example, we’re going to create a view that joins the Recorder table and WeatherStation table. Usually when creating views, try to preface the view name with “vw”.
Proc SQL;
Create View Chapt09.vw_Recorders as
Select A.ID as RecorderID,
A.Name,
A.OpCo,
A.Active,
B.ID,
B.City,
B.State
From Chapt09.Obj_Recorders as A
Inner Join Chapt09.Obj_Weatherstations as B
On A.WeatherstationID = B.ID;
Quit;
Also, dropping a View is just like dropping a table. However, there can’t be any constraints or indexes on a view.
Proc SQL;
Drop View Chapt09.vw_Recorders;
Quit;
Section III – Examples
Now that we’ve seen how to CREATE, DROP, and ALTER let’s go ahead and construct the rest of the database. Please take note of a certain type of index we create. I’ll let you find it yourself and we’ll discuss in class.
Create Tables
Proc SQL;
Create Table Chapt09.Obj_WeatherStations
(
ID char(3) format=$3. ,
City varchar(50) format=$50. ,
State char(2) format=$3. ,
Constraint pk_ID Primary Key(ID) ,
Constraint nn_City Not Null(City) ,
Constraint nn_State Not Null(State)
);
Quit;
Proc SQL;
Create Table Chapt09.LKP_LoadPeriods
(
IDStart date format=datetime19.,
ID date format=datetime19.,
IDDate date format=mmddyy10.,
IDTime date format=hhmm5.,
IDSeason char(6) format=$6.,
IDYear smallint ,
IDMonth smallint ,
IDDay smallint ,
IDHourEnding smallint ,
IDWeekday smallint ,
IDDayType smallint ,
IDWeekOfYear smallint ,
IDTradePeriod char(26) format=$26.,
DSTStart date format=datetime19.,
DST date format=datetime19.,
DSTDate date format=mmddyy10.,
DSTTime date format=hhmm5.,
DSTSeason char(6) format=$6.,
DSTYear smallint ,
DSTMonth smallint ,
DSTDay smallint ,
DSTHourEnding smallint ,
DSTWeekday smallint ,
DSTDayType smallint ,
DSTWeekOfYear smallint ,
DSTTradePeriod char(26) format=$26.,
DSTFlag char(1) format=$1.,
Constraint pk_ID Primary Key(ID) ,
Constraint nn_IDStart Not Null(IDStart) ,
Constraint nn_IDDate Not Null(IDDate) ,
Constraint nn_IDTime Not Null(IDTime) ,
Constraint nn_IDSeason Not Null(IDSeason) ,
Constraint nn_IDYear Not Null(IDYear) ,
Constraint nn_IDMonth Not Null(IDMonth) ,
Constraint nn_IDDay Not Null(IDDay) ,
Constraint nn_IDHourEnding Not Null(IDHourEnding) ,
Constraint nn_IDWeekday Not Null(IDWeekday) ,
Constraint nn_IDDayType Not Null(IDDayType) ,
Constraint nn_IDWeekOfYear Not Null(IDWeekOfYear) ,
Constraint nn_IDTradePeriod Not Null(IDTradePeriod) ,
Constraint nn_DSTStart Not Null(DSTStart) ,
Constraint nn_DST Not Null(DST) ,
Constraint nn_DSTDate Not Null(DSTDate) ,
Constraint nn_DSTTime Not Null(DSTTime) ,
Constraint nn_DSTSeason Not Null(DSTSeason) ,
Constraint nn_DSTYear Not Null(DSTYear) ,
Constraint nn_DSTMonth Not Null(DSTMonth) ,
Constraint nn_DSTDay Not Null(DSTDay) ,
Constraint nn_DSTHourEnding Not Null(DSTHourEnding) ,
Constraint nn_DSTWeekday Not Null(DSTWeekday) ,
Constraint nn_DSTDayType Not Null(DSTDayType) ,
Constraint nn_DSTWeekOfYear Not Null(DSTWeekOfYear) ,
Constraint nn_DSTTradePeriod Not Null(DSTTradePeriod),
Constraint ck_DSTFlag Check((DSTFlag = “T”) or (DSTFlag = “F”))
);
Quit;
Proc SQL;
Create Table Chapt09.LKP_StatusCodes
(
ID int ,
Code char(2) format=$2. ,
Description varchar(50) format=$50. ,
Constraint pk_ID Primary Key(ID) ,
Constraint nn_Code Not Null(Code) ,
Constraint nn_Description Not Null(Description)
);
Quit;
Proc SQL;
Create Table Chapt09.Obj_Recorders
(
ID varchar(14) format=$14. ,
Premise char(9) format=$9. ,
Name varchar(50) format=$50. ,
OpCo char(3) format=$3. ,
State char(2) format=$2. ,
MV90 char(3) format=$3. ,
Active char(1) format=$1. ,
Interval smallint format=2. ,
StartDate date format=mmddyy10.,
LastUploadDate date format=mmddyy10.,
LastFinaledDate date format=mmddyy10.,
WeatherStationID char(3) format=$3. ,
Constraint pk_ID Primary Key(ID) ,
Constraint fk_WeatherStationID Foreign Key(WeatherStationID) References Chapt09.Obj_WeatherStations On Update Cascade On Delete Set Null,
Constraint ck_Active Check((Active = “T”) or (Active = “F”)),
Constraint nn_Opco Not Null(OpCo) ,
Constraint nn_State Not Null(State) ,
Constraint nn_Name Not Null(Name) ,
Constraint nn_MV90 Not Null(MV90) ,
Constraint nn_Interval Not Null(Interval)
);
Quit;
Proc SQL;
Create Table Chapt09.Data_Load
(
RecorderID varchar(14) format=$14. ,
LoadPeriodID date format=datetime19. ,
Channel smallint ,
Value real ,
StatusCodeID int ,
Constraint fk_RecorderID Foreign Key(RecorderID) references Chapt09.Obj_Recorders on update cascade on delete set null,
Constraint fk_LoadPeriodID Foreign Key(LoadPeriodID) references Chapt09.LKP_LoadPeriods on update cascade on delete set null,
Constraint fk_StatusCodeID Foreign Key(StatusCodeID) references Chapt09.LKP_StatusCodes on update cascade on delete set null,
Constraint nn_Channel Not Null(Channel) ,
Constraint nn_Value Not Null(Value)
);
Quit;
Create Indexes
Proc SQL;
Create Unique Index idx_query On Chapt09.Data_Load(RecorderID, LoadPeriodID, Channel, StatusCodeID);
Create Index idx_query On Chapt09.Obj_Recorders(MV90, Active, LastUploadDate);
Create Index IDHourEnding On Chapt09.LKP_LoadPeriods(IDHourEnding);
Create Index City On Chapt09.Obj_Weatherstations(City);
Quit;
Create Views
Proc SQL;
Create View Chapt09.vw_Recorders as
Select A.ID as RecorderID,
A.Name,
A.OpCo,
A.Active,
B.ID,
B.City,
B.State
From Chapt09.Obj_Recorders as A
Inner Join Chapt09.Obj_Weatherstations as B
On A.WeatherstationID = B.ID;
Quit;
Drop Indexes
Proc SQL;
Drop Index idx_query On Chapt09.Obj_Recorders;
Drop Index idx_query On Chapt09.Data_Load;
Drop Index IDHourEnding On Chapt09.LKP_LoadPeriods;
Drop Index City on Chapt09.Obj_Weatherstations;
Quit;
Drop Constraints
Proc SQL;
Alter Table Chapt09.Obj_Recorders Drop Constraint fk_WeatherStationID;
Alter Table Chapt09.Data_Load Drop Constraint fk_LoadPeriodID;
Alter Table Chapt09.Data_Load Drop Constraint fk_RecorderID;
Alter Table Chapt09.Data_Load Drop Constraint fk_StatusCodeID;
Quit;
Drop Tables
Proc SQL;
Drop Table Chapt09.Data_Load;
Drop Table Chapt09.OBJ_Recorders;
Drop Table Chapt09.OBJ_WeatherStations;
Drop Table Chapt09.LKP_StatusCodes;
Drop Table Chapt09.LKP_LoadPeriods;
Drop View Chapt09.vw_Recorders;
Quit;
