Oracle DDL | John & Kathy Purcell

Oracle Commands to manipulate data structures and there indicies.

Create Table

CREATE TABLE testtable (
field1 VARCHAR2(255) NOT NULL,
field2 NUMBER(10) NOT NULL,
field3 CHAR(1) NOT NULL,
field4 NUMBER(10),
field5 VARCHAR2(255),
field6 DATE,
CONSTRAINT "TESTTABLE_PK" PRIMARY KEY("FIELD1"),
CONSTRAINT "TESTTABLE_FK01"
FOREIGN KEY("FIELD2")
REFERENCES "OTHERTABLE"("OTHERTABLEFIELD1"),
CONSTRAINT "TESTTABLE_CK01"
CHECK(field3 in ('A', 'C', 'D')),
CONSTRAINT "TESTTABLE_UQ01" UNIQUE(field4),
);

Alter Table

ALTER TABLE testtable
ADD ( field7 varchar2(100) );

ALTER TABLE testtable
ADD CONSTRAINT testtable_CK99
CHECK ( field3 in ('A', 'C', 'D', 'X') );

ALTER TABLE testtable
ADD CONSTRAINT testtable_UQ99
UNIQUE( field5 );
exit;

Drop Table

DROP TABLE testtable;

Create Index

CREATE INDEX testtable_idx01 ON testtable(field1, field2);

Create Index with Analyze

CREATE UNIQUE INDEX testtable_idx02 ON testtable(field5)
COMPUTE STATISTICS;
exit;

Drop Index

DROP INDEX testtable_idx01;