A wise man once said: start at the beginning and go on till you come to the end: then stop.<\/p>\n
We\u2019ve 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.\u00a0 Time to fix that.<\/p>\n
Here’s the syntax:<\/p>\n
CREATE TABLE <table name> (\r\n\t<column name><data type>,\r\n\t<column name><data type>,\r\n\t<column name><data type>\r\n\u2026);<\/pre>\nThere are a few rules that you’ll need to bear in mind:<\/p>\n
Actually, every<\/i> name you give objects you create in your database \u2013 and this includes our stored procedures, functions and sequences \u2013 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.<\/p>\nValid Oracle Identifiers<\/h5>\n
An acceptable Oracle name must:<\/p>\n
That\u2019s the law and you must obey it; however, I would recommend that you come up with some rules \u2013 conventions \u2013 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\u2019ve got a table named FRIEND_NAME, and others named FRIEND_ADDRESS, FRIEND_PHONE\u00a0 and PHONE_NUMBER. If we needed to create a new table to hold email addresses, wouldn\u2019t you expect it to be named \u2026 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.<\/p>\n
Let\u2019s talk about database design a bit more later, perhaps in a future installment of this series.<\/p>\n
Now we know the syntax, the rules and the conventions, let us produce the code needed to create the FRIEND_NAME table.<\/p>\n
CREATE TABLE friend_name (\r\n friend_id\u00a0 NUMBER(3),\r\n first_name VARCHAR2(30),\r\n middle_name VARCHAR2(30),\r\n last_name VARCHAR2(30),\r\n gender VARCHAR2(1)\r\n);<\/pre>\nYou won\u2019t be able to successfully run the code because the table FRIEND_NAME already exists (remember: you can\u2019t have two children named John), so maybe we should try writing a script that we will be able to run.<\/p>\n
CREATE TABLE friend_email (\r\n friend_id NUMBER(3),\r\n email VARCHAR2(50)\r\n);<\/pre>\nThat\u2019s pretty straightforward. Try running it.<\/p>\n
Did it work? Good.<\/p>\n
But what if, after creating our table, we realise that we\u2019d like some date columns to map the life of the email address.\u00a0 We have some choices.<\/p>\n
We can drop the table (in order to rebuild it with the columns we want).\u00a0 Here\u2019s the syntax.<\/p>\n
DROP TABLE <table-name>;<\/pre>\nDROP TABLE friend_email;<\/pre>\nHowever, 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.<\/p>\n
ALTER TABLE <table name>\r\nADD (\r\n <column name> <datatype>,\r\n <column name> <datatype>\r\n);<\/pre>\nALTER TABLE friend_email\r\nADD (\r\n start_date DATE,\r\n end_date\u00a0 DATE\r\n);<\/pre>\nThe 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.<\/p>\n
ALTER TABLE <table-name>\r\n DROP COLUMN <column-name>;<\/pre>\nALTER TABLE friend_email\r\n DROP COLUMN start_date;<\/pre>\nThere are other things we can do with the alter command. Let\u2019s talk about another one.<\/p>\n
Constraints<\/h5>\n
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 \u2013 friend_id and email.\u00a0 To add this constraint to our table we\u2019ll need to depend on our trusty alter command again.<\/p>\n
ALTER TABLE <table name>\r\n ADD CONSTRAINT <constraint name>\r\n PRIMARY KEY (<column name>, <column name>);<\/pre>\nALTER TABLE friend_email\r\n ADD CONSTRAINT friend_email_pk\r\n PRIMARY KEY (friend_id, email);<\/pre>\nWhat this means is that every record we insert into this table must<\/i> have an email address and must<\/i> have a friend id, and this coupling of data must not be repeated.<\/p>\n
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<\/i> to records in friend_email).<\/p>\n
The syntax for creating a foreign key constraint is as follows:<\/p>\n
ALTER TABLE <table name>\r\n ADD CONSTRAINT <constraint name>\r\n FOREIGN KEY (<column name>)\r\n REFERENCES <parent table> (<column name>);<\/pre>\nALTER TABLE friend_email\r\n ADD CONSTRAINT friend_name_friend_email_fk\r\n FOREIGN KEY (friend_id)\r\n REFERENCES friend_name (friend_id);<\/pre>\nWhat 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.<\/p>\n
Triggers<\/h5>\n
There is another piece of our jigsaw that I would like to talk about, but it\u2019s probably best if we pause for a quick recap. We\u2019ve talked about creating tables, and on the back of that we\u2019ve created primary keys and foreign keys; we\u2019ve talked about PL\/SQL blocks, and on the back of that we\u2019ve created named procedures and named functions; and we\u2019ve talked about creating sequences.<\/p>\n
And if you\u2019re thinking, I wish there was a way we could tie all of this together, then sit back cos I\u2019m about to blow your socks off.<\/p>\n
Since friend_id is the primary key column in friend_name and is populated by our friend_id_seq sequence, wouldn\u2019t 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?<\/p>\n
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.)<\/p>\n
Here is the syntax for a table trigger.<\/p>\n
CREATE OR REPLACE TRIGGER <trigger name>\r\n [BEFORE|AFTER] [INSERT|UPDATE|DELETE]\r\n ON <table name> \r\n FOR EACH ROW\r\n\r\nDECLARE\r\n -- variable declarations\r\nBEGIN\r\n -- trigger code\r\nEND;<\/pre>\nThere are a few things to note. You can choose whether you want your trigger to fire before<\/i> or after<\/i> the row is inserted\/updated\/deleted. \u00a0This 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<\/i> it has been deleted, would you?<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Our trigger to populate the friend_id column in FRIEND_NAME will look something like this.<\/p>\n
CREATE OR REPLACE TRIGGER friend_name_id_trg\r\n BEFORE INSERT\r\n ON friend_name\r\n FOR EACH ROW\r\nBEGIN\r\n SELECT friend_id_seq.nextval\r\n INTO :new.friend_id\r\n FROM dual;\r\nEND;\r\n\/<\/pre>\nTriggers are powerful \u2013 and much despised \u2013 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.\u00a0 Many developers dislike them because they fire silently in the background, seeming to live just beyond the fingertips of their control.<\/p>\n
My advice is this:<\/p>\n
Beyond that, use them only when they are clearly the best available option.<\/p>\n
<\/p>\n
A wise man once said: start at the beginning and go on till you come to the end: then stop. We\u2019ve 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.\u00a0 Tim…<\/p>\n","protected":false},"author":221907,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[48366,4197,4459,48529],"coauthors":[],"class_list":["post-73198","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-beginners-guide","tag-database-design","tag-oracle","tag-triggers"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73198","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221907"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73198"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73198\/revisions"}],"predecessor-version":[{"id":91684,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73198\/revisions\/91684"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73198"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73198"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73198"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73198"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}