0

Using SQL Server 2014, this query doesn't seem to work (ie, doesn't drop the foreign key)

IF (OBJECT_ID('FK', 'F') IS NOT NULL)
BEGIN
    ALTER TABLE my_table 
    DROP CONSTRAINT [FK]
END

but this one does

IF EXISTS(
SELECT  *
FROM    sys.foreign_keys
WHERE   name = 'FK')
BEGIN
    ALTER TABLE my_table 
    DROP CONSTRAINT [FK]
END

I'm trying to understand why. Especially because the first query did use to work.

Running this query does return the row with the foreign key (with type F)

select * from dbo.sysobjects o where o.type = 'F' and name = 'FK'

But running this doesn't print the Found foreign key statement

IF (OBJECT_ID('FK', 'F') IS NOT NULL)
BEGIN
    PRINT 'Found foreign key'
END

Results from suggested queries in comments

select LEN(name) as 'Len', CAST(name as varbinary(MAX)) as AsBinary, name from dbo.sysobjects o 
where o.type = 'F' and name = 'my_fk_name'

gives me

108 | 0x46004B005F0049........006F006E0049006400 | my_fk_name

and

select id from dbo.sysobjects o 
where o.type = 'F' and name = 'my_fk_name'

select OBJECT_NAME(TheObjectId)

gives me

my_fk_name

reggaemahn
  • 6,272
  • 6
  • 34
  • 59
  • You're just missing the object type parameter. Try IF (OBJECT_ID('FK', 'F') IS NOT NULL.... – Eric Brandt Apr 19 '18 at 01:14
  • @EricBrandt Sorry, I do have that in my query. Ctrl+c, Ctrl+v error. – reggaemahn Apr 19 '18 at 01:16
  • Hmm. Ok. What do you mean when you say the query used to work? Maybe knowing more about that would point to something. – Eric Brandt Apr 19 '18 at 01:37
  • could it be related to a permissions issue? Does the login have restricted rights? – Moe Sisko Apr 19 '18 at 04:09
  • @MoeSisko Nah, I'm running it under my login and it has SysAdmin access – reggaemahn Apr 19 '18 at 04:12
  • if you run: select * from dbo.sysobjects o where o.type = 'F' and name = 'YourFKName' do you get a row back? – Moe Sisko Apr 19 '18 at 04:18
  • @MoeSisko So, I ran that and it's only got more confusing for me. See the update to the question. – reggaemahn Apr 20 '18 at 03:59
  • Strange. Can you run: select LEN(name) as 'Len', CAST(name as varbinary(MAX)) as AsBinary, name from dbo.sysobjects o where o.type = 'F' and name = 'YourFKName' ,and paste the values of the 3 columns. – Moe Sisko Apr 20 '18 at 06:31
  • Can also try: select id from dbo.sysobjects o where o.type = 'F' and name = 'FK' , to get the object id. Then run: select OBJECT_NAME(TheObjectId) , and see if that returns anything. Where TheObjectId is whatever you got back from select id. – Moe Sisko Apr 20 '18 at 06:38
  • @MoeSisko Updated the question with results from those queries – reggaemahn Apr 23 '18 at 01:45
  • @JeevanJose can you try: OBJECT_SCHEMA_NAME(TheObjectId) . If the result is not dbo, then maybe try using the schema name in the call to OBJECT_ID as Emilio Lucas Ceroleni has suggested in a comment in one of the answers below. – Moe Sisko May 03 '18 at 03:49

1 Answers1

0

According to the documentation of OBJECT_ID the function only searches for objects that are "schema scoped", that is, they depend directly on a schema. You're using it to query a foreign key, which is dependen on its underlying table and not on any schema, therefore it'll never be found.

On the other way, the second query uses the appropriate view for checking the existence of the foreign key, where it'll always be found if it exists.

In fact, MSDN has almost your very same code as an example of this behavior, only for a trigger instead:

Important

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog``'.

Alejandro
  • 7,290
  • 4
  • 34
  • 59
  • 1
    If you read the documentation further, you'll see that foreign keys are an accepted object type (https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-2017). – Gordon Linoff Apr 19 '18 at 01:04
  • I mean OBJECT_ID did work for me before. Plus if you look at questions like [this one](https://stackoverflow.com/questions/482885/how-do-i-drop-a-foreign-key-constraint-only-if-it-exists-in-sql-server), OBJECT_ID seems to be the 'recommended' way to do this. Thoughts? – reggaemahn Apr 19 '18 at 01:06
  • You should specify the FK's schema. Check out the syntax: `OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] object_name' [ ,'object_type' ] )` – Emilio Lucas Ceroleni Apr 19 '18 at 01:25