SQL Write protection of tables

Write protection of tables

No votes yet.
Please wait...
Voting is currently disabled, data maintenance in progress.

Write protection can be applied to tables. This prevents DDL (Data Definition Language) and DML (Data Manipulation Language) changes during maintenance mode. The statement:

ALTER TABLE tablename READ ONLY; -- nur mehr lesbar ALTER TABLE tablename READ WRITE; -- Read and write possible
Werbung / Advertisements

VIEWs can be equipped with a WITH READ ONLY option. This ensures that no DML operations can be performed.

CREATE VIEW empView AS SELECT empno, empname, deptid FROM emp WHERE deptid = 10 WITH READ ONLY;

Alternatively, a CHECK option can be added to keep DML operations in the VIEW domain.

Werbung / Advertisements
Werbung / Advertisements
CREATE VIEW empView AS SELECT empno, empname, deptid FROM emp WHERE deptid = 10 WITH CHECK OPTION CONSTRAINT empView_ck;

This ensures that only values relating to Department 10 can be inserted, changed or deleted. Inserting an employee with another deptId is not possible.

No votes yet.
Please wait...
Voting is currently disabled, data maintenance in progress.