January 28, 2009 at 9:09 am
I have a primary table (A) having one to one relationship with other table (B) basically table B has columns which should belong to table A but for reasons not to over populate columns in Table A we made the decision to put them in Table B with one to one relationship.
We were using an view to update Table A but now we have an requirement to update fews columns in Table B too. So how to design the updatable view? Or we need to redesign the table A?
January 28, 2009 at 9:31 am
An updatable view must reference only one base table however you can create Partitioned views which can update more than one table but all tables must be UNION ALL compatible. Run a search for UNION requirements in SQL Server BOL because you may need to change your tables definitions and check below for the requirements to create partitioned views.
http://msdn.microsoft.com/en-us/library/ms187956.aspx
Kind regards,
Gift Peddie
January 28, 2009 at 9:39 am
I performed the following sample & found that a view can have multiple tables but while performing updates you can use only columns from a single table. Pl. see the code below.
create table abc (id int, email varchar(100), uname varchar(100)
CONSTRAINT abc_PK PRIMARY KEY NONCLUSTERED (id)
)
create table def (id int, fname varchar(100), sname varchar(100)
CONSTRAINT def_PK PRIMARY KEY CLUSTERED (id)
)
ALTER TABLE def ADD CONSTRAINT abc_def_FK1
FOREIGN KEY (id)
REFERENCES abc(id)
-----------------------------
insert into abc
select 1, 'a@a.com', 'a@a.com'
insert into abc
select 2, 'b@b.com', 'b@b.com'
insert into abc
select 3, 'c@c.com', 'c@c.com'
-----------------------------
-----------------------------
insert into def
select 1, 'fa', 'sa'
insert into def
select 2, 'fb', 'sb'
insert into def
select 3, 'fc', 'sc'
-----------------------------
select * from abc
select * from def
create or alter view vsomething
as
select a.id, email, uname, fname from abc a, def b
where a.id = b.id
select * from vsomething
update vsomething set email = 'b@b.com' where id = 2
update vsomething set fname = 'fb2' where id = 2
January 28, 2009 at 9:50 am
You are saying the same thing I am saying because you can create a view to reference many tables but to be updatable it must reference only one table. The little sample you are running may not work in production because it takes planning to create a partitioned view.
Kind regards,
Gift Peddie
January 28, 2009 at 11:09 am
I dont understand the part "Planning". Can you please help me out here.
Whether inner join or any other join would work if this is not going to be a partitioned view?
January 28, 2009 at 11:23 am
You cannot have any JOIN because that will prevent the update to the base tables. Look in the link I posted there are sample codes showing all tables are connected by UNION ALL operator so you need to make your base tables all UNION compatible that is what I mean by planning.
Kind regards,
Gift Peddie
January 28, 2009 at 11:58 am
ok. my problem is my Table A has 10 columns & Table B has 20 columns. And, its a one to one relationship from Table A to Table B.
In my view I would select 29 columns & other missing column is primary key from Table B which I wouln't select in the view & I wont perform update operation on it either.
Can I make this view still updatable without having partitioned view by updating only columns from 1 table during single update operations?
i.e. update tablea.col1
update tableb.col11
In order to do this whether my view should be a partitioned view or it can be an ordinary view?
January 28, 2009 at 12:13 pm
Your tables are not UNION compatible because you have 10 and 20 columns which means both tables are not equal. I don't think you can create partitioned views and I am also not aware of regular view that can update more than one base table.
The reason ANSI SQL requirement is updatable view must reference only on base table but RDBMS vendors like Microsoft created the partitioned View to update many base tables in one query. Microsoft sells packaged software like Biztalk that update more than ten tables in one query.
Kind regards,
Gift Peddie
January 28, 2009 at 3:33 pm
as per the above sample code the update views works with multiple tables when columns from one table alone is updated in single statement.
But, what I'm not sure is whether this is acceptable process.
January 28, 2009 at 3:45 pm
That is because the relational engine is just running your code as a view from one base table, again I don't think you should run it because the RDBMS vendors did all the math needed to implement the partitioned view without data integrity issues. So you may run into data integrity issues because there are many existing known issues with views.
Kind regards,
Gift Peddie
January 28, 2009 at 4:20 pm
It is perfectly valid to create an updatable view on two separate tables. There are certain rules that need to be followed, but you can definitely create a veiw that joins multiple tables and is updatable.
If you cannot work within those rules - there is always the option of setting instead of triggers on the view where you have more control over how the other tables get updated.
Refer to the following article in BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/aecc2f73-2ab5-4db9-b1e6-2f9e3c601fb9.htm
Scroll down to the section on Updatable Views which spells out all of the requirements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 28, 2009 at 4:36 pm
(Updatable Views
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.)
This come from the docs link I posted in the first reply to this thread and these are just the start of the restrictions which can fill half a page.
And my references to JOINs was relevant to partitioned views not updatable views.
Kind regards,
Gift Peddie
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply