Consider the following simple view: CREATE VIEW Emp_view AS SELECT Ename, Empno, deptno FROM Emp; This view does not involve a join operation. In general, all modifiable columns of a join view must map to columns of a key-preserved table. Empno WITH CHECK OPTION; No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.If you issue the SQL statement: UPDATE Emp_view SET Ename = 'SHAHRYAR' WHERE Empno = 109; then the EMP base table that underlies the view changes, and employee 109's name changes from ASHI to SHAHRYAR in the EMP table. If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not modifiable. The following INSERT statement on the EMP_DEPT view succeeds, because only one key-preserved base table is being modified (EMP), and 40 is a valid DEPTNO in the DEPT table (thus satisfying the FOREIGN KEY integrity constraint on the EMP table).For example, suppose all the employee working in Department No.
The following statement inserts a new row into the EMP table using the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (199, 'ABID', 30); Restrictions on DML operations for views use the following criteria in the order listed: The constraint created by WITH CHECK OPTION of the ACCOUNTS_STAFF view only allows rows that have a department number of 10 to be inserted into, or updated in, the EMP table.
Alternatively, assume that the ACCOUNTS_STAFF view is defined by the following statement (that is, excluding the DEPTNO column): CREATE VIEW Accounts_staff AS SELECT Empno, Ename FROM Emp WHERE Deptno = 10 WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst; Considering this view definition, you can update the EMPNO or ENAME fields of existing records, but you cannot insert rows into the EMP table through the ACCOUNTS_STAFF view because the view does not let you alter the DEPTNO field. Loc IN ('HYD', 'BOM', 'DEL'); then there are restrictions on modifying either the EMP or the DEPT base table through this view.
Insert, Update and Delete SQL Views You can use the "OR Replace" option.
If the view exists it will be replaced with the new definition or a new view will be created.
To alter the definition of a view, you must replace the view using one of the following methods: For example, assume that you create the ACCOUNTS_STAFF view, as given in a previous example.