Why won't SQL let me delete this foreign key?

I'm trying to remove a foreign key from a SQL table, but it isn't working for some reason.

ALTER TABLE PETS
DROP FOREIGN KEY OWNER_NAME

Bu开发者_如何学Got the above doesn't work - I don't get it. There isn't much syntax to mess up on, and PETS does have a foreign key OWNER_NAME to another table. What gives?

Pets table:

CREATE TABLE PETS
(
  NAME VARCHAR(10) NOT NULL,
  BIRTH_DATE DATE NOT NULL,
  OWNER_NAME VARCHAR(10) NOT NULL,
  PRIMARY KEY (NAME),
  FOREIGN KEY (OWNER_NAME) REFERENCES OWNER ON DELETE CASCADE
);

ERROR RECIEVED: "OWNER_NAME" is an undefined name.

The syntax for dropping a foreign key is:

   ALTER TABLE table
      DROP FOREIGN KEY fk_name

In your example (that shows how the foreign key constraint is created, OWNER_NAME is not the name of the foreign key -- that is the name of the column in the PETS table that is used to reference the OWNER table.

Because you don't specify a specific name for the constraint in the CREATE TABLE statement, it will have a system generated name, which will look something like SQL110213181225320.

You can find the name of the constraint by looking at SYSCAT.REFERENCES:

   SELECT constname, fk_colnames
   FROM   syscat.references
   WHERE  tabschema = 'YOURSCHEMA'
     AND  tabname = 'PETS'

Then, you can drop your foreign key using the appropriate value of CONSTNAME from this query:

ALTER TABLE pets DROP FOREIGN KEY SQL110213181225320;

FYI, if you want to have a more normal name for the foreign key constraint, you can define it in the CREATE TABLE statement:

CREATE TABLE PETS
(
  NAME VARCHAR(10) NOT NULL,
  BIRTH_DATE DATE NOT NULL,
  OWNER_NAME VARCHAR(10) NOT NULL,
  PRIMARY KEY (NAME),
  CONSTRAINT FK_OWNER 
     FOREIGN KEY (OWNER_NAME) 
     REFERENCES OWNER 
     ON DELETE CASCADE
);

If you do this, then you'll see that the query against SYSCAT.REFERENCES will return 'FK_OWNER'.