Delete foreign keys in sybase 12.5

Question

Hi,

I have two tables:

CREATE TABLE dbo.country
(
cntry_id  VARCHAR(2)    NOT NULL,
name      VARCHAR(50)   NOT NULL,

CONSTRAINT pk_country PRIMARY KEY (cntry_id)

CREATE TABLE dbo.city
(
city_id   VARCHAR(3)    NOT NULL,
name      VARCHAR(50)   NOT NULL,
cntry_id  VARCHAR(2)    NOT NULL,

CONSTRAINT pk_city PRIMARY KEY (city_id),
FOREIGN KEY (cntry_id) REFERENCES dbo.country(cntry_id)
)

I am trying to drop the fk constrait so I can then drop the table.

The FK definitley exists:

EXEC sp_fkeys country 
pktable_qualifier   pktable_owner pk_tablename ... 
xxxxxx          xxx             country cntry_id ....

(DB name obscured)

But both

EXEC sp_dropkey foreign, country, city
EXEC sp_dropkey foreign, city, country

return

264 Error (17499) No foreign key for the table or view exists. sp_dropkey(263)

Does anybody know how to drop these keys?

Thank you in advance

Ryan

Answer 1

ALTER TABLE dbo.city DELETE FOREIGN KEY [enternameoftheforeignkeyhere]

Otherwise I don't know what could be the reason. The number of the error message means he couldn't delete it from syskeys. But he found the two tables alright and your are the owner of the tables too.

---

Did you try sp_helpkey and sp_helpconstraint to check what they say about the existence of a FK?

This should also be able to tell you if there really is a FK defined.

select * from syskeys where depid = object_id([parenttablename]) and type = 2

Regarding the naming of a FK. This should do the trick

CREATE TABLE
....
CONSTRAINT fk_mykey FOREIGN KEY (cntry_id) REFERENCES dbo.country(cntry_id)
...

Answer 2

I do not have a 12.5 DB infront of me at the moment but I know with Sybase Anywhere and Sybase IQ you can manage foreign keys with Sybase Central. If you have Sybase Central installed, fire it up select your table and then on the right side look for a tab called Constraints or Foreign Keys. If you have it select the FK you want to drop, press delete then right click the table on the left side and select SAVE TABLE.

I hope that helps!