PRIMARY KEY ON A VIEW - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

PRIMARY KEY ON A VIEW

Author

Topic

vishalj
Starting Member

32 Posts

Posted - 2006-05-22 : 14:35:41

I need to create 2 views and then join them to get all the columns into another view.

can i create primary keys on both the views
both views have vendor_id as one column
that is the only column common in them

view 1 has vendor_id, address, startdate, enddate, city, state

vendor_id address startdate enddate city state
1 abc may 10 june 10 Kansas MO

view 2 has vendor_id, service offered, employee name

view 2 will have multiple services and each vendor ID will have multiple employee who will offer multiple services as well so there will be repititons like this:

Vendor ID EmployeeName Service Offered
1 Albert Tech Support
1 Albert Desktop Technician
1 Albert Lotus Notes Admin
2 Tony Developer
2 Tony Admin

which would be the best join to use. HOW DO I CHANGE MY VIEWS TO MAKE VENDOR_ID as primary keys in them

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-22 : 14:37:53

You can not add a primary key constraint to views. You can join columns though. In the join condition, you do not have to use a primary key constraint.

SELECT ...
FROM view1 v1
INNER JOIN view2 v2
ON v1.vendor_id = v2.vendor_id

The type of join to use is based upon your data requirement. In the example above, I am using an inner join. But you'd have to tell us what you want in order for us to help.

Tara Kizer
aka tduggan

vishalj
Starting Member

32 Posts

Posted - 2006-05-22 : 14:43:29

this is what i did but the join gives some data descripency

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-22 : 14:46:07

You'll need to explain what you mean by data discrepancy in order for us to help. Please us show a data example of what you mean. Also, we'll need to see the query that you are using that shows this data issue as well as the code in both views.

Tara Kizer
aka tduggan

vishalj
Starting Member

32 Posts

Posted - 2006-05-22 : 14:56:25

I cannot post data or actual query
I think the example i gave is the closest one
what its doing is in the result is:
for vendor ID 1 it returns values of vendor ID 2 as well
so i though if its possible to create a primary key on a view (which i think u can in oracle not sure about SQL)
then that might not be the problem

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-22 : 15:03:22

You can not put a primary key on a view in SQL Server.

There is very little that we can do to help you unless you post an example that more closely illustrates your problem.

If you only want vendorID 1 returned, then you need to add that to your WHERE clause.

Tara Kizer
aka tduggan

vishalj
Starting Member

32 Posts

Posted - 2006-05-22 : 15:07:16

ok i create a view like

create view vendor_view
select v.vendor_id, c.city, st.state, ol.address
from Vendors V
left outer join........
right outer join .........
where..........
on........

where do i put the constraint to get v.vendor_id as primary key for that view

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-22 : 15:10:21

Views can not have primary key constraints, so there is no place to put that. If you are instead referring to the ON condition of your join, then you put it in the ON condition, just like the in the example that I provided.

Doesn't your Vendor table already have the vendorID column as the PK constraint?

You'll need to provide a data example of what you are trying to do in order for us to help. Posting partial code is not going to help us help you.

Tara Kizer
aka tduggan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-22 : 20:58:27

Why do you need a primary key on a view ?
A view is just a stored query it is not a physical table.

Is it because you are getting duplicate records from your view ?
Please post your table structure, view & some sample data with the result that you want.

wilsonds
Starting Member

1 Post

Posted - 2010-07-16 : 12:22:19

We also have a need for a primary key on a view. We have a GIS application that needs a primary key when working with database objects in order for selection to work. Oracle allows us to create a primary key on a view and we have no problems. SQL Server however does not and views are the most efficient means of aggregating data. This would be a great addtion to SQL Server.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-16 : 12:47:50

wilsonds, for SQL Server, you can use an indexed view with a unique clustered index: http://weblogs.sqlteam.com/tarad/archive/2009/11/09/Multiple-ldquoclusteredrdquo-indexes-on-a-SQL-Server-table.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JoeAtDBSA
Starting Member

2 Posts

Posted - 2010-07-26 : 16:43:45

quote:
Originally posted by wilsonds

We also have a need for a primary key on a view. We have a GIS application that needs a primary key when working with database objects in order for selection to work. Oracle allows us to create a primary key on a view and we have no problems. SQL Server however does not and views are the most efficient means of aggregating data. This would be a great addtion to SQL Server.

You could use tabular SQL functions...I have had to resort to this method to support updates on legacy projects...below is a small sample:

Alter Function fn_MyTable()

Returns @tbl TABLE (ID int primary key NOT NULL, ValueField varchar(50) NULL)

As
Begin

Insert Into @tbl(ID, ValueField)
Select MyTable_ID, MyTable_value From MyTable

Return
End

Joseph E. Foster
Daniel B. Stephens & Associates, Inc.
www.dbstephens.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-26 : 17:31:52

quote:
Originally posted by JoeAtDBSA

quote:
Originally posted by wilsonds

We also have a need for a primary key on a view. We have a GIS application that needs a primary key when working with database objects in order for selection to work. Oracle allows us to create a primary key on a view and we have no problems. SQL Server however does not and views are the most efficient means of aggregating data. This would be a great addtion to SQL Server.

You could use tabular SQL functions...I have had to resort to this method to support updates on legacy projects...below is a small sample:

Alter Function fn_MyTable()

Returns @tbl TABLE (ID int primary key NOT NULL, ValueField varchar(50) NULL)

As
Begin

Insert Into @tbl(ID, ValueField)
Select MyTable_ID, MyTable_value From MyTable

Return
End

Joseph E. Foster
Daniel B. Stephens & Associates, Inc.
www.dbstephens.com

Out of curiosity, can you describe the circumstance you needed to do this?

I was looking over this and this seems like a strange thing to do if you can select right out of a table. It appears that MyTable_ID is already unique. But, perhaps the sample is too simple to be representative of the actual issue or I'm missing the point..? :)

JoeAtDBSA
Starting Member

2 Posts

Posted - 2010-07-27 : 11:59:15

quote:
Originally posted by Lamprey
...
I was looking over this and this seems like a strange thing to do if you can select right out of a table. It appears that MyTable_ID is already unique. But, perhaps the sample is too simple to be representative of the actual issue or I'm missing the point..? :)

Without getting into anything proprietary, let me say there were instances when applications needed to see a primary key, the functions written were a bit more complicated that the one I showed as an example.

I think, based on the sample provided, I would likely choose to change that to an inline function for performance were I actually going to implement something like that.

Having said that, we all just love legacy support, don't we :)

Joseph E. Foster
Daniel B. Stephens & Associates, Inc.
www.dbstephens.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources