Some questions about SQL views 1

[davideart]

Hy everyobody

I'm not very used with SQL Enterprise Manager, and I've got some questions:

1) How can I include views in database diagrams? Enterprise manager seems to show only tables.

2)How can I create a foreign key in a table referencing a column in a view? (I can't reference the table the view come from because it's in a different database)

Thank you for your time

David

 

[fluteplr]

Sorry about the following answers .

1. You can't.
2. you can't create a relation to a view. Have you tried creating the relation in code referencing the table in the other database as database.owner.table?

 

[dbmsguy]

keep in mind that a 'view' is NOT an actual physical object like a table. there is no actual structure to it. A view is just that a view of data, just like a window. in the view, you specify what that view will contain with a select statement.

data is only materialized when you select data from it. In other words, if you have a very complex statement in a view, it could take a long time to get data back. Paul

http://www.dbmsguy.com

 

[tlbroadbent]

You cannot create a foreign key on a View but you can join Views to Tables or other Views. Foreign keys are not required to do create Joins. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[davideart]

Ok, you were all very claryfing and helpful. Thank you very much.David

 

[davideart]

I've tried to create a reference from my table to the table in the other database my view come from, but the script:

alter table <tablename> add constraint <constraintname> foreign key (<columnname) references <otherDBname>.<owner>.<tablename>(<columnname>)

returned the followin' error:

Cross-database foreign key references are not supported.

So, it does not seem possible to reference table in different databases....

David

 

[tlbroadbent]

You can't reference a table in another database with a foreign key contraint. However, you can join tables from different databases.

Select a.col1, b.col2
From db1.dbo.tableA As a
Inner Join db2.dbo.tableB As b
On a.fkcol=b.pkcol Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.