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

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!
PieterI 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
>|||Well, the problem is that the underlying table doesn't have a Primary Key,
but in access it should.
I'm not allowed to change the udnerlying table, so I should put something on
theView...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> 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
> >
> >
>|||The option for "putting something on the view" you find in my prior post. But the bigger question is
why the table doesn't have a PK...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> Well, the problem is that the underlying table doesn't have a Primary Key,
> but in access it should.
> I'm not allowed to change the udnerlying table, so I should put something on
> theView...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> 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
>> >
>> >
>>
>|||I don't know why it doesn't have a Primary Key: It's a table of a big
Accountant Software, so I can't change anything to the table... :-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> The option for "putting something on the view" you find in my prior post.
But the bigger question is
> why the table doesn't have a PK...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> > Well, the problem is that the underlying table doesn't have a Primary
Key,
> > but in access it should.
> > I'm not allowed to change the udnerlying table, so I should put
something on
> > theView...
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> 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
> >> >
> >> >
> >>
> >>
> >
> >
>|||I see :-(. I suggest you post this to an Access forum to see whether you can define in Access what
column define uniqueness.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
>I don't know why it doesn't have a Primary Key: It's a table of a big
> Accountant Software, so I can't change anything to the table... :-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
>> The option for "putting something on the view" you find in my prior post.
> But the bigger question is
>> why the table doesn't have a PK...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
>> > Well, the problem is that the underlying table doesn't have a Primary
> Key,
>> > but in access it should.
>> > I'm not allowed to change the udnerlying table, so I should put
> something on
>> > theView...
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
>> >> 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
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||Well I did, and I just got the answer!
It shoudl have been the nicest oslution if I could implement it on the view,
but now I have some VBA that puts the index on the linked view after I
refreshed everything... It seemsto work fine.
Thanks a lot for the effort!
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOSsf5A6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> I see :-(. I suggest you post this to an Access forum to see whether you
can define in Access what
> column define uniqueness.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:e9Y2cqA6EHA.4028@.TK2MSFTNGP15.phx.gbl...
> >I don't know why it doesn't have a Primary Key: It's a table of a big
> > Accountant Software, so I can't change anything to the table... :-)
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OuPd7K45EHA.1300@.TK2MSFTNGP14.phx.gbl...
> >> The option for "putting something on the view" you find in my prior
post.
> > But the bigger question is
> >> why the table doesn't have a PK...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> http://www.sqlug.se/
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:OIV9Vr35EHA.828@.TK2MSFTNGP14.phx.gbl...
> >> > Well, the problem is that the underlying table doesn't have a Primary
> > Key,
> >> > but in access it should.
> >> > I'm not allowed to change the udnerlying table, so I should put
> > something on
> >> > theView...
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> > message news:uVHfWi35EHA.272@.TK2MSFTNGP10.phx.gbl...
> >> >> 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>