July 30, 2010 at 9:32 pm
Hi all,
I have to DROP and RE CREATE all the foreign keys referenced by a column of a table x, to do some work on the table x.
But, I cannot work on table x because SQL Server indicated that the FK still exists.
When I checked the system tables, I found the value in sys.foreign_key_columns table for this FK still exists. But the value in sys.foreign_keys table is gone.
What could be the reason. Any ideas? Please help.
Thanks
John
July 30, 2010 at 11:35 pm
I beleive as there is an orphaned entry in the sys.foreign_key_columns table, which prevent the DROP of PK.
Is there any method to overcome this?
John
July 30, 2010 at 11:51 pm
I cannot DROP the PK due to this orphaned FK value in sys.foreign_key_columns.
Is there any method to overcome this? Like delet this entry?
John
July 31, 2010 at 1:59 am
can't you just drop the FK constraint on the dependant table(s) ?
Make sure you have scripted all PK/FK constraints you need for this operation.
play it safe, start with a full backup !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 31, 2010 at 3:22 am
Sounds like someone's been playing with the system tables, and I don't mean in a good way.
Was this database upgraded from SQL 2000? If so, was there a chance of someone doing direct system table updates on SQL 2000? (sp_configure 'allow updates', 1)
Please run this and post the results
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Just to check, you were dropping the foreign keys with the ALTER TABLE ... DROP CONSTRAINT syntax?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 2:44 am
Hi all,
Thanks for all your responses,
The issue was not with the orphaned value in the system table or any adhoc updates to the system table.
The issue was that there was a table with same table structure (and name) living under a different schema.
Lets say 2 schemas, dbo and x.
And there exists 2 tables dbo.table_y and x.table_y.
Causes of Issue:
The sys.foreign_key_columns gives only the table name (no schema_id).
I had generated the DROP FK script relating to this system Catalog view.
The FK CONSTRAINT name was same for both tables.
This caused the confusion.
While troubleshooting the issue, I manually checked the sys.foreign_keys Catalog view which gave the clear picture of schema_id information.
Then I dropped another FK too.
Thank u all
John
August 2, 2010 at 2:52 am
You could have fetched the schema using:
select object_schema_name(parent_object_id) as TheParentSchema
, object_name(parent_object_id) as TheParentObject
, object_schema_name(referenced_object_id) as TheRefSchema
, object_name(referenced_object_id) as TheRefObject
from sys.foreign_key_columns
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 2, 2010 at 6:02 am
Thanks,
object_schema_name() is useful in this context.
It may be worth noting that if the table is owned by a user (like user_schema_name.table_name), then we need to specify it as it is (as user_schema_name.table_name) while quering.
If we specify only table_name, then the information returned from the query would be for the table under 'dbo' schema only..:-)
Thanks
John
August 2, 2010 at 7:20 am
$partacu$ (8/2/2010)
It may be worth noting that if the table is owned by a user (like user_schema_name.table_name), then we need to specify it as it is (as user_schema_name.table_name) while quering.
It's not the owner of the table, it's the schema that the table is in. (used to be owner back on SQL 2000)
If we specify only table_name, then the information returned from the query would be for the table under 'dbo' schema only..:-)
Depends what the default schema of the user you are using is. If it's dbo, then yes. If it's something else, the table will result to a table in that schema first.
This is one reason why one should always qualify object names.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 7:26 am
Dup
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2010 at 7:50 am
$partacu$ (8/2/2010)
...If we specify only table_name, then the information returned from the query would be for the table under 'dbo' schema only...
One small addition to Gails reply:
If you have db-access through multiple windows groups (windows group membership), this "default schema" setting is ommitted.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply