A wise man once said: start at the beginning and go on till you come to the end: then stop.
We’ve done things a little differently; all this time we’ve been talking about examining and manipulating the data in our tables, but we have never paused to consider where these tables come from, or discuss how they are created. Time to fix that.
Creating a Table
Here’s the syntax:
CREATE TABLE <table name> ( <column name><data type>, <column name><data type>, <column name><data type> …);
There are a few rules that you’ll need to bear in mind:
- The table name must be unique. You cannot, for example, have two tables named FRIEND_NAME. After all, you wouldn’t have two children and name them both John. Or maybe you would; I don’t know you that well.
- Your column names must be unique within your table. While you cannot have two columns in FRIEND_NAME called friend_id; it is possible to have friend_id in FRIEND_NAME and FRIEND_ADDRESS.
- Each column must have a data type. You’ll recall that we’ve discussed a number of different data types – VARCHAR2, NUMBER, DATE. With VARCHAR2 columns you must additionally specify the length; you should specify the precision for NUMBER columns too.
- The names you give your table – and your columns – must be valid Oracle identifiers.
Actually, every name you give objects you create in your database – and this includes our stored procedures, functions and sequences – must obey a number of set rules, or Oracle will throw its toys out of the cot and give you an error. Here are those rules.
Valid Oracle Identifiers
An acceptable Oracle name must:
- Not be a reserved keyword. You cannot, for example, name your table TABLE or PROCEDURE or VARCHAR2.
- Be no longer than 30 characters.
- Begin with an alphabetical character. You can have numbers and certain special characters in the name, but the first character must be an alpha character.
- Contain only alphabetical characters, numbers, or one of the following special characters: # $ _
That’s the law and you must obey it; however, I would recommend that you come up with some rules – conventions – of your own that you follow in naming objects in your database. The surest shortcut to confusion in a database is a mishmash of incomprehensible object names. Take our sample database for example: we’ve got a table named FRIEND_NAME, and others named FRIEND_ADDRESS, FRIEND_PHONE and PHONE_NUMBER. If we needed to create a new table to hold email addresses, wouldn’t you expect it to be named … EMAILS_4_OUR_PALS? See how that jars? We instinctively know it should be named something like FRIEND_EMAIL; the dissonance caused by an unexpected name is often the first step to a badly-designed database.
Let’s talk about database design a bit more later, perhaps in a future installment of this series.
Now we know the syntax, the rules and the conventions, let us produce the code needed to create the FRIEND_NAME table.
CREATE TABLE friend_name ( friend_id NUMBER(3), first_name VARCHAR2(30), middle_name VARCHAR2(30), last_name VARCHAR2(30), gender VARCHAR2(1) );
You won’t be able to successfully run the code because the table FRIEND_NAME already exists (remember: you can’t have two children named John), so maybe we should try writing a script that we will be able to run.
CREATE TABLE friend_email ( friend_id NUMBER(3), email VARCHAR2(50) );
That’s pretty straightforward. Try running it.
Did it work? Good.
But what if, after creating our table, we realise that we’d like some date columns to map the life of the email address. We have some choices.
We can drop the table (in order to rebuild it with the columns we want). Here’s the syntax.
DROP TABLE <table-name>;
DROP TABLE friend_email;
However, if our table already contained valuable data, this option might not be open to you. A less drastic option would be to ALTER the table.
ALTER TABLE <table name> ADD ( <column name> <datatype>, <column name> <datatype> );
ALTER TABLE friend_email ADD ( start_date DATE, end_date DATE );
The alter command has more tricks in its backpack than just adding columns. For example, if you realise that the start_date column is redundant (email addresses do not have start dates), you can drop the column.
ALTER TABLE <table-name> DROP COLUMN <column-name>;
ALTER TABLE friend_email DROP COLUMN start_date;
There are other things we can do with the alter command. Let’s talk about another one.
We talked about constraints in previous articles, and I told you how all tables should have a primary key to describe uniqueness. For our new table, a unique record would probably be described by a pair of columns – friend_id and email. To add this constraint to our table we’ll need to depend on our trusty alter command again.
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> PRIMARY KEY (<column name>, <column name>);
ALTER TABLE friend_email ADD CONSTRAINT friend_email_pk PRIMARY KEY (friend_id, email);
What this means is that every record we insert into this table must have an email address and must have a friend id, and this coupling of data must not be repeated.
Talking about the friend_id column brings us to the foreign key constraint. Every friend_id we use in friend_email must already exist in the friend_name table (in other words, a record in friend_name must be the parent to records in friend_email).
The syntax for creating a foreign key constraint is as follows:
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> FOREIGN KEY (<column name>) REFERENCES <parent table> (<column name>);
ALTER TABLE friend_email ADD CONSTRAINT friend_name_friend_email_fk FOREIGN KEY (friend_id) REFERENCES friend_name (friend_id);
What this now means is that if you try to insert a record with a friend_id that does not exist in friend_name, Oracle will smack you with an error.
There is another piece of our jigsaw that I would like to talk about, but it’s probably best if we pause for a quick recap. We’ve talked about creating tables, and on the back of that we’ve created primary keys and foreign keys; we’ve talked about PL/SQL blocks, and on the back of that we’ve created named procedures and named functions; and we’ve talked about creating sequences.
And if you’re thinking, I wish there was a way we could tie all of this together, then sit back cos I’m about to blow your socks off.
Since friend_id is the primary key column in friend_name and is populated by our friend_id_seq sequence, wouldn’t it be nice if we could write some PL/SQL that would automatically populate the next value from our sequence into the column each time a new row is inserted?
And that’s where triggers come in. An Oracle trigger is a procedure that is automatically executed on the occurrence of some other specified event. Since we can code a trigger to fire on a table insert, we can use it to get friend_id_seq.nextval from our sequence and put it in the friend_id column. (Triggers can also fire on updates and deletes.)
Here is the syntax for a table trigger.
CREATE OR REPLACE TRIGGER <trigger name> [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON <table name> FOR EACH ROW DECLARE -- variable declarations BEGIN -- trigger code END;
There are a few things to note. You can choose whether you want your trigger to fire before or after the row is inserted/updated/deleted. This is important as it determines what table values are available to your code; you wouldn’t expect to be able to access a record after it has been deleted, would you?
Talking of referencing a table’s values brings us to a construct that is specific to triggers – :NEW. and :OLD. Since triggers execute when a table’s values may be in flux, we need to tell Oracle what value we want. If, for example, you are updating the last_name column for your friend, Monica, from Geller to Bing, :OLD.last_name will be Geller, while :NEW.last_name will be Bing.
With INSERT triggers, only :NEW values are available. Since inserts create brand new records, there are no :OLD values to reference. Similarly, with DELETE triggers, you can only reference :OLD values.
Another thing worth noting is the phrase FOR EACH ROW; it means that your trigger will fire for every row affected. If you update 1000 records, your update trigger will fire 1000 times.
Our trigger to populate the friend_id column in FRIEND_NAME will look something like this.
CREATE OR REPLACE TRIGGER friend_name_id_trg BEFORE INSERT ON friend_name FOR EACH ROW BEGIN SELECT friend_id_seq.nextval INTO :new.friend_id FROM dual; END; /
Triggers are powerful – and much despised – tools. Simple triggers to populate primary key columns from sequences are pretty uncontroversial; however, there is no upper limit to their potential complexity. They can be hundreds of lines long and contain convoluted conditions. Many developers dislike them because they fire silently in the background, seeming to live just beyond the fingertips of their control.
My advice is this:
- always use them to populate primary key columns that depend on sequences;
- always use them for auditing. (You could, for example, create an audit table into which you automatically inserted a record each time a major change is carried out on FRIEND_NAME.)
Beyond that, use them only when they are clearly the best available option.