Much More About Constraints

PRIMARY KEY LOOK UP DURING FOREIGN KEY CREATION


The lookup of matching primary keys at time of foreign key insertion takes time.In realease Oracle 9i , the first 256 primary keys can be cached so the addition of multiple foreign keys become significantly faster .The cache is only set up after the second row is processed.this avoid overhead of setting up a cache for single row DML.

----------------------
Constraints On View
--------------------
Constraint definitions are done on View from Oracle 9i onwards.
Views constraint definitions are declarative in nature;therefore DML operations on view are subject to the constraints defined on base tables.
defining constraints on base table is necessary ,not only for data correctness and cleanliness but also for MV query

NOT NULL and CHECK constraint are not supported on Views

For Creating View with constraints you must specify [RELY|NORELY ](Allows/disallows query rewrites) DISABLE NOVALIDATE (valid state for view constraint )
Otherwise it will result an error message.

Table Creation/// NOT NULL (check constraint)

Everything (Table Creation including Primary ,Foreign key ,Check constraint and giving user defined CONSTRAINT NAME for NOT NULL CONSTRAINT) in a single Script ;without using alter command for predefined constraints till design time


CREATE TABLE   STUDENTS_MAST(
N_STUDID NUMBER(5),CONSTRAINT PK_STUDID  PRIMARY KEY(N_STUDID))


CREATE TABLE STUDENT_TEAM (
N_STDTMID NUMBER(10) ,CONSTRAINT PK_STUDENT_TEAM PRIMARY KEY(N_STUDTMID),
N_STUDID NUMBER(10),CONSTRAINT FK_STUD_TEAM_EMPID FOREIGN KEY(N_STUDID) REFERENCES STUDENTS_MAST(N_STUDID),
C_TLFLAG CHAR(1) ,CONSTRAINT CK_STUD_TEAM_TLFLAG CHECK (C_TLFLAG IN ('Y','N')) ,
D_STDATE DATE CONSTRAINT NN_STUD_TEAM_STDATE NOT NULL,
D_ENDDATE DATE CONSTRAINT NN_STUD_TEAM_ENDATE NOT NULL,
D_ENTRYDAT DATE DEFAULT SYSDATE )


--------------

CREATE TABLE STUDENT_TEAM (N_STDTMID NUMBER(10) CONSTRAINT NN_STUDENT_STATUS_TMID NOT NULL)


ALTER TABLE STUDENT_STATUS ADD ( CONSTRAINT NN_ST_STATUS_TMID CHECK (N_STDTMID IS NOT NULL))


Both Scripts will restrict not null values but error code will be different first script results


ORA-01407: cannot update to NULL

ORA-01400: cannot insert NULL into N_STDTMID

second script results


Another difference is the null(nullable) field will be true when null is checked by first script but it will not be for second second script
-----------
During check constraint creation if already exists some records in the particular table which violates the new check constraint.This error will be displayed
ORA-02293: cannot validate (##########) - check constraint violated

-----------------