Why table has drop, but the foreign key still there?

Why table has drop, but the foreign key still there?

Post by Raymond Ch » Fri, 15 Aug 2003 03:14:40

Here are the simple things I did

create table state (
state_code      char(2) not null,
state           varchar(15) not null,
primary key (state_code)

create table whitepage (
user_id char(8) not null,
email   varchar(50),
telephone       char(16) not null,
contact_name    varchar(30) not null,
city    varchar(20),
state_code      char(2),
primary key (user_id),
foreign key (state_code) references state (state_code)

insert into state (state_code,state) values ('GU','Guam');
drop table whitepage;
delete from state where state_code = 'GU';
ERROR:   Relation "whitepage" does not exist

Why I got this error message??!!
The whitepage table already drop. When the table drop, will the
foreign key constraint also drop??

Now, order for me delete a row from state table, I HAVE TO
re-create whitepage table. That's silly!!!

What shall I do? How do I delete a row in state without
re-create the whitepage table???

Thank you very much in advance!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?



1. Scripting DROP Table *and* DROP (related) Foreign Keys...

NEWBIE QUESTION:  I'm trying to create a SQL script that I can execute
by passing a table name as a parameter, but I'm having a tough time
figuring out how to DROP the related Foreign Key Constraint through a
variable first since it's in a different table AND goes by a different
name.  Here's what I have so far (I'm sure there are many other ways
to accomplish this too, so any ideas or guidance would be appreciated
as well...  I'm just starting to learn SQL!):

USE Northwind

if exists ( SELECT    *
FROM         sysobjects

N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Suppliers

if exists ( SELECT     *
FROM         sysobjects

OBJECTPROPERTY(id, N'IsUserTable') )= 1))



Thanks in advance for any help you can provide,


2. How to use variables for the column and table within the SELECT statement?

3. DROP TABLE hangs because of same table foreign key

4. Group query

5. Dropping Foreign Key without recreating table

6. Autentication in MySql

7. SQL won't let me drop table, Foreign Key constraint error

8. Weird FoxPro 2.6 For Mac Error!

9. Drop Table, SQL Server 2000, Foreign Key Constraints

10. Drop Tables Foreign Key Constraints

11. SQL to drop and then recreate all foreign keys for a table

12. How to DROP tables that are included in a foreign key constraint