Re: add a Primary Key (or something like that) to a View

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 12/21/04 

Date: Tue, 21 Dec 2004 17:17:41 +0100

I would assume that Access would pick up the underlying table's primary key definition. Do you have
such?
You can't define a PK on a view as the view doesn't store any data in itself. You can, in some
cases, make the view with SCHEMABINING and create a unique index on the view. The question is, of
course, whether Access would pick up on that. But that is a question for the Access experts. 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@hotmail.com> wrote in message 
news:Opndze35EHA.3828@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> I have a View like this in my SQL Server 2000:
> CREATE VIEW vw_oas_linkhead
> AS
> SELECT     *
> FROM         oas_linkhead
> WHERE     (cmpcode = SUSER_SNAME())
> WITH CHECK OPTION
>
> The problem is: When, inside an Access-applciation, I put a link to this
> View, I can't delete records from the View unless I have a Primary Key
> defined on the View.
>
> I can define that Primary Key in Access, but when I refresh the Linked Table
> (View) the Primary Key disspaears. This doesn't happen when the Table itself
> has a Primary Key on the SQL Server. So I wouldl ike to know if there is a
> possiblity to put a Primary Key on a View in Sql Server (I thought this
> isn't possible?) or to kind of simulate this on another way (with a check
> constraint, ... ?)?
>
> Does anybody has any idea?
>
> Thanks a lot!
>
> Pieter
>
>

Relevant Pages

  • Re: Clustered Index question
    ... Columnist, SQL Server Professional ... If I keep the primary key, ... >select packet_time,packet_data from packet where ... >have the clustered index (either on PACKET_TIME or on ...
    (microsoft.public.sqlserver.programming)
  • A pk is *both* a physical and a logical object.
    ... Primary key is a logical constraint. ... but SQL Server does not allow you to create a unique or primary ... constraints) by indexing is purely *platform dependent*. ... PKs are part of the ANSI SQL standard *for db implementations*. ...
    (comp.databases.theory)
  • Re: Re-Seed in SQL Server
    ... Add a new field to the table that holds the Primary Key. ... or something like that and make it an Autonumber field and a Primary Key. ... If the table is already on the SQL Server you could try the below. ... that your Autonumber field in the Access Database has gotten corrupted. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Table Design Advice
    ... I will be creating db in SQL server though. ... MaterialCost (Currency) ... CONSTRAINT pk_products PRIMARY KEY, ...
    (microsoft.public.sqlserver.setup)
  • Re: Primary key error
    ... I read the data from the sql server with a basic SELECT command. ... backup for each row where the column YPCODE has the value of the read data ... For the tables where YPcode is the primary key, this is not a problem, but I ... Cannot insert duplicate key in object 'dbo.OverallStatus'. ...
    (microsoft.public.sqlserver.server)