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.

query if a FK constraint is set to cascade deletes

2 posts in General Discussion Last posting was on 2009-10-20 14:46:44.0Z
Brandon Theobald Posted on 2009-10-20 14:15:42.0Z
From: Brandon Theobald <brandont@sybase.com>
User-Agent: Thunderbird 2.0.0.23 (Windows/20090812)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: query if a FK constraint is set to cascade deletes
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: <4addc60e@forums-1-dub>
Date: 20 Oct 2009 07:15:42 -0700
X-Trace: forums-1-dub 1256048142 10.22.241.152 (20 Oct 2009 07:15:42 -0700)
X-Original-Trace: 20 Oct 2009 07:15:42 -0700, vip152.sybase.com
Lines: 18
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7764
Article PK: 6801

A customer had the following question and provided the following details:

How can I tell via a query if a FK constraint is set to cascade deletes?
I want to test for this in a stored procedure.

The dba user can't select from sys.ISYSCONSTRAINT and the SYSCONSTRAINT
view doesn't have this info. I know it must be somewhere in the
dictionary tables. We have about 2000 remote DB's and all will run a
stored proc to perform the next upgrade. A few (maybe 1%) have an FK
that does not cascade deletes and I need to fix those. Dropping and
recreating the FK on all the remotes seems a bit wasteful, so I'd like
to be able to query a table/view and see if they are set right, then
only fix the ones that need it.

Does anyone know how to do this?

Thanks,
Brandon Theobald


Martijn Tonies Posted on 2009-10-20 14:46:44.0Z
From: "Martijn Tonies" <m.tonies@upscene.remove.com>
Newsgroups: ianywhere.public.general
References: <4addc60e@forums-1-dub>
Subject: Re: query if a FK constraint is set to cascade deletes
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3598
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4addcd54$1@forums-1-dub>
Date: 20 Oct 2009 07:46:44 -0700
X-Trace: forums-1-dub 1256050004 10.22.241.152 (20 Oct 2009 07:46:44 -0700)
X-Original-Trace: 20 Oct 2009 07:46:44 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7765
Article PK: 6802

Hello Brandon,

>A customer had the following question and provided the following details:
>
> How can I tell via a query if a FK constraint is set to cascade deletes? I
> want to test for this in a stored procedure.
>
> The dba user can't select from sys.ISYSCONSTRAINT and the SYSCONSTRAINT
> view doesn't have this info. I know it must be somewhere in the dictionary
> tables. We have about 2000 remote DB's and all will run a stored proc to
> perform the next upgrade. A few (maybe 1%) have an FK that does not
> cascade deletes and I need to fix those. Dropping and recreating the FK on
> all the remotes seems a bit wasteful, so I'd like to be able to query a
> table/view and see if they are set right, then only fix the ones that need
> it.
>
> Does anyone know how to do this?

I figured this out when doing Database Workbench for Sybase SQL Anywhere --

A constraint has 2 optional links to systrigger and that has the referential
actions.


--
With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com