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.

Can't delete a table with a foreign key

7 posts in Windows NT Last posting was on 2000-04-18 03:40:36.0Z
Jeff Bowers Posted on 2000-03-15 18:13:29.0Z
Message-ID: <38CFD2C9.3FDE1CE1@ncsc.mil>
Date: Wed, 15 Mar 2000 13:13:29 -0500
From: Jeff Bowers <jdbower@ncsc.mil>
X-Mailer: Mozilla 4.51 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Can't delete a table with a foreign key
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 24
NNTP-Posting-Host: saltblock.ncsc.mil 144.51.185.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2409
Article PK: 1089859

Hi,
I'm running ASE 11.9.2 on Win NT4.0 SP5. I'm logged in as SysAdmin. I
have a user db created by the SysAdmin account with 2 tables that I am
try to drop. Table A has a foreign key that references table B. Table B
has no references to any other table. If I try to drop table B first it
tells me that it can't be dropped because of a referential integrity
constraint. OK, that's the way it's supposed to work. If I try to drop
table A (via Sybase Central, ISQL, or SQL Advantage) I get no errors
(which is just peachy) but.... the table is still there!!! I've tried
ALTER TABLE A DROP CONSTRAINT xxxxx_nnnnnn which doesn't complain either
but the constraint doesn't get dropped. I've run the consistency checker
and no errors were reported. It even let me rename both tables but I
still get the same problem. Does anybody have any ideas about what is
going on here????

Thanks for any help,
Jeff


Ahmad AbdelHady Posted on 2000-03-21 11:54:22.0Z
Reply-To: "Ahmad AbdelHady" <ahady@generation.ca>
From: "Ahmad AbdelHady" <ahady@generation.ca>
References: <38CFD2C9.3FDE1CE1@ncsc.mil>
Subject: Re: Can't delete a table with a foreign key
Date: Tue, 21 Mar 2000 13:54:22 +0200
Lines: 35
Organization: Generation Systems Inc.
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Message-ID: <Alwxvyyk$GA.290@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: 60-15.idsc.gov.eg 163.121.15.60
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2394
Article PK: 1089845

i'm using the same configuration/versions and i tried ur scenario but it
worked with no errors .. and the table is "dropped"
maybe there's somthin rong with ur catalog tables.
you may want to take a backup before you start
try these steps:
sp_configure 'allow updates',1
then
delete sysobjects where name='table name'
then
dbcc checkalloc with fix option

let me know !

Jeff Bowers <jdbower@ncsc.mil> wrote in message
news:38CFD2C9.3FDE1CE1@ncsc.mil...
> Hi,
> I'm running ASE 11.9.2 on Win NT4.0 SP5. I'm logged in as SysAdmin. I
> have a user db created by the SysAdmin account with 2 tables that I am
> try to drop. Table A has a foreign key that references table B. Table B
> has no references to any other table. If I try to drop table B first it
> tells me that it can't be dropped because of a referential integrity
> constraint. OK, that's the way it's supposed to work. If I try to drop
> table A (via Sybase Central, ISQL, or SQL Advantage) I get no errors
> (which is just peachy) but.... the table is still there!!! I've tried
> ALTER TABLE A DROP CONSTRAINT xxxxx_nnnnnn which doesn't complain either
> but the constraint doesn't get dropped. I've run the consistency checker
> and no errors were reported. It even let me rename both tables but I
> still get the same problem. Does anybody have any ideas about what is
> going on here????
>
> Thanks for any help,
> Jeff
>
>


Jeff Bowers Posted on 2000-03-23 15:32:41.0Z
Message-ID: <38DA3919.6F0D87A5@ncsc.mil>
Date: Thu, 23 Mar 2000 10:32:41 -0500
From: Jeff Bowers <jdbower@ncsc.mil>
X-Mailer: Mozilla 4.51 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Can't delete a table with a foreign key
References: <38CFD2C9.3FDE1CE1@ncsc.mil> <Alwxvyyk$GA.290@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 48
NNTP-Posting-Host: saltblock.ncsc.mil 144.51.185.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2389
Article PK: 1089842

After spending an hour with tech support, I wound up deleting "stuff" from 3
system tables, sysobjects, sysconstraints, syscolumns and everything that I
wanted gone is now gone. There was no speculation as to how or why the problem
occured in the first place. Guess I just need to keep my fingers crossed that
it doesn't happen again. At least this one was on a development db.

Ahmad AbdelHady wrote:

> i'm using the same configuration/versions and i tried ur scenario but it
> worked with no errors .. and the table is "dropped"
> maybe there's somthin rong with ur catalog tables.
> you may want to take a backup before you start
> try these steps:
> sp_configure 'allow updates',1
> then
> delete sysobjects where name='table name'
> then
> dbcc checkalloc with fix option
>
> let me know !
> Jeff Bowers <jdbower@ncsc.mil> wrote in message
> news:38CFD2C9.3FDE1CE1@ncsc.mil...
> > Hi,
> > I'm running ASE 11.9.2 on Win NT4.0 SP5. I'm logged in as SysAdmin. I
> > have a user db created by the SysAdmin account with 2 tables that I am
> > try to drop. Table A has a foreign key that references table B. Table B
> > has no references to any other table. If I try to drop table B first it
> > tells me that it can't be dropped because of a referential integrity
> > constraint. OK, that's the way it's supposed to work. If I try to drop
> > table A (via Sybase Central, ISQL, or SQL Advantage) I get no errors
> > (which is just peachy) but.... the table is still there!!! I've tried
> > ALTER TABLE A DROP CONSTRAINT xxxxx_nnnnnn which doesn't complain either
> > but the constraint doesn't get dropped. I've run the consistency checker
> > and no errors were reported. It even let me rename both tables but I
> > still get the same problem. Does anybody have any ideas about what is
> > going on here????
> >
> > Thanks for any help,
> > Jeff
> >
> >


qwerr Posted on 2000-04-18 03:40:36.0Z
Message-ID: <38FBD934.1CB35859@netscape.net>
Date: Mon, 17 Apr 2000 23:40:36 -0400
From: qwerr@netscape.net
X-Mailer: Mozilla 4.61 [en] (Win98; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Can't delete a table with a foreign key
References: <38CFD2C9.3FDE1CE1@ncsc.mil> <Alwxvyyk$GA.290@forums.sybase.com> <38DA3919.6F0D87A5@ncsc.mil>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 60
NNTP-Posting-Host: gateway.uwnyc.org 38.228.204.10
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2301
Article PK: 1089734

You may also play and try another approach: check what locking scheme you have on
this table.
If you have switched locking for this table from DOL to All Page Locks
(or back) some time ago, constraints could have been not updated and because
of that cannot be dropped. You may try to swtch locking scheme back to
initial and drop the constraint.

Thanks

Ninel

Jeff Bowers wrote:

> After spending an hour with tech support, I wound up deleting "stuff" from 3
> system tables, sysobjects, sysconstraints, syscolumns and everything that I
> wanted gone is now gone. There was no speculation as to how or why the problem
> occured in the first place. Guess I just need to keep my fingers crossed that
> it doesn't happen again. At least this one was on a development db.
>
> Ahmad AbdelHady wrote:
>
> > i'm using the same configuration/versions and i tried ur scenario but it
> > worked with no errors .. and the table is "dropped"
> > maybe there's somthin rong with ur catalog tables.
> > you may want to take a backup before you start
> > try these steps:
> > sp_configure 'allow updates',1
> > then
> > delete sysobjects where name='table name'
> > then
> > dbcc checkalloc with fix option
> >
> > let me know !
> > Jeff Bowers <jdbower@ncsc.mil> wrote in message
> > news:38CFD2C9.3FDE1CE1@ncsc.mil...
> > > Hi,
> > > I'm running ASE 11.9.2 on Win NT4.0 SP5. I'm logged in as SysAdmin. I
> > > have a user db created by the SysAdmin account with 2 tables that I am
> > > try to drop. Table A has a foreign key that references table B. Table B
> > > has no references to any other table. If I try to drop table B first it
> > > tells me that it can't be dropped because of a referential integrity
> > > constraint. OK, that's the way it's supposed to work. If I try to drop
> > > table A (via Sybase Central, ISQL, or SQL Advantage) I get no errors
> > > (which is just peachy) but.... the table is still there!!! I've tried
> > > ALTER TABLE A DROP CONSTRAINT xxxxx_nnnnnn which doesn't complain either
> > > but the constraint doesn't get dropped. I've run the consistency checker
> > > and no errors were reported. It even let me rename both tables but I
> > > still get the same problem. Does anybody have any ideas about what is
> > > going on here????
> > >
> > > Thanks for any help,
> > > Jeff
> > >
> > >


Charles Monts Posted on 2000-03-17 19:57:05.0Z
From: "Charles Monts" <gti4monts@erols.com>
References: <38CFD2C9.3FDE1CE1@ncsc.mil>
Subject: Re: Can't delete a table with a foreign key
Date: Fri, 17 Mar 2000 14:57:05 -0500
Lines: 6
Organization: Gusrdsman Technology, Inc.
X-Newsreader: Microsoft Outlook Express 4.72.3155.0
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3155.0
Message-ID: <nOz2tuEk$GA.34@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: w228.z208037100.was-dc.dsl.cnc.net 208.37.100.228
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2403
Article PK: 1089858

I sent out a similar message twice before & have not received input from
anyone. This is a major problem for me. I am about to resort to dropping the
database and recreating from scratch just to get around what appears to be a
MAJOR BUG with this database.


Jeff Bowers Posted on 2000-03-20 17:42:08.0Z
Message-ID: <38D662F0.1266FF03@ncsc.mil>
Date: Mon, 20 Mar 2000 12:42:08 -0500
From: Jeff Bowers <jdbower@ncsc.mil>
X-Mailer: Mozilla 4.51 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Can't delete a table with a foreign key
References: <38CFD2C9.3FDE1CE1@ncsc.mil> <nOz2tuEk$GA.34@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 14
NNTP-Posting-Host: saltblock.ncsc.mil 144.51.185.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2401
Article PK: 1089853


Charles Monts wrote:

> I sent out a similar message twice before & have not received input from
> anyone. This is a major problem for me. I am about to resort to dropping the
> database and recreating from scratch just to get around what appears to be a
> MAJOR BUG with this database.

Guess this is going to be a tech support case. If I get any useful answers I
will post them. I didn't want to have to go into the system tables and mess
around but I have this funny feeling that's what I will have to do.


Tony Imbierski Posted on 2000-03-21 10:07:14.0Z
Message-ID: <38D749D2.A97680CF@sybase.com>
Date: Tue, 21 Mar 2000 10:07:14 +0000
From: Tony Imbierski <tonyi@sybase.com>
X-Mailer: Mozilla 4.61 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Can't delete a table with a foreign key
References: <38CFD2C9.3FDE1CE1@ncsc.mil> <nOz2tuEk$GA.34@forums.sybase.com> <38D662F0.1266FF03@ncsc.mil>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 25
NNTP-Posting-Host: chirico.sybase.com 158.76.102.96
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:2397
Article PK: 1089849

I guess I must have missed the earlier part of this thread, but anyone who has a
major problem like this should, as Jeff says, take it to tech support. The
newsgroup is great for general enquiries but it is *informal*. You may not get any
replies if nobody reading knows the answer to your problem. Personally I always
answer posts *if* I know the answer.

regards,
tonyi
ASE Engineering

Jeff Bowers wrote:

> Charles Monts wrote:
>
> > I sent out a similar message twice before & have not received input from
> > anyone. This is a major problem for me. I am about to resort to dropping the
> > database and recreating from scratch just to get around what appears to be a
> > MAJOR BUG with this database.
>
> Guess this is going to be a tech support case. If I get any useful answers I
> will post them. I didn't want to have to go into the system tables and mess
> around but I have this funny feeling that's what I will have to do.