Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Inserting data into view

    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

  2. #2
    Join Date
    Jul 2003
    Location
    india
    Posts
    15

    Re: Inserting data into view

    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You can also define a column contraint on the table.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks neelamchalam, that is solution I have been looking for.
    Thank you Markus_A. That is also a good point.

    Thanks,
    Grofaty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •