drop table reference key problem
-
Hi, all,
I designed a very simple database use DeZign3 Datanamic design tool. And I used generated script to create a database and later on I found I put it in a wrong server instance, I tried to drop it(use drop.sql created by the design tool). But it gave me error message as follow:
Could not drop object 'OwnerOrderDetails' because it is referenced by a FOREIGN KEY constraint.
Could not drop object 'OwnerOrders' because it is referenced by a FOREIGN KEY constraint.
Could not drop object 'Owners' because it is referenced by a FOREIGN KEY constraint.
But I just don't get it. Why this doesn't happen to Employees and EmployeesOrder or Employees and Dependents relationships. Object dependent or employeesOrder referenced by a foreign key SSN too.
Do I have to add on delete--cascade for integrity in the Owners_OwnerOrders/OwnerOrders_OwnerOrderDetails relationships.
Can you shed a light on me?
Thank you!
Betty
The following are drop.sql and create.sql
/*==========================================================================*/
/* Drop Indexes */
/*==========================================================================*/
DROP INDEX Owners.IDX_Owners1
GO
DROP INDEX Employees.IDX_Employees1
GO
DROP INDEX Employees.IDX_Employees2
GO
DROP INDEX Dependents.IDX_Dependents1
GO
DROP INDEX Dependents.IDX_Dependents2
GO
DROP INDEX EmployeeOrders.IDX_EmployeeOrders1
GO
DROP INDEX EmployeeOrders.IDX_EmployeeOrders2
GO
DROP INDEX OwnerOrders.IDX_OwnerOrders1
GO
DROP INDEX OwnerOrders.IDX_OwnerOrders2
GO
DROP INDEX OwnerOrderDetails.IDX_OwnerOrderDetails1
GO
DROP INDEX OwnerOrderDetails.IDX_OwnerOrderDetails2
GO
DROP INDEX PaymentDetails.IDX_PaymentDetails1
GO
DROP INDEX OrderContent.IDX_OrderContent1
GO
DROP INDEX TrxnLog.IDX_TrxnLog1
GO
/*==========================================================================*/
/* Drop Column Constraints */
/*==========================================================================*/
/*==========================================================================*/
/* Drop Table Constraints */
/*==========================================================================*/
/*==========================================================================*/
/* Drop Triggers */
/*==========================================================================*/
/*==========================================================================*/
/* Drop Procedures */
/*==========================================================================*/
/*==========================================================================*/
/* Drop Views */
/*==========================================================================*/
/*==========================================================================*/
/* Drop Tables */
/*==========================================================================*/
DROP TABLE TrxnLog
GO
DROP TABLE OrderContent
GO
DROP TABLE PaymentDetails
GO
DROP TABLE UsaSate
GO
DROP TABLE OwnerOrderDetails
GO
DROP TABLE OwnerOrders
GO
DROP TABLE EmployeeOrders
GO
DROP TABLE Dependents
GO
DROP TABLE Employees
GO
DROP TABLE Owners
GO
The drop.sql is as follow:
the generated database schema is as follow:
/*==========================================================================*/
/* Tables */
/*==========================================================================*/
CREATE TABLE Owners (
StoreID VARCHAR(50) NOT NULL,
FName VARCHAR(40) NOT NULL,
MI CHAR(1),
LName VARCHAR(50) NOT NULL,
Sex CHAR(1) NOT NULL,
StoreAddress VARCHAR(100) NOT NULL,
City VARCHAR(40) NOT NULL,
State CHAR(2) NOT NULL,
zip VARCHAR(25) NOT NULL,
StorePhone VARCHAR(25),
Email VARCHAR(50) NOT NULL UNIQUE,
PRIMARY KEY (StoreID)
)
GO
CREATE TABLE Employees (
StoreID VARCHAR(50) NOT NULL,
EmployeeID INTEGER IDENTITY(1,1) NOT NULL,
FName VARCHAR(50) NOT NULL,
MI CHAR(1),
LName VARCHAR(50) NOT NULL,
Sex CHAR(1),
SSN CHAR(11) NOT NULL,
HomeAddress VARCHAR(100),
City VARCHAR(40),
State CHAR(2),
Zip VARCHAR(25),
DayPhone VARCHAR(25),
EveningPhone VARCHAR(25),
HasDependent CHAR(1) DEFAULT 'N' NOT NULL,
PRIMARY KEY (SSN)
)
GO
CREATE TABLE Dependents (
DependentID INTEGER IDENTITY(1,1) NOT NULL,
txndatetime DATETIME DEFAULT 'getDate()',
FName VARCHAR(50) NOT NULL,
MI CHAR(1),
LName VARCHAR(50) NOT NULL,
SSN CHAR(11) NOT NULL,
Sex CHAR(1),
OrderDetailsID INTEGER,
PRIMARY KEY (DependentID)
)
GO
CREATE TABLE EmployeeOrders (
OrderID INTEGER IDENTITY(1,1) NOT NULL,
txndatetime DATETIME DEFAULT 'getDate()',
CoveredPeriod INTEGER NOT NULL,
DiscountPlan VARCHAR(10) NOT NULL,
OrderTotal MONEY NOT NULL,
PaymentReceived MONEY NOT NULL,
DependentFlag CHAR(1) DEFAULT 'N' NOT NULL,
PaymentMethod VARCHAR(40) NOT NULL,
SSN CHAR(11) NOT NULL,
MonthlyPayment MONEY NOT NULL,
PRIMARY KEY (OrderID)
)
GO
CREATE TABLE OwnerOrders (
OrderID INTEGER NOT NULL,
StoreID VARCHAR(50),
txndatetime DATETIME DEFAULT 'getDate()',
NumEnrolled INTEGER NOT NULL,
PaymentMethod VARCHAR(40) NOT NULL,
OrderTotal MONEY NOT NULL,
PaymentReceived MONEY NOT NULL,
MonthlyCharge MONEY,
PRIMARY KEY (OrderID)
)
GO
CREATE TABLE OwnerOrderDetails (
OrderID INTEGER NOT NULL,
OrderDetailsID INTEGER NOT NULL,
txndatetime DATETIME DEFAULT 'getDate()',
EmployeeFName VARCHAR(50) NOT NULL,
EmployeeMI CHAR(1),
EmployeeLName VARCHAR(50) NOT NULL,
SSN CHAR(11) NOT NULL,
EmployeeSex CHAR(1),
DiscountPlan VARCHAR(10) NOT NULL,
CoveredPeriod INTEGER NOT NULL,
DependentFlag CHAR(1) DEFAULT 'N' NOT NULL,
IndTotalCharge MONEY,
IndPaymentReceived MONEY,
IndMonthlyCharge MONEY,
PRIMARY KEY (OrderDetailsID, SSN)
)
GO
CREATE TABLE UsaSate (
State CHAR(2) NOT NULL,
FullName VARCHAR(30) NOT NULL,
PRIMARY KEY (State)
)
GO
CREATE TABLE PaymentDetails (
OrderID INTEGER NOT NULL,
txndattime DATETIME DEFAULT 'getDate()' NOT NULL,
TotalCharge MONEY NOT NULL,
PaymentReceived MONEY NOT NULL,
CreditHolder VARCHAR(100),
BillingAddress VARCHAR(100),
City VARCHAR(50),
State VARCHAR(30),
Country VARCHAR(150),
pnref VARCHAR(20),
Auth_Code VARCHAR(10),
Avs_addr CHAR(1),
Avs_zip CHAR(1),
crno VARCHAR(29),
expdate VARCHAR(5),
csc CHAR(5),
MonthlyPayment MONEY NOT NULL,
PRIMARY KEY (OrderID)
)
GO
CREATE TABLE OrderContent (
OrderID INTEGER NOT NULL,
txndatetime DATETIME DEFAULT 'getDate()',
content VARCHAR(8000),
PRIMARY KEY (OrderID)
)
GO
CREATE TABLE TrxnLog (
OrderID INTEGER NOT NULL,
LogID INTEGER IDENTITY(1,1) NOT NULL,
txndatetime DATETIME DEFAULT 'getDate()' NOT NULL,
txnType CHAR(1) NOT NULL,
content VARCHAR(300) NOT NULL,
acct VARCHAR(25),
expdate VARCHAR(7),
Amount MONEY,
billName VARCHAR(100),
Address VARCHAR(200),
resultCode VARCHAR(6),
pnref VARCHAR(20),
RespMessage VARCHAR(30),
AuthoCode VARCHAR(8),
AVSADDR CHAR(1),
AVSZIP CHAR(1),
CVV2MATCH CHAR(1),
PRIMARY KEY (LogID)
)
GO
/*==========================================================================*/
/* Indexes */
/*==========================================================================*/
CREATE UNIQUE INDEX IDX_Owners1 ON Owners (StoreID)
GO
CREATE INDEX IDX_Employees1 ON Employees (StoreID)
GO
CREATE UNIQUE INDEX IDX_Employees2 ON Employees (SSN)
GO
CREATE INDEX IDX_Dependents1 ON Dependents (SSN)
GO
CREATE INDEX IDX_Dependents2 ON Dependents (OrderDetailsID, SSN)
GO
CREATE INDEX IDX_EmployeeOrders1 ON EmployeeOrders (SSN)
GO
CREATE UNIQUE INDEX IDX_EmployeeOrders2 ON EmployeeOrders (OrderID)
GO
CREATE INDEX IDX_OwnerOrders1 ON OwnerOrders (StoreID)
GO
CREATE UNIQUE INDEX IDX_OwnerOrders2 ON OwnerOrders (OrderID)
GO
CREATE INDEX IDX_OwnerOrderDetails1 ON OwnerOrderDetails (OrderID)
GO
CREATE UNIQUE INDEX IDX_OwnerOrderDetails2 ON OwnerOrderDetails (OrderDetailsID, SSN)
GO
CREATE INDEX IDX_PaymentDetails1 ON PaymentDetails (OrderID)
GO
CREATE INDEX IDX_OrderContent1 ON OrderContent (OrderID)
GO
CREATE INDEX IDX_TrxnLog1 ON TrxnLog (OrderID)
GO
/*==========================================================================*/
/* Foreign Keys */
/*==========================================================================*/
ALTER TABLE Employees
ADD FOREIGN KEY (StoreID) REFERENCES Owners (StoreID)
GO
ALTER TABLE Dependents
ADD FOREIGN KEY (SSN) REFERENCES Employees (SSN)
GO
ALTER TABLE Dependents
ADD FOREIGN KEY (OrderDetailsID,SSN) REFERENCES OwnerOrderDetails (OrderDetailsID, SSN)
GO
ALTER TABLE EmployeeOrders
ADD FOREIGN KEY (SSN) REFERENCES Employees (SSN)
GO
ALTER TABLE OwnerOrders
ADD FOREIGN KEY (StoreID) REFERENCES Owners (StoreID)
GO
ALTER TABLE OwnerOrderDetails
ADD FOREIGN KEY (OrderID) REFERENCES OwnerOrders (OrderID)
GO
ALTER TABLE PaymentDetails
ADD FOREIGN KEY (OrderID) REFERENCES EmployeeOrders (OrderID)
GO
ALTER TABLE PaymentDetails
ADD FOREIGN KEY (OrderID) REFERENCES OwnerOrders (OrderID)
GO
ALTER TABLE OrderContent
ADD FOREIGN KEY (OrderID) REFERENCES EmployeeOrders (OrderID)
GO
ALTER TABLE OrderContent
ADD FOREIGN KEY (OrderID) REFERENCES OwnerOrders (OrderID)
GO
ALTER TABLE TrxnLog
ADD FOREIGN KEY (OrderID) REFERENCES OwnerOrders (OrderID)
GO
ALTER TABLE TrxnLog
ADD FOREIGN KEY (OrderID) REFERENCES EmployeeOrders (OrderID)
GO
/*==========================================================================*/
/* Views */
/*==========================================================================*/
/*==========================================================================*/
/* Procedures */
/*==========================================================================*/
/*==========================================================================*/
/* Triggers */
/*==========================================================================*/
/*==========================================================================*/
/* Table Comments */
/*==========================================================================*/
/*==========================================================================*/
/* Column Comments */
/*==========================================================================*/
EXEC sp_addextendedproperty 'MS_Description','For individual enrollment, an employee need to provide storeID too','user','dbo','table','Employees','column','StoreID'
GO
-
You just need to get the order of your drops correct. Instead of trying to totally follow your create logic, let me suggest that you manually drop them through the GUI and note your sequence of what is allowed until you come up with the right sequence, and then create your script from that sequence (although you may never need it again, it doesn't hurt to have it around). There are other answers that could be provided; but this has been here all day and nobody has responded, so if you were waiting for an answer to progress... here's an answer
Hope it helps you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply