Truncate table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Truncate table

Author

Topic

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-07 : 07:07:54

I have two tables Table1 and Table2

In which Table1 is a master table and Table2 is a transaction table.

Table2 has foreign key reference from Table1

I can truncate table2 but when i try to truncate table1 systems throughs error but instead I delete. It deletes.

To reset the Identity I need to truncate the Table1 How do I do it ? with out dropping the foreign key constraint.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 07:11:08

Try the DBCC CHECKIDENT command

quote:

DBCC CHECKIDENT 
( 
        table_name
        [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-07 : 07:12:08

dbcc CHECKIDENT (tablename,reseed,0)

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-07 : 07:14:33

dbcc checkident('tbl_name',reseed,0)

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-07 : 07:15:06

First U can truncate Table2 and then truncate the Table1,
becoz primary key and foreign key relations are in ur tables

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-07 : 07:18:10

For reset the identity -- Peso,jai's,bklr's suggested Correct one.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-07 : 08:18:58

Thanks for your comments. It's working fine I reset the identity based on your coding.

But Is there any way to truncate Table1 rather than to reseed the master table.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-07 : 08:43:39

I think there is one way

1. Truncate transaction table
2. Drop Foreign key constraint on transaction table which is referring to master table
3. Truncate master table
4. ReCreate foreign key constraint on transaction table

Follow the above steps in the same order...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 08:50:47

quote:
Originally posted by karthik_padbanaban

Thanks for your comments. It's working fine I reset the identity based on your coding.

But Is there any way to truncate Table1 rather than to reseed the master table.

you need to make sure you dont have any records in table2 that refers to records you're trying to delete from table1. In case, the record trying to delete is referred, it will cause error. Otherwise, you should be creating foreign key with ON DELETE CASCADE option.

B/w whats the significance of table2 records after you truncate table1 if table2 records are refering to table1?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-07 : 09:14:06

quote:
Originally posted by visakh16

quote:
Originally posted by karthik_padbanaban

Thanks for your comments. It's working fine I reset the identity based on your coding.

But Is there any way to truncate Table1 rather than to reseed the master table.

you need to make sure you dont have any records in table2 that refers to records you're trying to delete from table1. In case, the record trying to delete is referred, it will cause error. Otherwise, you should be creating foreign key with ON DELETE CASCADE option.

B/w whats the significance of table2 records after you truncate table1 if table2 records are refering to table1?

I Truncate Table2 which is the transcation table.

Then I delete the master table Table1 because Truncate cause error here.

So I need to reseed the Table1 after deletion --- this is for preventing growth of Identity.

SO My Question is is there any way to truncate master table Table1 rather than deleting it and reseeding it.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-07 : 09:33:13

quote:
Originally posted by karthik_padbanaban

I have two tables Table1 and Table2

In which Table1 is a master table and Table2 is a transaction table.

Table2 has foreign key reference from Table1

I can truncate table2 but when i try to truncate table1 systems throughs error but instead I delete. It deletes.

To reset the Identity I need to truncate the Table1 How do I do it ? with out dropping the foreign key constraint.

I have mentioned with out droping the constraint in my first post so i cant do it.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-07 : 09:51:25

first truncate the transaction table(foreign key table)
then truncate the master table (primary key table)
if u use truncate, no need to reseed the table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 09:55:53

bklr, we have repeatedly written that this is not possible due to foreign key constraint!
If you don't know, please don't post false information. Are you by-the-way employed by Satyam?

See this example

CREATE TABLE	t1
		(
			col1 INT PRIMARY KEY
		)

CREATE TABLE	t2
		(
			col1 INT FOREIGN KEY REFERENCES t1(col1),
			col2 INT
		)
		
INSERT	t1
SELECT	1

INSERT	t2
SELECT	1, 2

SELECT	*
FROM	t1

SELECT	*
FROM	t2

truncate table t2

truncate table t1	-- Error !!!

DROP TABLE	t2,
		t1

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-07 : 09:57:20

quote:
Originally posted by bklr

first truncate the transaction table(foreign key table)
then truncate the master table (primary key table)
if u use truncate, no need to reseed the table

Hi bklr,

u can't truncate master table when there is a foreign key constraint on transaction table....

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-07 : 09:57:53

quote:
Originally posted by bklr

first truncate the transaction table(foreign key table)
then truncate the master table (primary key table)
if u use truncate, no need to reseed the table

By doing so causes error like this.

Cannot truncate table 'Table1' because it is being referenced by a FOREIGN KEY constraint.

note : I have no records in the transcation table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 09:58:34

quote:
Originally posted by bklr

first truncate the transaction table(foreign key table)
then truncate the master table (primary key table)
if u use truncate, no need to reseed the table

please try to read and learn from earlier posted suggestions and dont keep repeating wrong posts.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 09:59:42

quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by bklr

first truncate the transaction table(foreign key table)
then truncate the master table (primary key table)
if u use truncate, no need to reseed the table

By doing so causes error like this.

Cannot truncate table 'Table1' because it is being referenced by a FOREIGN KEY constraint.

note : I have no records in the transcation table

truncate wont work when you've table being referenced by fk constraint.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-01-07 : 10:00:48

quote:
Originally posted by raky

quote:
Originally posted by bklr

first truncate the transaction table(foreign key table)
then truncate the master table (primary key table)
if u use truncate, no need to reseed the table

Hi bklr,

u can't truncate master table when there is a foreign key constraint on transaction table....

This could be the reason so that I can't truncate the master table.

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-07 : 10:01:00

The answer is already given with DBCC Checkident to reset Identity after delete.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 10:03:25

Yes.
But some people are really stubborn, or listens to badly given advices.

E 12°55'05.63"
N 56°04'39.26"

tosscrosby
Aged Yak Warrior

676 Posts