nup
asked on
truncate tables with foreign key reference
I have three tables in sql server database. Table1 as:
CREATE TABLE [IA_report] (
[report_id] [int] IDENTITY (1, 1) NOT NULL ,
[report_yr] [smallint] NOT NULL ,
[report_nbr] [smallint] NOT NULL ,
[finalized_dt] [datetime] NOT NULL ,
[committee_dt] [datetime] NOT NULL ,
[is_confidential_ind] [bit] NOT NULL ,
[type] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[keyword] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
CONSTRAINT [pk_IA_report] PRIMARY KEY NONCLUSTERED
(
[report_id]
) ON [PRIMARY] ,
CONSTRAINT [fk1_IA_report] FOREIGN KEY
(
[type]
) REFERENCES [IA_type_ref] (
[type]
),
CONSTRAINT [fk2_IA_report] FOREIGN KEY
(
[keyword]
) REFERENCES [IA_keyword_ref] (
[keyword]
)
) ON [PRIMARY]
Table 2 as:
CREATE TABLE [IA_recommendation] (
[recommendation_id] [int] IDENTITY (1, 1) NOT NULL ,
[report_id] [int] NOT NULL ,
[recommendation_nbr] [smallint] NOT NULL ,
[completion_dt] [datetime] NULL ,
[status] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[risk] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[division] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
[recommendation] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_ AS NOT NULL ,
CONSTRAINT [pk_IA_recommendation] PRIMARY KEY NONCLUSTERED
(
[recommendation_id]
) ON [PRIMARY] ,
CONSTRAINT [fk1_IA_recommendation] FOREIGN KEY
(
[report_id]
) REFERENCES [IA_report] (
[report_id]
),
CONSTRAINT [fk2_IA_recommendation] FOREIGN KEY
(
[status]
) REFERENCES [IA_status_ref] (
[status]
),
CONSTRAINT [fk3_IA_recommendation] FOREIGN KEY
(
[risk]
) REFERENCES [IA_risk_ref] (
[risk]
),
CONSTRAINT [fk4_IA_recommendation] FOREIGN KEY
(
[division]
) REFERENCES [IA_division_ref] (
[division]
)
) ON [PRIMARY]
How do I truncate (not delete) these tables?
CREATE TABLE [IA_report] (
[report_id] [int] IDENTITY (1, 1) NOT NULL ,
[report_yr] [smallint] NOT NULL ,
[report_nbr] [smallint] NOT NULL ,
[finalized_dt] [datetime] NOT NULL ,
[committee_dt] [datetime] NOT NULL ,
[is_confidential_ind] [bit] NOT NULL ,
[type] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_
[keyword] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_
[title] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_
CONSTRAINT [pk_IA_report] PRIMARY KEY NONCLUSTERED
(
[report_id]
) ON [PRIMARY] ,
CONSTRAINT [fk1_IA_report] FOREIGN KEY
(
[type]
) REFERENCES [IA_type_ref] (
[type]
),
CONSTRAINT [fk2_IA_report] FOREIGN KEY
(
[keyword]
) REFERENCES [IA_keyword_ref] (
[keyword]
)
) ON [PRIMARY]
Table 2 as:
CREATE TABLE [IA_recommendation] (
[recommendation_id] [int] IDENTITY (1, 1) NOT NULL ,
[report_id] [int] NOT NULL ,
[recommendation_nbr] [smallint] NOT NULL ,
[completion_dt] [datetime] NULL ,
[status] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_
[risk] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_
[division] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_
[recommendation] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_
CONSTRAINT [pk_IA_recommendation] PRIMARY KEY NONCLUSTERED
(
[recommendation_id]
) ON [PRIMARY] ,
CONSTRAINT [fk1_IA_recommendation] FOREIGN KEY
(
[report_id]
) REFERENCES [IA_report] (
[report_id]
),
CONSTRAINT [fk2_IA_recommendation] FOREIGN KEY
(
[status]
) REFERENCES [IA_status_ref] (
[status]
),
CONSTRAINT [fk3_IA_recommendation] FOREIGN KEY
(
[risk]
) REFERENCES [IA_risk_ref] (
[risk]
),
CONSTRAINT [fk4_IA_recommendation] FOREIGN KEY
(
[division]
) REFERENCES [IA_division_ref] (
[division]
)
) ON [PRIMARY]
How do I truncate (not delete) these tables?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, but delete will not reset the index. So, how do I reseed?
Do I need to have permission to reseed?
Do I need to have permission to reseed?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
have you tried this script from here
http://sql-server-performance.com/Community/forums/p/20593/114949.aspx
it seems to recursevly truncate your tables
Nicolas
http://sql-server-performance.com/Community/forums/p/20593/114949.aspx
it seems to recursevly truncate your tables
Nicolas
ASKER