Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL

Posted on 2011-05-10
27
Medium Priority
?
237 Views
Last Modified: 2012-05-11
Hello,

If the tables include forgin key and index I can not drop or trunate talbes.
How I can do that?

Thanks
0
Comment
Question by:samprg
  • 14
  • 7
  • 2
  • +4
27 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35734327

You start by truncating or dropping the referenced table then the master table
0
 

Author Comment

by:samprg
ID: 35734444
THanks
But I don't need drop all the tables
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35734447
>>But I don't need drop all the tables

Is that a statement or a question?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:samprg
ID: 35734467
Question?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35734487
So, no, you don't need to drop all the tables. As he said, you can either:

1) Disable constraints, delete data, then re-enable constraints
2) Delete/truncate in order of child first
0
 

Author Comment

by:samprg
ID: 35734500
So casn I drop tables after  Disable constraints?
0
 

Author Comment

by:samprg
ID: 35734518
Sory...
Can I drop table  after  Disable constraints?
0
 

Author Comment

by:samprg
ID: 35734533
I don't have  constraints,but I have  FOREIGN KEY and give error this below
Cannot truncate table 'customer' because it is being referenced by a FOREIGN KEY constraint.

Thanks  
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35734544
It depends on what your goal is. If your goal is to drop the table, then drop the constraint first. If your goal is just to clean rows from the table then disable/re-enable (or drop-recreate)
0
 

Author Comment

by:samprg
ID: 35734554
My goal is truncate tables, How I can disable or drop all  FOREIGN KEY?
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35734567
ALTER TABLE mytab NOCHECK CONSTRAINT ALL

-- do work here

ALTER TABLE mytab CHECK CONSTRAINT ALL
 
0
 

Author Comment

by:samprg
ID: 35734618
I did and give this error

because it is being referenced by a FOREIGN KEY constraint.
0
 

Author Comment

by:samprg
ID: 35734624
I can't truncate table,.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35734629
Obviously dropping constraints is too complicated for you, so just do:
DELETE YourTable

And move on.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35734636
Let me assume you disabled the constraints on the wrong table. You disable the constraints on the child table because that is where the foreign key is.

0
 

Author Comment

by:samprg
ID: 35734642
Yes on the chiled
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35734652
Is there more than one child?
0
 

Author Comment

by:samprg
ID: 35734663
Yes, and I need to truncate the child
0
 

Author Comment

by:samprg
ID: 35734673
I did for parent
ALTER TABLE parent NOCHECK CONSTRAINT ALL
and I cant truncate the child.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 35734681
You have it backwards. A parent does not point to a child.
0
 

Author Comment

by:samprg
ID: 35734704
OK,can you give steps to truncate a table ?
0
 
LVL 9

Expert Comment

by:anillucky31
ID: 35734935
Here are the steps to truncate the tables. in your case table name is customer.

1) Look for the primary key in Customer table.
2) Find out if this primary key is referenced in other table as foreign key.
3) If customer table primary key is referenced in other table, you can not truncate the table.
4) Remove the reference of primary key to other table.
5) Now use truncate table Customer  to truncate table
6) After truncating table you can again reference customer table primary key in to other table.
0
 

Author Comment

by:samprg
ID: 35735048
Thank you so much,
for remove the reference I will run
ALTER TABLE mytable NOCHECK CONSTRAINT ALL

That's right?

Thank you again
0
 
LVL 9

Accepted Solution

by:
anillucky31 earned 2000 total points
ID: 35735335
First you need to find out the foreign key with this command

SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME (f.referenced_object_id) = 'customer'


This query will give you foreign key relationship

result will be like this (if there are multiple foreign keys then more than one row will come)

Foreignkey                                      tablename         columnname       referencetablename    referencecolumnname
FK_foreigntable2_parenttable       foreigntable2       foreignid                 parenttable                       Id
FK_foreigntable_parenttable              foreigntable      foreignid               parenttable                       Id


Now you will know the tablename and foreginkey name for your customer table.

in my example i will do like this by seeing above result
ALTER TABLE [dbo].[foreigntable2] drop CONSTRAINT [FK_foreigntable2_parenttable]
ALTER TABLE [dbo].[foreigntable] drop CONSTRAINT [FK_foreigntable_parenttable]

after executing these command you refernce are removed. now you can truncate your customer table.

if you want to create these foreign keys again then just not down the result of query and you can add foreign key agians with folllwing commnd

ALTER TABLE [dbo].[foreigntable]
  WITH CHECK ADD CONSTRAINT [FK_foreigntable_parenttable] FOREIGN KEY([foreignid])
    REFERENCES [dbo].[parenttable] ([ID])

ALTER TABLE [dbo].[foreigntable2]
  WITH CHECK ADD CONSTRAINT [FK_foreigntable2_parenttable] FOREIGN KEY([foreignid])
    REFERENCES [dbo].[parenttable] ([ID])






SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME (f.referenced_object_id) = 'customer'


/*put your foreign key name and table name as given by above query*/

ALTER TABLE [dbo].[foreigntable2] drop CONSTRAINT [FK_foreigntable2_parenttable]
ALTER TABLE [dbo].[foreigntable] drop CONSTRAINT [FK_foreigntable_parenttable]


/*Not down the above constraints if you want to recreate them again*/

ALTER TABLE [dbo].[foreigntable] 
  WITH CHECK ADD CONSTRAINT [FK_foreigntable_parenttable] FOREIGN KEY([foreignid])
    REFERENCES [dbo].[parenttable] ([ID])

ALTER TABLE [dbo].[foreigntable2] 
  WITH CHECK ADD CONSTRAINT [FK_foreigntable2_parenttable] FOREIGN KEY([foreignid])
    REFERENCES [dbo].[parenttable] ([ID])

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35735500
If the tables are small, why don't you just delete the data as acperkins stated.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35736143
yes you cannot have to delete first references and then truncate the table.
0
 

Author Closing Comment

by:samprg
ID: 35742646
Thanks soooooooooooo much
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question