5 Replies - 415 Views - Last Post: 13 March 2017 - 04:55 PM

#1 MadKazper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 13-March 17

SQL Could not drop Object Error because of a foreign key contraint?

Posted 13 March 2017 - 04:01 PM

These are some of the errors I am getting:
Could not drop object 'Unit' because it is referenced by a FOREIGN KEY constraint.
Could not drop object 'ZipCode' because it is referenced by a FOREIGN KEY constraint.
There is already an object named 'ZipCode' in the database.

Drop Table Development;
Drop Table Move;
Drop Table Household;
Drop Table Unit;
Drop Table ZipCode;
Drop Table HouseHoldMember;


Create Table ZipCode
(
Zip_ID int IDENTITY(1,1) PRIMARY KEY,
Zip_City varchar(30) NOT NULL,
Zip_State varchar(25) NOT NULL,
);

Create Table Development
(
DevelopmentID int IDENTITY(1,1) PRIMARY KEY,
DevelopmentName varchar(20) NOT NULL,
DevelopmentAdress varchar(20) NOT NULL,
Zip_ID INT,
FOREIGN KEY (Zip_ID) REFERENCES ZipCode ON DELETE SET NULL
);

Create Table Unit
(
UnitID varchar(100) PRIMARY KEY,
BedroomNum varchar(20) NOT NULL,
BathroomNum varchar(20) NOT NULL,
UnitSqFootage varchar(999) NOT NULL,
UnitKitchen varchar(20) NOT NULL ,
UnitLivingRoom varchar(20) NOT NULL
);

Create Table HouseholdMember
(
MemberID int IDENTITY(1,1) PRIMARY KEY,
MemberFName varchar(20) NOT NULL,
MemberLName varchar(20) NOT NULL,
MemberDOB varchar(11) NOT NULL,
MemberSex varchar(7) NOT NULL,
MemberPosition varchar(20) NOT NULL
);

Create Table Household
(
HouseholdID int IDENTITY(1,1) PRIMARY KEY,
UnitID INT,
FOREIGN KEY (UnitID) REFERENCES Unit ON DELETE SET NULL,
MemberID INT,
FOREIGN KEY (MemberID) REFERENCES HouseholdMember ON DELETE SET NULL
);

Create Table Move
(
MoveID int IDENTITY(1,1) PRIMARY KEY,
MoveDate varchar(11) NOT NULL,
MoveNewDevelopment varchar(50) NOT NULL,
MoveOldDevelopment varchar(50) NOT NULL,
MovePreviousUnit varchar(100) NOT NULL,
MoveNewUnit varchar(100) NOT NULL,
HouseholdID INT,
FOREIGN KEY (HouseholdID) References Household ON DELETE SET NULL
);

Is This A Good Question/Topic? 0
  • +

Replies To: SQL Could not drop Object Error because of a foreign key contraint?

#2 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: SQL Could not drop Object Error because of a foreign key contraint?

Posted 13 March 2017 - 04:03 PM

And you are confused or what exactly?
Was This Post Helpful? 0
  • +
  • -

#3 MadKazper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 13-March 17

Re: SQL Could not drop Object Error because of a foreign key contraint?

Posted 13 March 2017 - 04:05 PM

View Postastonecipher, on 13 March 2017 - 04:03 PM, said:

And you are confused or what exactly?


I am not sure why I keep getting the errors. When I change the order the tables drop in I just get the same error.
Was This Post Helpful? 0
  • +
  • -

#4 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: SQL Could not drop Object Error because of a foreign key contraint?

Posted 13 March 2017 - 04:15 PM

Why are you dropping the tables first? The constraints are there for a reason.

You need to drop the tables that reference others first.
Was This Post Helpful? 0
  • +
  • -

#5 MadKazper  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 13-March 17

Re: SQL Could not drop Object Error because of a foreign key contraint?

Posted 13 March 2017 - 04:29 PM

View Postastonecipher, on 13 March 2017 - 04:15 PM, said:

Why are you dropping the tables first? The constraints are there for a reason.

You need to drop the tables that reference others first.


Would I do it like this?

Drop Table Move;
Drop Table Household;
Drop Table HouseholdMember;
Drop Table Unit;
Drop Table Development;
Drop Table ZipCode;
Was This Post Helpful? 0
  • +
  • -

#6 astonecipher  Icon User is offline

  • Too busy for this
  • member icon

Reputation: 2329
  • View blog
  • Posts: 9,356
  • Joined: 03-December 12

Re: SQL Could not drop Object Error because of a foreign key contraint?

Posted 13 March 2017 - 04:55 PM

Have you tried it?


Another option is to drop the constraints before you drop the table.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1