Creating Tables
Below are some examples of how to create tables in Oracle 12. PL/SQL. For the most part PL/SQL follows standard SQL conventions. I will show how to create some tables and some of the more popular options when creating tables.
Create Table example with primary key and check constraint.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE Customer ( custno VARCHAR(8) NOT NULL ,custname VARCHAR2(128) NOT NULL ,address VARCHAR2(128) NOT NULL ,internal char(1) NOT NULL ,contact VARCHAR2(128) NOT NULL ,phone VARCHAR2(32) NOT NULL ,city VARCHAR2(128) NOT NULL ,"state" VARCHAR2(2) NOT NULL ,zip VARCHAR2(20) NOT NULL ,CONSTRAINT custnoPK PRIMARY KEY (custno) ,CONSTRAINT isInternal CHECK(internal IN ('Y', 'N')) ); |
The table creation is straight forward and similar to other create table SQL statements. There are a few items of interest in creating the table.
In the column names the column state is in quotes. The reason being is that STATE is a reserved word in PL/SQL. While normally not advisable to name a column with the same value as a reserved word, in the example I am showing how you can do it.
On each line, I’m specifying a NOT NULL constraint. When inserting rows not only will the datatype need to match but each column will need a value. Alternatively if a column could be NULL the NOT NULL can be omitted, or implicitly stated by just using NULL.
The next constraint is specifying the custno column as the primary key. The value will need to be unique and by definition can not be null. The last constraint added, was added to the isInternal column where the value must be a “Y” or “N”.
Foreign Keys
The next example adds a foreign key when creating the table.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Location ( locno VARCHAR2(8) NOT NULL ,facno VARCHAR2(8) NOT NULL ,locname VARCHAR2(128) NOT NULL ,CONSTRAINT locnoPK PRIMARY KEY (locno) ,CONSTRAINT FK_FACNO FOREIGN KEY (facno) REFERENCES FACILITY(facno) ); |
While a primary key column can not be null, a foreign key can be null. If the value is optional, you will want to designate it as a NULL column. The foreign key will reference values of the primary key in the FACILITY table. The value must exist in the FACILITY table before trying to insert the row into the Location table.
Self Reference Constraints
The next example shows how you can reference another column in the table for the constraint.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE EventPlanLine ( planno VARCHAR(8) NOT NULL ,lineno INTEGER NOT NULL ,TimeStart DATE NOT NULL ,TimeEnd DATE NOT NULL ,NumberFld INTEGER NOT NULL ,locno VARCHAR2(8) NOT NULL ,resno VARCHAR2(8) NOT NULL ,CONSTRAINT linenoPK PRIMARY KEY (planno, lineno) ,CONSTRAINT plannoFK FOREIGN KEY (planno) REFERENCES EventPlan(planno) ,CONSTRAINT locnoFK FOREIGN KEY (locno) REFERENCES LOCATION(locno) ,CONSTRAINT resnoFK FOREIGN KEY (resno) REFERENCES RESOURCETBL(resno) ,CONSTRAINT timeCK CHECK(TimeStart < TimeEnd) ); |
Here in this example, the constraint is on the TimeStart and TimeEnd columns. When inserting or updating rows, the TimeStart must be less than the TimeEnd.
The above are some common examples of creating tables in PL/SQL, while it doesn’t cover every possibility it shows some of the more common tasks. Let me know if you found the above useful.
— michael.data@eipsoftware.com
Leave a Reply