Data Definition Language

DATA DEFINITION LANGUAGE:

• DEFAULT dafaultvalue — To set default value.
• CONSTRAINT name_PK PRIMARY KEY (fieldname).
• CONSTRAINT name_FK FOREIGN KEY (fieldname).
• CONSTRAINT name_NN NOT NULL (fieldname) — ONLY INLINE.
• CONSTRAINT name_CK CHECK (fieldname IN(x,y)).
• CONSTRAINT name_UQ UNIQUE (fieldname).

•  Implicit commit occurs after DDL even if it fails on even if rolled back.

• CREATE DATABASE.
• CREATE TABLE.
• ALTER TABLE ADD (columnname) MODIFY (columnname) (No need of brackets if single data) The existing data should not be altered by a change of properties Add DEFAULT value there are NULL values and we change to NOT NULL.
• ALTER TABLE tablename RENAME columnname1 TO columname2.
• ALTER TABLE DROP (columnname) (No need of brackets if single data) If it is a primary key or foreign key we need to add CASCADE CONSTRAINT.
• ALTER TABLE tablename SET UNUSED COLUMN columnname Similar to drop though it is retained. Better performance in heavy usage.

• ALTER TABLE ADD (CONTRAINT constraintname contrainttype (columnname) Not for NULL.
• NOT NULL has to be in line not out of line (not using ADD).
• ALTER TABLE MODIFY columnname CONTRAINT constraintname contrainttype.
• ALTER TABLE tablename DROP PRIMARY KEY CASCADE or KEEP INDEX or DROP INDEX.
• ALTER TABLE tablename DROP UNIQUE (col1, colo2, …).
• To DROP NULL use modify as NULL.
• ALTER TABLE DISABLE constrainttype constraintname — PRIMARY KEY UNIQUE CHECK FOREIGN KEY.
• ALTER TABLE MODIFY constrainttype constraintname DISABLE — PRIMARY KEY UNIQUE CHECK FOREIGN KEY.
• ENABLE VALIDATE same as ENABLE.
• ENABLE NOVALIDATE same as ENABLE without validation.
• DISABLE VALIDATE same as ENABLE Disables.
• DISABLE NOVALIDATE same as DISABLE.
• ENABLE ensures that all incoming data conforms to the constraint.
• DISABLE allows incoming data, regardless of whether it conforms to the constraint.
• VALIDATE ensures that existing data conforms to the constraint.
• NOVALIDATE means that some existing data may not conform to the constrain.
• DROP TABLE tablename CASCADE CONSTRAINTS.
• ALTER TABLE … ON DELETE CASCADE or SET NULL (more relations) for deleting rows with constraints.
• To use DEFERRED create constraint with DEFERRABLE in the end and then use.
• SET CONSTRAINT ALL or contraintname DEFFERED Reverse it with IMMEDIATE.
• ALTER TABLE RENAME contraintname1 TO constraintname2.

• CREATE TABLE tablename fieldname fielddatatype CONSTRAINT — INLINE.
• CREATE TABLE tablename fielddetails, ADD CONSTRAINT constraintname CONSTRAINTTYPE fieldname — EXTERNAL.
• ALTER TABLE tablename MODIFY fieldname CONSTRAINT constraintname CONSTRAINTTYPE — INLINE.
• ALTER TABLE tablename ADD CONSTRAINT constraintname CONSTRAINTTYPE fieldname — EXTERNAL.
• FOREIGN KEY (columnname) REFERENCES tablename(columnname).

• CREATE OR REPLACE VIEW viewname AS SELECT … WITH CHECK OPTION (Now with check option any insert or update operation that makes a record disappear from the view raises a trappable runtime error).
• Views provide security and abstraction.
• They are objects and satisfy underlying table constraints.
• INLINE VIEWS.
• ALTER VIEW viewname COMPILE.

• CREATE SEQUENCE sequence_name sequence_options INCREMENT BY integer STARTS BY integer MAXVALUE integer MINVALUE integer NOMAXVALUE NOMINVALUE CYCLE NOCYCLE.
• columnname.NEXTVAL columnname.CURRVAL.
• Can use within INSERT, SELECT not in WHERE.
• First call-in session must be NEXTVAL.
• Even if INSERT fails INCREMENT occurs.

• CREATE INDEX indexname ON tablename(columnname).
• INDEX work on =, <, >, LIKE string% not on functions (unless function based index) or on inequality or %string.
• Usually five indexes on a transaction-based table.

• Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the non-leading key of the index

• CREATE UNIQUE INDEX indexname ON tablename(columnname)
• DROP INDEX indexname

• CREATE OR REPPLACE SYNONYMN syynonymnname for tablename
• CREATE OR REPLACE PUBLIC SYNONYMN synonymnname for tablename Here created by USER PUBLIC Everyone can see but access to be provided.

• Consider A has created a table and B needs access We can give access by.
• GRANT SELECT ON tablename TO user.
• Access by user.tablename etc.
• Now using PUBLIC SYNONYMNS user.table can be replaced with user. If user changes, we need to alter only PUBLIC SYNONYMN.
• DROP SYNONYMN synonymnname.
• DROP PUBLIC SYNONYMN synonymnname.
• First namespace searched for is the schema followed by public.
• SYNONYMN used by itself is private.

• SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = tablename.
• CREATE TABLE table_name PRIMARY KEY USING INDEX (CREATE INDEX indexname ON tablename(columnname) We can create index on PRIMARY KEY as well as UNIQUE.
• CREATE TABLE tablename … CONTRAINT indexname(columnnames) USING INDEX (CREATE INDEX indexname ON tablename(columnnames) or … tablename(expression) to create function-based indexes. Only computation result is considered mainly not order.

• FLASHBACK_TRANSACTION_QUERY (UNDO_SQL) UNDO_SQL the query that will undo but not necessarily the ROWID.
• FLASHBACK TABLE tablename TO BEFORE DROP.
• FLASHBACK TABLE tablename TO SCN scn_expression.
• FLASHBACK TABLE tablename TO TIMESTAMP timestamp_expression.
• FLASHBACK TABLE tablename TO RESTOREPOINT restore_point_expression.
• When flashedback all constraints except refereential integrity constraints are restored.

• EXTERNAL TABLE
  CREATE TABLE statments
  ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER     -- ORACLE DATA_PUMP
  ACCESS PARAMETERS
  (RECORDS DELIMITED BY NEWLINE
  SKIP 2
  FIELDS ( fieldname fieldtype)
  )
  LOCATION 'filepath'
  )

• Cannot use LOB, Foreign key constraints, UNUSED Also no INSERT, UPDATE or DELETE.