January 17, 2006 at 11:15 am
Hi all,
I don't mean to have multiple posts, but I think this is generic problem not for sql server 2005, can you help?
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
January 17, 2006 at 11:41 am
The tables have to be dropped in the right order. Without rewriting your script for you, I did notice that you have to drop the Dependents table before you can drop the OwnerOrderDetails table because Dependents references it.
You don't have to delete the data but all referencing tables must be dropped before the table they reference can be dropped.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 17, 2006 at 11:52 am
Hi, dcpeterson,
Good catch. I only noticed the order: OwnerOrderDetails-->OwnerOrders-->Owners, it's right order and I didn't pay attention to dependents table. Thank you so much.
It seems that auto generated script sometimes will have problems. I didn't change anything after datanamic auto generating the drop.sql script.
Betty
January 18, 2006 at 2:00 pm
This is true for any references to the table as well as creating tables, nested stored procs etc... You can't rely on it to generate the scripts with interdependent objects in the proper order. It can be a pain on larger scripts, but you'll often have to rearrange things to get the script to run as it should.
January 18, 2006 at 2:17 pm
...or drop all the foreign key constraints before dropping the tables. If you drop the constraints first, you won't have to worry about what order the tables are dropped.
Greg
Greg
January 18, 2006 at 4:10 pm
Hi Greg,
That's good idea. But when I look at the drop.sql created by datanamic.
In drop column/table constaints area, it is empty, nothing there.
Maybe some other design tools will give some options how to create drop.sql.
Betty
January 19, 2006 at 3:31 pm
Query analyzer will script out creates/drops for individual objects. If you want one file, you'll have to compile them together though.
January 20, 2006 at 10:36 am
Betty,
I don't know anything about Datanamic. I run queries in Query Analyzer to find foreign key dependencies and script the ALTER TABLE statements to drop and create them. Here they are if you're interested:
--list FK constraints
select t.name as 'Table', c.name as 'Foreign_Key_Constraint', rt.name as 'Referenced_Table'
from (select distinct constid, fkeyid, rkeyid from sysforeignkeys) as FK
join sysobjects c on c.id = FK.constid
join sysobjects t on t.id = FK.fkeyid
join sysobjects rt on rt.id = FK.rkeyid
order by 1,2,3
--generate statements to create FK constraints
select 'ALTER TABLE ' + cast(t.name as char(40)) + 'ADD CONSTRAINT '
+ cast(c.name as char(50)) + 'FOREIGN KEY ' + '(' + fc.name + ') ' + 'REFERENCES '
+ cast(rt.name as char(30)) + '(' + rc.name + ') ' + char(13)
from (select distinct constid, fkeyid, rkeyid, fkey, rkey from sysforeignkeys) as FK
join sysobjects c on c.id = FK.constid
join sysobjects t on t.id = FK.fkeyid
join sysobjects rt on rt.id = FK.rkeyid
join syscolumns fc on fc.colid = FK.fkey and fc.id = FK.fkeyid
join syscolumns rc on rc.colid = FK.rkey and rc.id = FK.rkeyid
--generate statements to drop FK constraints
select 'ALTER TABLE ' + cast(table_name as char(40)) + 'DROP CONSTRAINT '
+ cast(constraint_name as char(50)) + char(13)
from information_schema.constraint_table_usage
where constraint_name like 'FK%'
order by table_name, constraint_name
Greg
January 20, 2006 at 10:51 am
Wow, that's great! I guess you programed against database very often, otherwise how come you know master database? Thank you.
Betty
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply