Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

index is constraint?

4 posts in Performance and Tuning Last posting was on 2012-10-05 11:30:16.0Z
rick_806 Posted on 2012-09-30 15:02:58.0Z
Sender: 215.50685bdc.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.performance+tuning
Subject: index is constraint?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50685f22.24d.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 30 Sep 2012 08:02:58 -0700
X-Trace: forums-1-dub 1349017378 172.20.134.41 (30 Sep 2012 08:02:58 -0700)
X-Original-Trace: 30 Sep 2012 08:02:58 -0700, 172.20.134.41
Lines: 7
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13267
Article PK: 91758

I want to modify a index XPKTABLE1. When I try to drop this
index, I get an error saying it is a constraint. Since it is
not a constraint on TABLE1, I assume it is a foreign key on
some other table? How do I find that table?

thx,
rick_806


Rob V Posted on 2012-09-30 20:06:09.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: index is constraint?
References: <50685f22.24d.1681692777@sybase.com>
In-Reply-To: <50685f22.24d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5068a631@forums-1-dub>
Date: 30 Sep 2012 13:06:09 -0700
X-Trace: forums-1-dub 1349035569 172.20.134.152 (30 Sep 2012 13:06:09 -0700)
X-Original-Trace: 30 Sep 2012 13:06:09 -0700, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13268
Article PK: 91757


On 30-Sep-2012 17:02, rick_806 wrote:
> I want to modify a index XPKTABLE1. When I try to drop this
> index, I get an error saying it is a constraint. Since it is
> not a constraint on TABLE1, I assume it is a foreign key on
> some other table? How do I find that table?
>
> thx,
> rick_806
>

Run:

sp_helpcontraint TABLE1

If it is an PK-FK constriant, this will give you details.

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


rick_806 Posted on 2012-10-03 19:22:07.0Z
Sender: 3ce.506c8ac1.1804289383@sybase.com
From: rick_806
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: index is constraint?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <506c905f.44e.1681692777@sybase.com>
References: <5068a631@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 3 Oct 2012 12:22:07 -0700
X-Trace: forums-1-dub 1349292127 172.20.134.41 (3 Oct 2012 12:22:07 -0700)
X-Original-Trace: 3 Oct 2012 12:22:07 -0700, 172.20.134.41
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13270
Article PK: 91761


> On 30-Sep-2012 17:02, rick_806 wrote:
> > I want to modify a index XPKTABLE1. When I try to drop
> > this index, I get an error saying it is a constraint.
> > Since it is not a constraint on TABLE1, I assume it is a
> > foreign key on some other table? How do I find that
> table? >
> > thx,
> > rick_806
> >
>
> Run:
>
> sp_helpcontraint TABLE1
>
> If it is an PK-FK constriant, this will give you details.
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide"
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------

That sproc shows 3 tables that reference the column in the
index, but I do not see anyway to detemine which constraint
the index is associated with? Do I need to just drop any
constraint on any table that references this column in the
primary table in order to add a column to the index? To be
clear, I want to add two columns to this index to get a
"covered" index.

Where is the definition of this constraint, how do I find
it?
The DDL for these tables does not show it as XPKTABLE1

The error I get when I try to drop this index is:

3712, Sev 16, State 2, Cannot drop XPKTABLE1 because it
still has referntial integrity constraints


Rob V Posted on 2012-10-05 11:30:16.0Z
From: Rob V <rob@sypron.nl>
Reply-To: rob@sypron.nl
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: index is constraint?
References: <5068a631@forums-1-dub> <506c905f.44e.1681692777@sybase.com>
In-Reply-To: <506c905f.44e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <506ec4c8@forums-1-dub>
Date: 5 Oct 2012 04:30:16 -0700
X-Trace: forums-1-dub 1349436616 172.20.134.152 (5 Oct 2012 04:30:16 -0700)
X-Original-Trace: 5 Oct 2012 04:30:16 -0700, vip152.sybase.com
Lines: 80
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13276
Article PK: 91767


On 03-Oct-2012 21:22, rick_806 wrote:
>> On 30-Sep-2012 17:02, rick_806 wrote:
>>> I want to modify a index XPKTABLE1. When I try to drop
>>> this index, I get an error saying it is a constraint.
>>> Since it is not a constraint on TABLE1, I assume it is a
>>> foreign key on some other table? How do I find that
>> table? >
>>> thx,
>>> rick_806
>>>
>>
>> Run:
>>
>> sp_helpcontraint TABLE1
>>
>> If it is an PK-FK constriant, this will give you details.
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Professional DBA for Sybase ASE, IQ, Replication
>> Server
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
>> ASE" "The Complete Sybase IQ Quick Reference Guide"
>> "The Complete Sybase ASE Quick Reference Guide"
>> "The Complete Sybase Replication Server Quick Reference
>> Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>
> That sproc shows 3 tables that reference the column in the
> index, but I do not see anyway to detemine which constraint
> the index is associated with? Do I need to just drop any
> constraint on any table that references this column in the
> primary table in order to add a column to the index? To be
> clear, I want to add two columns to this index to get a
> "covered" index.
>
> Where is the definition of this constraint, how do I find
> it?
> The DDL for these tables does not show it as XPKTABLE1
>
> The error I get when I try to drop this index is:
>
> 3712, Sev 16, State 2, Cannot drop XPKTABLE1 because it
> still has referntial integrity constraints
>

I don't think I understand the exact problem, so it would help if you
posted your exact SQL command and the error message you get, plus any
output (like the sp_helpconstraint info).

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Professional DBA for Sybase ASE, IQ, Replication Server

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE"
"The Complete Sybase IQ Quick Reference Guide"
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------