共2表和一个view
往view俩面insert数据报错?CREATE TABLE T_MESSAGE
(
ID VARCHAR2(32 CHAR),
CONTENT VARCHAR2(222 CHAR),
USER_ID VARCHAR2(32 CHAR)
)
CREATE TABLE T_USER
(
ID VARCHAR2(32 CHAR),
USERNAME VARCHAR2(32 CHAR),
PASSWORD VARCHAR2(32 CHAR)
)
CREATE OR REPLACE VIEW MYVIEW
(USERID, MID, MUSERID, NAME, PWD,
CONTENT)
AS
select t_user.id,t_message.id,user_id,username,password,t_message.content from t_user,t_message where t_user.id=t_message.user_id
为什么我insert into myview values('zzz','zzz','zzz','zzz','zz','zzz');
insert into myview(userid) values('z')
不行?
ORA-01779: 无法修改与非键值保存表对应的列
往view俩面insert数据报错?CREATE TABLE T_MESSAGE
(
ID VARCHAR2(32 CHAR),
CONTENT VARCHAR2(222 CHAR),
USER_ID VARCHAR2(32 CHAR)
)
CREATE TABLE T_USER
(
ID VARCHAR2(32 CHAR),
USERNAME VARCHAR2(32 CHAR),
PASSWORD VARCHAR2(32 CHAR)
)
CREATE OR REPLACE VIEW MYVIEW
(USERID, MID, MUSERID, NAME, PWD,
CONTENT)
AS
select t_user.id,t_message.id,user_id,username,password,t_message.content from t_user,t_message where t_user.id=t_message.user_id
为什么我insert into myview values('zzz','zzz','zzz','zzz','zz','zzz');
insert into myview(userid) values('z')
不行?
ORA-01779: 无法修改与非键值保存表对应的列
SQL> INSERT INTO test1 VALUES (1,0);
SQL> INSERT INTO test1 VALUES (2,0);SQL> CREATE TABLE test2 ( id integer, num integer, upd integer );
SQL> INSERT INTO test2 VALUES (1,10, 0);
SQL> INSERT INTO test2 VALUES (2,20, 1);SQL> UPDATE ( SELECT t1.id id1, t1.num num1, t2.id id2, t2.num num2 FROM test1 t1, test2 t2 WHERE t1.id=t2.id AND t2.upd=1) SET num1=num2;
ORA-01779: cannot modify a column which maps to a non key-preserved tableSQL> CREATE UNIQUE INDEX test2_idx_001 ON test2 (id);
SQL> UPDATE ( SELECT t1.id id1, t1.num num1, t2.id id2, t2.num num2 FROM test1 t1, test2 t2 WHERE t1.id=t2.id AND t2.upd=1) SET num1=num2;
1 row updated.
Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.
Action: Modify the underlying base tables directly
2. 如果涉及多个表的话,被(涉及到的)view列所映射table列(或列的组合)必须是有主健约束的
3. 强制执行,方法是加上hint /*+ BYPASS_UJVC */
多谢,学习了
insert into T_MESSAGE values('zzz','zzz','zzz');
insert into t_user values('zzz','zzz','zzz');构建视图
CREATE OR REPLACE VIEW MYVIEW
(USERID, MID, MUSERID, NAME, PWD,
CONTENT)
AS
select t_user.id,t_message.id,user_id,username,password,t_message.content from t_user,t_message where t_user.id=t_message.user_id;查看结果
select * from myview;建议写个触发器实现