Partitioned View is not updatable. Need help!!

[kishore.kr]

Hi All,
In the database(SQL Server 2000), we are using horizontal partitioned view(nearly 15 tables are joined with UNION ALL). Everything were fine, until we changed the CHECK constriant of one table . Now the view has become not updatable. It is giving error as PARTITIONED COLUMN NOT FOUND
We tried to drop the view and re-create it, drop constriant->view and create constriant->view. None worked. The constraint added is very much correct.
I think there is some logical stuff regarding partitioned view, which I am not aware of.
Can anyone guide me or help me regarding this. I have searched a lot in the net, still not able to find any solution for this.

Thank you.,

[szlamany]

This link gives the rules for UPDATABLE PARTIONED VIEWS...

http://msdn2.microsoft.com/en-us/lib...1(SQL.80).aspx

Are you using UNION ALL??

[kaffenils]

Updatable partitioned views requires that the PK of the underlying tables have a check constraint that defines unique values for each table. That means that the constraints should prevent the same PK value to be inserted into more than one table in the view.

[szlamany]

Kaf...

I'm curious - what would the syntax be to create a UNIQUE constraint on two different tables?

Let's say I have Table1 (with PriKey1) and Table2 (with PriKey2).

Thanks!

[kaffenils]

ALTER TABLE Table1 ADD CONSTRAINT PriKey1_check 
CHECK (priColumn1 BETWEEN 1 AND 1000)

ALTER TABLE Table2 ADD CONSTRAINT PriKey2_check
CHECK (priColumn2 BETWEEN 1001 AND 2000)

This constraint enusres that the PK in the two tables cannot have the same value. SQL Server looks for and analyzes CHECK constraints and verifies if the check contraints will guarantee different PK values across the tables participating in the view.

[szlamany]

Oh - so we are not really referencing each table from the other - we are simply creating "distinct" ranges of PK's on each table.

Interesting...

[kaffenils]

Oh - so we are not really referencing each table from the other - we are simply creating "distinct" ranges of PK's on each table.

Interesting...

Correct. That is how SQL Server knows what table to do an insert/update/delete when you insert/update/delete data in the view. The view can be partitioned across multiple servers.

Quite clever, but I have never actually used it. Just something I had to learn when i took the SQL Server Development MCP exam.