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.
| Author |
Topic |
|
robg69
Starting Member
41 Posts |
 Posted - 2003-09-04 : 20:05:31
|
| Does anyone know how you can add a primary key/unique identifier on a view? I have an access db that links to my SQL Server. The problem is I have a view as table, and access won't let you update it b/c it doesn't have a key (I'm assuming, if I choose a key, you can update).can you do something similar in SQL to this in Oracle:alter view table1add constraint table1_pk primary key (tID, StartDate) |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-04 : 20:48:03
|
| when you link to the view, access asks you to pick the primary key of the view.Just pick was it logically should be; Access wants to know so that the JET engine can pass updates to the linked table/view.for example, if your view is of the employee's table and that table has a PK of "EmpID", pick that in your view.- Jeff |
 |
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-09-05 : 08:56:13
|
| Yea, I was trying to do it in code. So after some searching, I found out that you can create and index on the linked table through code. So it's working now, but It got me thinking about how to do this with just SQL Server. Can you just create an index on the view? Would that be the same thing?Thanks |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-05 : 11:42:57
|
Well, the concept of Indexed views in SQL Server is a little different from what you'd imagine, you can read all about it in Books online. But to answer your question, no you can't create a primary key on a view, it must be provided by one of the tables included in the view. Look at this thread it might give you a better idea: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16519Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-09-05 : 12:44:38
|
| Ok, so I guess the only way to do this is with an "INSTEAD OF" trigger?Thanks. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-06 : 03:36:05
|
It wasn't really what I was trying to imply, I was just trying to draw your attention to the fact that you can't really create a primary key on a view. If you need to be able to update a view, SQL Server must know which column uniquely identifies that row. The INSTEAD OF triggers were recommended in that specific case, especially if you have many tables in the view. But Access makes it easier for you by asking you to pick the key column when you link the view.Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
robg69
Starting Member
41 Posts |
Posted - 2003-09-08 : 11:05:01
|
| Well I totally understand why you can't put a key on a view, esp. if it has several different tables with different keys. But if you do only have a view with 1-2 tables and you want to update it, you should be able to set an index on it, or create some method to ensure uniqueness (to me anyway). I did find some info in BOL for an indexed view, so I'm looking into that. It seems to be what I was looking for. Thanks. |
|
|
|
|
|
|
|
|
primary key on a view?