SQL Oracle DDL

ALTER.sql

-- ------------------------------------
-- ALTER TABLE ADD CONSTRAINT
-- ------------------------------------
-- ALTER TABLE <table>
-- ADD CONSTRAINT <constraintName> <constraint>
-- ------------------------------------
CREATE TABLE table_alter_constraint(
    id INT
);

ALTER TABLE table_alter_constraint
ADD CONSTRAINT PK PRIMARY KEY(id);

-- ------------------------------------
-- ALTER TABLE
-- ------------------------------------
CREATE table table_to_alter(
    id INT NOT NULL PRIMARY KEY,
    col1 VARCHAR2(10)
);

ALTER TABLE table_to_alter
ADD col2 varchar2(20);

ALTER TABLE table_to_alter
MODIFY col2 varchar2(30);

ALTER TABLE table_to_alter
RENAME COLUMN col2 TO col3;

ALTER TABLE table_to_alter
DROP COLUMN col3;

CONSTRAINTS.sql

CREATE TABLE gist_constraint_test(
    cons_unique INT UNIQUE,
    cons_not_null INT NOT NULL,
    cons_pk INT PRIMARY KEY,
    cons_check INT CHECK ( cons_check > 0 ),
    cons_fk_cascade INT REFERENCES gist_constraint_test(cons_unique) ON DELETE CASCADE,
    cons_fk_null INT REFERENCES gist_constraint_test(cons_unique) ON DELETE SET NULL
);

CREATE TABLE gist_constraint_test(
    cons_unique INT,
    cons_pk INT,
    cons_check INT,
    cons_fk_cascade INT,
    cons_fk_null INT,
    UNIQUE (cons_unique),
    PRIMARY KEY (cons_pk),
    CHECK ( cons_check > 0 ),
    FOREIGN KEY (cons_fk_cascade) REFERENCES gist_constraint_test(cons_unique) ON DELETE CASCADE,
    FOREIGN KEY (cons_fk_null) REFERENCES gist_constraint_test(cons_unique) ON DELETE SET NULL
);

ALTER TABLE gist_constraint_test ADD CONSTRAINT named_check CHECK ( cons_named_check > 0);
ALTER TABLE gist_constraint_test ADD CONSTRAINT named_check CHECK ( cons_named_check BETWEEN 0 AND 1000);
ALTER TABLE gist_constraint_test ADD CONSTRAINT named_check CHECK ( cons_named_check = UPPER(cons_named_check)); -- Does not make sense on INT column

CREATE.sql

-- ------------------------------------
-- CREATE TABLE SYNTAX:
-- ------------------------------------
-- CREATE TABLE <table> (
--    <name> <typ> <constraint>
-- );
-- ------------------------------------

CREATE TABLE table_column_constraint (
    id INT PRIMARY KEY,
    name VARCHAR2(10) NOT NULL,
    age INT CHECK ( age >= 18 )
);

-- ------------------------------------
-- CREATE TABLE SYNTAX:
-- ------------------------------------
-- CREATE TABLE <table> (
--    <name> <typ>,
--    CONSTRAINT <constraint>
-- );
-- ------------------------------------

CREATE TABLE table_table_constraint (
    id INT,
    name VARCHAR2(10) NOT NULL,
    age INT,
    PRIMARY KEY(id),
    CONSTRAINT over_18_check CHECK(age >= 18)
);

-- ------------------------------------
-- CREATE TABLE WITH FOREIGN KEY
-- ------------------------------------
CREATE TABLE fk_person(
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR2(100) NOT NULL
);

-- SEPERATE
CREATE TABLE fk_orders (
    id INT NOT NULL PRIMARY KEY ,
    person INT,
    CONSTRAINT fk_orders_person FOREIGN KEY(person) REFERENCES fk_person(id)
);

-- SELF-CONTAINING
CREATE TABLE fk_orders (
  	id INT NOT NULL PRIMARY KEY,
  	person INT CONSTRAINT person_fk REFERENCES fk_person(id)
);