January 6, 2010 at 10:38 am
I have have table that I need to get refreshed from another database.
I thought it would be as easy as dropping a PK constraint but it is not turning out that way.
Here is my my table DDL
USE [KOC151]
GO
/****** Object: Table [dbo]. Script Date: 01/06/2010 12:34:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].(
[area_chargable] [numeric](7, 2) NOT NULL DEFAULT (0.0),
[area_comn] [numeric](7, 2) NOT NULL DEFAULT (0.0),
[area_comn_nocup] [numeric](7, 2) NOT NULL DEFAULT (0.0),
[area_comn_ocup] [numeric](7, 2) NOT NULL DEFAULT (0.0),
[area_comn_rm] [numeric](7, 2) NOT NULL DEFAULT (0.0),
[area_comn_serv] [numeric](7, 2) NOT NULL DEFAULT (0.0),
[area_rm] [numeric](8, 2) NOT NULL DEFAULT (0.0),
[bl_id] [char](8) NULL DEFAULT (null),
[calling_card_number] [char](24) NULL DEFAULT (null),
[cellular_number] [char](24) NULL DEFAULT (null),
[comments] [varchar](500) NULL DEFAULT (null),
[contingency_bl_id] [char](8) NULL DEFAULT (null),
[contingency_email] [varchar](50) NULL DEFAULT (null),
[contingency_fac_at] [smallint] NOT NULL DEFAULT (0),
[contingency_fax] [char](20) NULL DEFAULT (null),
[contingency_fl_id] [char](4) NULL DEFAULT (null),
[contingency_phone] [char](20) NULL DEFAULT (null),
[contingency_rm_id] [char](8) NULL DEFAULT (null),
[cost] [numeric](8, 2) NOT NULL DEFAULT (0.0),
[dp_id] [char](16) NULL DEFAULT (null),
[dv_id] [char](16) NULL DEFAULT (null),
[dwgname] [char](64) NULL DEFAULT (null),
[ehandle] [char](16) NULL DEFAULT (null),
[em_number] [char](20) NULL CONSTRAINT [DF_em_em_number_default] DEFAULT (null),
[em_std] [char](16) NULL DEFAULT (null),
[varchar](50) NULL DEFAULT (null),
[emergency_contact] [char](64) NULL DEFAULT (null),
[emergency_phone] [char](20) NULL DEFAULT (null),
[emergency_relation] [char](32) NULL DEFAULT (null),
[extension] [char](20) NULL DEFAULT ('0'),
[fax] [char](20) NULL DEFAULT (null),
[fl_id] [char](4) NULL DEFAULT (null),
[honorific] [char](10) NULL DEFAULT ('Mr'),
[image_file] [char](64) NULL DEFAULT (null),
[layer_name] [char](32) NULL DEFAULT (null),
[mailstop] [char](10) NULL DEFAULT (null),
[name_first] [char](32) NULL DEFAULT (null),
[name_last] [char](32) NULL DEFAULT (null),
[net_id] [char](12) NULL DEFAULT (null),
[net_user_name] [char](16) NULL DEFAULT (null),
[option1] [char](16) NULL DEFAULT (null),
[option2] [char](16) NULL DEFAULT (null),
[pager_number] [char](24) NULL DEFAULT (null),
[pct_rm] [numeric](6, 2) NOT NULL DEFAULT (0.0),
[phone] [char](20) NULL DEFAULT (null),
[phone_home] [char](20) NULL DEFAULT (null),
[recovery_status] [char](12) NOT NULL DEFAULT ('NONE'),
[rm_id] [char](8) NULL DEFAULT (null),
[status] [char](10) NULL DEFAULT (null),
[tc_level] [char](16) NULL DEFAULT (null),
[em_id] [char](35) NOT NULL DEFAULT (null),
[fire_marshal] [char](3) NULL DEFAULT ('NO'),
[hire_date] [datetime] NULL DEFAULT (null),
[home_add1] [varchar](30) NULL DEFAULT (null),
[home_add2] [varchar](30) NULL DEFAULT (null),
[name_mi] [char](1) NULL DEFAULT (null),
[ss_num] [char](11) NULL DEFAULT (null),
[state_id] [char](2) NULL DEFAULT (null),
[zip_home] [char](10) NULL DEFAULT (null),
[badge_num] [char](8) NULL DEFAULT (null),
[city_id] [char](20) NULL DEFAULT (null),
[dob] [datetime] NULL DEFAULT (null),
[on_palm] [smallint] NOT NULL CONSTRAINT [DF_em_on_palm_default] DEFAULT (0),
[time_stamp] [char](40) NULL DEFAULT (null),
[username] [varchar](64) NULL DEFAULT (null),
[em_bar_code] [char](16) NULL DEFAULT (null),
CONSTRAINT [em_PK] PRIMARY KEY CLUSTERED
(
[em_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_bl_id] FOREIGN KEY([bl_id])
REFERENCES [dbo].[bl] ([bl_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_bl_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_contingency_bl_id] FOREIGN KEY([contingency_bl_id])
REFERENCES [dbo].[bl] ([bl_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_contingency_bl_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_contingency_fl_id] FOREIGN KEY([contingency_bl_id], [contingency_fl_id])
REFERENCES [dbo].[fl] ([bl_id], [fl_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_contingency_fl_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_contingency_rm_id] FOREIGN KEY([contingency_bl_id], [contingency_fl_id], [contingency_rm_id])
REFERENCES [dbo].[rm] ([bl_id], [fl_id], [rm_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_contingency_rm_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_dp_id] FOREIGN KEY([dv_id], [dp_id])
REFERENCES [dbo].[dp] ([dv_id], [dp_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_dp_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_dv_id] FOREIGN KEY([dv_id])
REFERENCES [dbo].[dv] ([dv_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_dv_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_em_std] FOREIGN KEY([em_std])
REFERENCES [dbo].[emstd] ([em_std])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_em_std]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_fl_id] FOREIGN KEY([bl_id], [fl_id])
REFERENCES [dbo].[fl] ([bl_id], [fl_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_fl_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_net_id] FOREIGN KEY([net_id])
REFERENCES [dbo].[net] ([net_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_net_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_rm_id] FOREIGN KEY([bl_id], [fl_id], [rm_id])
REFERENCES [dbo].[rm] ([bl_id], [fl_id], [rm_id])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_rm_id]
GO
ALTER TABLE [dbo]. WITH CHECK ADD CONSTRAINT [em_tc_level] FOREIGN KEY([tc_level])
REFERENCES [dbo].[afm_tclevel] ([tc_level])
GO
ALTER TABLE [dbo]. CHECK CONSTRAINT [em_tc_level]
Can anyone tell me which constraints I would need to drop in order to truncate and reload this table?
January 6, 2010 at 10:50 am
The error message that is returned when you try to Truncate the table will have the name of the constraint that is the problem.
You may have to drop most if not all of the FK constraints as this will be preventing the data from being deleted as it will violate ref integrity.
January 6, 2010 at 11:47 am
You can disable the constraints and then reenable them with the check option which will revalidate all the data.
I also only transfer new data based on a date in the linked table... get the max here and fetch everything over that.
That way you flush the delete and remove most of the insert and constraint checks.
January 7, 2010 at 3:53 am
I maybe mistaken but I'm sure I read somewhere that TRUNCATE TABLE will be rejected if there is an FK pointing to the target table even if the FK table is empty.
January 7, 2010 at 6:59 am
Grinja (1/7/2010)
I maybe mistaken but I'm sure I read somewhere that TRUNCATE TABLE will be rejected if there is an FK pointing to the target table even if the FK table is empty.
yes you're right about that, I guess I was thinking about something else. I know you can go around the constraint in a few loading methods, but this is not the case here.
January 7, 2010 at 9:10 am
Jpotucek (1/6/2010)
Can anyone tell me which constraints I would need to drop in order to truncate and reload this table?
You don't need to drop the primary key constraint, but you will need to drop the foreign key constraints that reference dbo.em as well as the FK constraints on dbo.em that reference other tables.
Since dbo.em references so many other tables, you should be careful that whatever data you into dbo.em exists in the referenced tables. Otherwise, you'll get FK violations when you recreate the constraints.
Greg
January 7, 2010 at 12:22 pm
More to the point... this is really an ETL task...
Download data, validate it first, then reinsert the valid data and warn about problems.
No real need to flush out the fks for import since there are there to validate the data. Since it needs to be done anyways, there's no real gain in doing that.
Just my 0.02$
January 11, 2010 at 9:04 am
When I am reloading my test database (SS 2000), I run the following query first:
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
go
(I disable the triggers because I use them for adding to Audit Trail tables.)
I then use DTS to reload the tables with deletes occuring before the data is copied. When the data has been reloaded, I run the following:
sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
go
sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
go
Steve
January 11, 2010 at 9:35 am
Awesome !! Thank you all for your help!
January 11, 2010 at 10:50 am
Ya that's what I meant in my first message... but how do you do error handling in this method?
I mean once you start reenabling all the constraints, it must fail once in a while (unless you prevalidated the data).
January 11, 2010 at 11:11 am
The Data is coming from our production database where all the Constraints are in force. I've never had it fail for me. (I only do this once every few months).
Steve
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply