This article presents constraints types that can be created on view level:

• check option
• read only
• primary key
• unique key
• foreign key

First let’s create some sample tables

DROP TABLE dmnsn_tbl1;
DROP TABLE fct_tbl;

CREATE TABLE dmnsn_tbl1
(
  id   NUMBER,
  name VARCHAR2(10)
);

CREATE TABLE fct_tbl
(
  fact_id NUMBER,
  id1     NUMBER,
  id2     NUMBER,
  measr1  NUMBER,
  measr2  NUMBER
);

ALTER TABLE fct_tbl 
ADD CONSTRAINT fct_tbl_pk 
PRIMARY KEY(fact_id);

INSERT INTO dmnsn_tbl1
SELECT level, level
  FROM dual
CONNECT BY level < 5;

INSERT INTO fct_tbl
SELECT level, level, level, level, level
 FROM dual
CONNECT BY level < 5;

COMMIT;

SELECT * FROM fct_tbl;

   FACT_ID        ID1        ID2     MEASR1     MEASR2
---------- ---------- ---------- ---------- ----------
         1          1          1          1          1
         2          2          2          2          2
         3          3          3          3          3
         4          4          4          4          4

• check option

This kind of constraints is used to disable

CREATE OR REPLACE VIEW view_check_option 
AS
SELECT * 
  FROM fct_tbl 
 WHERE fact_id=1
WITH CHECK OPTION;

--following update and inserts violates this check with
--ORA-01402: view WITH CHECK OPTION where-clause violation

UPDATE view_check_option 
   SET fact_id=6 
 WHERE fact_id=1;
 
INSERT INTO view_check_option 
VALUES(5,5,5,5,5);

• read-only

CREATE OR REPLACE VIEW view_read_only 
AS
SELECT * 
  FROM fct_tbl 
 WHERE fact_id=1
WITH READ ONLY;

--following update violates this check with
--ORA-42399: cannot perform a DML operation 
--on a read-only view

UPDATE view_read_only SET measr1 = 5;

• primary key

CREATE OR REPLACE VIEW dmnsn_tbl1_vw
AS
SELECT * FROM dmnsn_tbl1;

ALTER VIEW dmnsn_tbl1_vw 
ADD CONSTRAINT dmnsn_tbl1_vw_pk 
PRIMARY KEY (id) DISABLE NOVALIDATE;

• unique key

CREATE OR REPLACE VIEW fct_tbl_vw
AS
SELECT * FROM fct_tbl;

ALTER VIEW fct_tbl_vw 
ADD CONSTRAINT fct_tbl_vw_u 
UNIQUE(fact_id) DISABLE NOVALIDATE;

• foreign key

ALTER VIEW fct_tbl_vw 
ADD CONSTRAINT fct_tbl_vw_fk1 FOREIGN KEY (id1) 
REFERENCES dmnsn_tbl1_vw DISABLE NOVALIDATE;

So once created drop view command is

DROP VIEW dmnsn_tbl1_vw CASCADE CONSTRAINTS;

Have a fun 🙂

Tomasz