Inserting data into view

grofaty · #1 (**permalink**) 10-08-03, 07:33

Hi,

My system: db2 v7.1 fp 5 on Windows 2000

1. I have table "x". There is no Primary key on this table!

Code:

CustomerID  CustomerName
1           'Customer A'
2           'Customer B'

2. I created view:
create view y as select * from x where CustomerID <> 1

3. I can make "select":
select * from y
Returns:

Code:

CustomerID  CustomerName
2           'Customer B'

4. Why does this sql works:
insert into y (1,'Customer Z')

I am inserting the data to the view, and data are inserted to the table. Why doesn't DB2 prevents to insert into view - on view is restriction "CustomerID <> 1"?

Thanks,
Grofaty

neelamchalam · #2 (**permalink**) 10-08-03, 08:37

Quote:

Originally posted by grofaty
Hi,

My system: db2 v7.1 fp 5 on Windows 2000

1. I have table "x". There is no Primary key on this table!

Code:

CustomerID  CustomerName
1           'Customer A'
2           'Customer B'

2. I created view:
create view y as select * from x where CustomerID <> 1

3. I can make "select":
select * from y
Returns:

Code:

CustomerID  CustomerName
2           'Customer B'

4. Why does this sql works:
insert into y (1,'Customer Z')

I am inserting the data to the view, and data are inserted to the table. Why doesn't DB2 prevents to insert into view - on view is restriction "CustomerID <> 1"?

Thanks,
Grofaty

Dear Grofaty,

1.what DB2 is doing correct, we have restriction with view not with the base table

2. after inserting (1,'Customer Z') , if you select you will get the same result which you got previously

3. if you want to restrict VIEW itself we can use the
WITH CHECK OPTION.

CREATE VIEW y AS SELECT * from x where CustomerID <> 1 WITH CHECK OPTION;

this view won't allow to update view or base table with (1,'Customer Z') such a data which is violating the CHECK OPTION.

4. if there is any thing else so please inform me so i can get more knowledge.

Thanking you,
Chalam N