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.

how to convert column to identity

3 posts in General Discussion Last posting was on 2009-10-13 16:39:14.0Z
F.Schroedl Posted on 2009-10-13 07:44:45.0Z
Sender: 7e2d.4ad42a27.1804289383@sybase.com
From: F.Schroedl
Newsgroups: sybase.public.ase.general
Subject: how to convert column to identity
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ad42fec.7f26.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Oct 2009 00:44:45 -0700
X-Trace: forums-1-dub 1255419885 10.22.241.41 (13 Oct 2009 00:44:45 -0700)
X-Original-Trace: 13 Oct 2009 00:44:45 -0700, 10.22.241.41
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28457
Article PK: 77700

I have a ASE installation (select @@version --> "Adaptive
Server Enterprise/12.5.4/EBF 14926 ESD#7/P/Linux
Intel/Enterprise Linux/ase1254/2093/32-bit/OPT/Wed Oct 10
22:41:12 2007")

In a certain table I want to convert a column (int,
primary-key) to an identity.

My intention is to drop the column with "alter table
my_table drop my_ckey" and add it with "alter table my_table
add my_ckey int identity"

But drop command returns "ALTER TABLE 'my_table' failed. You
cannot drop column 'my_ckey' on which a primary/foreign key
constraint is defined using sp_primarykey or sp_foreignkey.
Drop the key definitions before dropping the column."

Now I have the problem how to drop the constraints,
particularly don't knowing the constraints name.

Any hint how to proceed would be appreciated


Vivek Kak Posted on 2009-10-13 07:56:35.0Z
Sender: 5e9a.4ad2fdb4.1804289383@sybase.com
From: Vivek Kak
Newsgroups: sybase.public.ase.general
Subject: Re: how to convert column to identity
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ad432b2.7fa6.1681692777@sybase.com>
References: <4ad42fec.7f26.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 13 Oct 2009 00:56:35 -0700
X-Trace: forums-1-dub 1255420595 10.22.241.41 (13 Oct 2009 00:56:35 -0700)
X-Original-Trace: 13 Oct 2009 00:56:35 -0700, 10.22.241.41
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28458
Article PK: 77701

Hi ,

You can use sp_helpkey for the same .

Syntax:
------------
sp_helpkey [tabname]
Parameter tabname is the name of a table or view in the
current database. If you do not specify a name,the procedure
reports on all keys defined in the current database.

--------------


HTH,
Vivek

> I have a ASE installation (select @@version --> "Adaptive
> Server Enterprise/12.5.4/EBF 14926 ESD#7/P/Linux
> Intel/Enterprise Linux/ase1254/2093/32-bit/OPT/Wed Oct 10
> 22:41:12 2007")
>
> In a certain table I want to convert a column (int,
> primary-key) to an identity.
>
> My intention is to drop the column with "alter table
> my_table drop my_ckey" and add it with "alter table
> my_table add my_ckey int identity"
>
> But drop command returns "ALTER TABLE 'my_table' failed.
> You cannot drop column 'my_ckey' on which a
> primary/foreign key constraint is defined using
> sp_primarykey or sp_foreignkey. Drop the key definitions
> before dropping the column."
>
> Now I have the problem how to drop the constraints,
> particularly don't knowing the constraints name.
>
> Any hint how to proceed would be appreciated


Rob V [ Sybase ] Posted on 2009-10-13 16:39:14.0Z
Reply-To: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Newsgroups: sybase.public.ase.general
References: <4ad42fec.7f26.1681692777@sybase.com> <4ad432b2.7fa6.1681692777@sybase.com>
Subject: Re: how to convert column to identity
Lines: 71
Organization: Sypron BV / TeamSybase / Sybase
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ad4ad32@forums-1-dub>
Date: 13 Oct 2009 09:39:14 -0700
X-Trace: forums-1-dub 1255451954 10.22.241.152 (13 Oct 2009 09:39:14 -0700)
X-Original-Trace: 13 Oct 2009 09:39:14 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:28464
Article PK: 77707

You need to use 'sp_helpconstraint <table-name> " to determine the names of
existing RI constraints.

sp_helpkey is not the right proc to use -- it only reports on *logical*
constraints that were explicitly added with sp_primarykey etc. But this is
for documentational purposes only -- it doesn't have a relation with
*actual* RI constraints that exist on tables.

HTH,

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

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

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

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

<Vivek Kak> wrote in message news:4ad432b2.7fa6.1681692777@sybase.com...
> Hi ,
>
> You can use sp_helpkey for the same .
>
> Syntax:
> ------------
> sp_helpkey [tabname]
> Parameter tabname is the name of a table or view in the
> current database. If you do not specify a name,the procedure
> reports on all keys defined in the current database.
>
> --------------
>
>
> HTH,
> Vivek
>
>
>> I have a ASE installation (select @@version --> "Adaptive
>> Server Enterprise/12.5.4/EBF 14926 ESD#7/P/Linux
>> Intel/Enterprise Linux/ase1254/2093/32-bit/OPT/Wed Oct 10
>> 22:41:12 2007")
>>
>> In a certain table I want to convert a column (int,
>> primary-key) to an identity.
>>
>> My intention is to drop the column with "alter table
>> my_table drop my_ckey" and add it with "alter table
>> my_table add my_ckey int identity"
>>
>> But drop command returns "ALTER TABLE 'my_table' failed.
>> You cannot drop column 'my_ckey' on which a
>> primary/foreign key constraint is defined using
>> sp_primarykey or sp_foreignkey. Drop the key definitions
>> before dropping the column."
>>
>> Now I have the problem how to drop the constraints,
>> particularly don't knowing the constraints name.
>>
>> Any hint how to proceed would be appreciated