Profile Picture
IT Made Easy
The site provides some rough IT Notes that make your life easier. The site currently has notes on HTML, CSS, Regex, OOPS, JS, JQuery, PHP, Java, ASP.NET, C#, Database, Linux, URL Rewrite, SEO, Project Management and Hosting. Please do share this site if you like.

Data Definition Language

Next
Next

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 rolledback
  • 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 prrformance 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
  • He 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

IT Notes

by VinodSebastian



IT Downloads


Catalogue