Integrity between table and View

It is possible to drop the table without dropping the view referencing
it. How do I force integrity?

MadhivananNo you can't. What do you mean by 'force integrity'? A view is always kept
in sync with the table(s) on which it is based.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1121175173.490148.91840@.g43g2000cwa.googlegro ups.com...
> It is possible to drop the table without dropping the view referencing
> it. How do I force integrity?
> Madhivanan|||Think about this for two seconds. If there is no base table, how would
you build the VIEW?|||Yes, you can drop a table when referencing views exist unless views are
created with the WITH SCHEMABINDING option. The view will be invalid in
that case. You can specify WITH SCHEMABINDING on your views to prevent
invalid views.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1121175173.490148.91840@.g43g2000cwa.googlegro ups.com...
> It is possible to drop the table without dropping the view referencing
> it. How do I force integrity?
> Madhivanan|||What do you mean by 'force integrity'?

I want to get the error, This table is referenced by a view

Think about this for two seconds. If there is no base table, how would

you build the VIEW?

I think you didnot understand my question

Yes, you can drop a table when referencing views exist unless views are

created with the WITH SCHEMABINDING option.

Thanks

Madhivanan|||No error will be generated when you drop a table referenced in a view
unless the view is indexed. Maybe you can index your view(s), although
in my opionion you shouldn't need to do so just for this. Surely you
wouldn't drop a table in production without first testing your code? If
the table or view is used in code then testing will show an error. If
the table or view isn't used then it isn't relevant. MS could have
implemented it better but unfortunately they didn't.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> No error will be generated when you drop a table referenced in a view
> unless the view is indexed. Maybe you can index your view(s), although
> in my opionion you shouldn't need to do so just for this. Surely you
> wouldn't drop a table in production without first testing your code? If
> the table or view is used in code then testing will show an error. If
> the table or view isn't used then it isn't relevant. MS could have
> implemented it better but unfortunately they didn't.

You don't need to go all the way and index the view. As Dan said,
what you need is WITH SCHEMABINDING. Try this:

CREATE TABLE mytable (a int NOT NULL)
go
CREATE VIEW myview WITH SCHEMABINDING AS
SELECT a FROM mytable
go
DROP TABLE mytable -- Fails

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ernold,

I get the error
Server: Msg 4512, Level 16, State 3, Procedure myview, Line 2
Cannot schema bind view 'myview' because name 'mytable' is invalid for
schema binding. Names must be in two-part format and an object cannot
reference itself.

Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> I get the error
> Server: Msg 4512, Level 16, State 3, Procedure myview, Line 2
> Cannot schema bind view 'myview' because name 'mytable' is invalid for
> schema binding. Names must be in two-part format and an object cannot
> reference itself.

Oops! I tested, got an error messages, was contented with that. I failed
to read the error message, though. OK, try this then:

CREATE TABLE mytable (a int NOT NULL)
go
CREATE VIEW myview WITH SCHEMABINDING AS
SELECT a FROM dbo.mytable
go
DROP TABLE mytable -- Fails

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||No it works well
Thanks Erland

Madhivanan