How can I edit the columns in an existing view

Question

I have a view with a typo:

CREATE OR REPLACE VIEW "USER"."VW_X" AS (
SELECT id, 
       name, 
       decode(WRONG_COLUMN, 1, 'T', 0, 'F') AS problem
FROM "USER"."TEST");

For some reason using

CREATE OR REPLACE VIEW "USER"."VW_X" AS (
SELECT id, 
       name, 
       decode(RIGHT_COLUMN, 1, 'T', 0, 'F') AS problem
FROM "USER"."TEST");

results in the following message:

Error starting at line : 5 in command -
CREATE OR REPLACE VIEW "USER"."VW_X" AS (
SELECT id, 
       name, 
       decode(RIGHT_COLUMN, 1, 'T', 0, 'F') AS problem
FROM "USER"."TEST")
Error report -
SQL Error: ORA-02449: unique/primary keys in table referenced by foreign keys
02449. 00000 -  "unique/primary keys in table referenced by foreign keys"
*Cause:    An attempt was made to drop a table with unique or
           primary keys referenced by foreign keys in another table.
*Action:   Before performing the above operations the table, drop the
           foreign key constraints in other tables. You can see what
           constraints are referencing a table by issuing the following
           command:
           SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

I didn't even know that a view was a valid target for a reference. So here's the problem:

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "USER"."VW_X";

returns nothing, this:

SELECT a.table_name, 
     a.column_name, 
     a.constraint_name, 
     c.owner, 
     c.r_owner, 
     c_pk.table_name r_table_name, 
     c_pk.constraint_name r_pk, 
     c.status
FROM all_cons_columns a
JOIN all_constraints c 
ON a.owner = c.owner AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk 
ON c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name
WHERE c_pk.table_name = 'VW_X' or c.table_name = 'VW_X';

(a variant on the top answer to this post) returns four constraints all of which with a status of 'DISABLED'.

I have looked into ALTER VIEW (the name sounded pretty promising), but it does not appear to be what I'm looking for.

Any suggestions?

NOTE: It is ~~possible~~ probable that there are some syntax errors in the provided sample code. These are mockups, in the interest of simplicity and out of professional paranoia, about a dozen irrelevant columns have been removed and names have been altered.

EDIT: this is Oracle11g

EDIT: The solution I found was

-- drop view.
DROP VIEW "USER"."VW_X" CASCADE CONSTRAINTS;
commit;

-- create view.
CREATE OR REPLACE VIEW "USER"."VW_X" AS (
SELECT id, 
       name, 
       decode(RIGHT_COLUMN, 1, 'T', 0, 'F') AS problem
FROM "USER"."TEST");
commit;

CASCADE CONSTRAINTS appears to be the missing link. I am pretty sure the constraints are lost, but I don't particularly care.

I've not used FK's on views. But, it looks like the behavior would be similar to tables. You'll need to identify the FK constraints on your view, drop them, alter your view, then recreate the constraints. You'll not be able to do this in a single alter view statement. — unleashed, Mar 22 '17 at 19:11

score 0 · Answer 1 · answered Mar 22 '17 at 04:32

0

ALTER VIEW
AS
SELECT
[new column name] = wrongcolumn

answered Mar 22 '17 at 04:32

Ryan Dooley

224
1
3
16

2

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – Donald Duck Mar 22 '17 at 09:38
if I'm reading this correctly, it looks like you may have this backwards, ALTER VIEW AS SELECT problem = rightcolumn would this work? – chrisgotter Mar 22 '17 at 15:29
and did you mean `ALTER VIEW VW_X AS` ? – chrisgotter Mar 22 '17 at 15:43
also, I am using Oracle11g, I am 90% sure that this is not supported – chrisgotter Mar 22 '17 at 16:00
OK, this is MS SQL – Ryan Dooley Mar 22 '17 at 23:45

score 0 · Answer 2 · answered Mar 22 '17 at 19:19

Here is a solution that would drop the FK, create the view, then recreate the FK

  -- create view with unique constraint
  create or replace view v_foo 
  (id unique disable novalidate, val)
  as 
  select id, val from foo;

  -- create second view
  create or replace view v_bar
  (id, val)
  as
  (select id, val from bar);

  -- add FK to second view
  alter view v_bar
  add constraint v_bar_ref
  foreign key (id) references v_foo(id)
   disable novalidate;


  -- add a column, this fails b/c of FK constraint 
  create or replace view v_foo 
  (id unique disable novalidate, val, dummy)
  as 
  select id, val, 1 from foo;

  -- remove constraint
  alter view v_bar
  drop constraint v_bar_ref;

  -- make changes to view
  create or replace view v_foo 
  (id unique disable novalidate, val, dummy)
  as 
  select id, val, 1 from foo;

  -- recreate FK constraint
  alter view v_bar
  add constraint v_bar_ref
  foreign key (id) references v_foo(id)
   disable novalidate;

this appears to be an excellent solution, but one of the problems is that I was unable to figure out which fk's it was even talking about. That said, I ended up finding an easier acceptable solution. — chrisgotter, Mar 23 '17 at 15:49

How can I edit the columns in an existing view

2 Answers2