Finding a Foreign Key Constraint

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

SQL Server 2000 Forums

SQL Server Development (2000)

Finding a Foreign Key Constraint

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-01 : 22:31:11

I'd like to truncate a table but

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

How can I find the constraint?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-02-01 : 22:37:30

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME='myTable' AND CONSTRAINT_TYPE='FOREIGN KEY'

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2005-02-02 : 01:14:51

sp_fkeys 'myTable'

Enjoy working

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-02 : 08:03:47

Thanks ...

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 10:00:46

I always think that having options is a good thing.. so here you go:
sp_helpconstraint tname
sp_help tname

rockmoose

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-02 : 14:20:19

rocko, you read

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

as

Cannot truncate table 'MyTable' because it has a FOREIGN KEY constraint.

(see vganesh76's reply)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-02 : 15:00:33

Umh, stoad...did not.

Have you seen this error before ?
>> Cannot truncate table 'MyTable' because it has a FOREIGN KEY constraint.
That's pretty strict imo

rockmoose

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-03 : 03:20:12

That's it. No such error. Try to truncate table northwind..Orders

and then, using sp_helpconstraint tname/sp_help tname, try to find the FK.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-03 : 03:57:35

Is it just me having had too many margeritas or what ?!

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

exec sp_helpconstraint orders
Table is referenced by foreign key                                                                                                                                                                                                                                                                           
----------------------------------------------------
Northwind.dbo.Order Details: FK_Order_Details_Orders

exec sp_help orders
Table is referenced by foreign key                                                                                                                                                                                                                                                                           
----------------------------------------------------
Northwind.dbo.Order Details: FK_Order_Details_Orders

exec sp_fkeys orders
FK_NAME
-----------------------
FK_Order_Details_Orders

rockmoose

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-03 : 06:16:20

rocko,

thank you very much... :(

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-03 : 10:31:42

You know, I never understood what the problem was in the first place.

Anyway what's cool is the following:
In QA, type "orders" highlight it, and press <alt><F1> :)

Well, medium cool.

rockmoose

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-02-03 : 10:50:53

Rockmoose, you're awsome. Thats <alt><f1> trick rocks!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-03 : 10:58:21

quote:
Originally posted by lazerath

Rockmoose, you're awsome. Thats <alt><f1> trick rocks!

Thank You, but the honor in this case goes to M$ i believe.
Tools->Customize

It's a good way to impress coworkers

rockmoose

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-03 : 12:59:30

rockmie,

you shniped me again! :|)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-03 : 14:54:51

quote:
Originally posted by Stoad

rockmie,

you shniped me again! :|)

We shuold have adrink sometime :)

rockmoose

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-04 : 06:38:44

heh... almost impossible to happen!

nothing can drive me out from where I keep being glued to.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-04 : 06:43:22

why not??

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-04 : 17:55:52

>> nothing can drive me out from where I keep being glued to.
Is there a clinical term for this condition

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-02-04 : 19:08:48

In Enterprise Manager, select the table, right-click, select Design Table, click the Properties icon, and select the Relationships tab to see related tables.

You can also create a Diagram in Enterprise Manager, add the table to the diagram, and select Add Releated Tables to see a visual view of related tables.

quote:
Originally posted by SamC

I'd like to truncate a table but

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

How can I find the constraint?

Codo Ergo Sum

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-05 : 05:58:46

quote:
Originally posted by rockmoose

>> nothing can drive me out from where I keep being glued to.
Is there a clinical term for this condition

rockmoose

lol, rockmie, maybe it's a sort of sick patriotism?
If seriously, I really despise those who leave their motherland seeking
for so-called better conditions of life. Especially those who cheat
authorities of the 1st rate countries pretending to be "refugees".

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-09 : 11:34:21

quote:
Originally posted by Stoad

quote:
Originally posted by rockmoose

>> nothing can drive me out from where I keep being glued to.
Is there a clinical term for this condition

rockmoose
lol, rockmie, maybe it's a sort of sick patriotism?
If seriously, I really despise those who leave their motherland seeking
for so-called better conditions of life. Especially those who cheat
authorities of the 1st rate countries pretending to be "refugees".

People abusing the system and/or exploiting other beings for personal gain
are truly worthy of despise.
There is so much crap going round in the world, done by people in power (representing the system) and citizens.
Patriot ? or a will to be honest, true and dependable.

I thought You were glued to your algorithms and pc!
I know I can have that kind of compulsive behavior from time to time

rockmoose

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources