-1

ALL,

This page talks in particular about dropping the foreign key from the table. In the "Example" section, Example D shows how to drop foreign key.

The problem is that the example uses the named foreign key constraint in which case it is very easy syntax.

However, SQL Server does not require the constraint name when creating a table with the foreign key.

So, I'm curious - is there an example somewhere on MSDN which shows how to drop un-named foreign key constraint? And, why the page I referenced doesn't provide such example?

TIA!

  • Will there be an explanation of why the question(s) was downvoted? It is very easy to go around and just click the down arrow, without an explanation. It is much harder to explain why the question is downvoted so it become improved. Or maybe even try to fix it yourself. – Igor Apr 27 '18 at 16:45
  • There is quite a bit of history/culture here. You can see in this meta post the various opinions and community consensus about this. For me, personally, I stopped explaining downvotes after a string of retaliatory behavior that is far worse than the original issue (though generally I hold off on down-voting until I've offered constructive criticism and it is ignored). Things might change, though. – Aaron Bertrand Apr 27 '18 at 16:55
  • @AaronBertrand, so then how does it work? Because even though it is downvoted, I just got "+7" for the reputation on this question... Shouldn't it go "+" if the question is upvoted and "-" if its downvoted? – Igor Apr 27 '18 at 16:58
  • There is some detail about how reputation points work here. In this case, you got -4 yesterday due to two down-votes (-2 * 2). Today, you got +5 for an up-vote, and then +2 for accepting an answer. Today's reputation had nothing to do with down-votes: i.stack.imgur.com/pT4FA.png – Aaron Bertrand Apr 27 '18 at 17:21
  • @AaronBertrand, but today's positive reputation is about the up-vote (at least partially), which correlates to my guess. Thank you for explanation. – Igor Apr 27 '18 at 17:50
4

There is no such statement, because the DROP CONSTRAINT option requires you to name the constraint you want to drop. So, no, I wouldn't expect it to be on MSDN.

While you can create a constraint without explicitly naming it, it still gets a name. This is just a convenience that is allowed presumably because most of the time you create a constraint for good and don't later need to know or care what the name is. Generally, it is considered bad practice to let the system give your constraints a name, with the exception of certain types of constraints on #temp tables (due to concurrency collisions).

I suspect the syntax is only still allowed today to prevent backward compatibility issues, rather than because it's a good idea. In fact, if I were in charge of the MSDN docs, I would not describe the method below to drop an unnamed constraint, because all it would do is encourage this bad practice to continue.

Anyway, to drop a constraint when you don't know the name, you need to either look it up manually (armed with the names of the tables and columns involved), or use dynamic SQL. Here's an incomplete but working example of the latter:

DECLARE @fkname sysname;

SELECT @fkname = fk.name FROM sys.foreign_keys AS fk
  INNER JOIN sys.objects AS parent
  ON fk.referenced_object_id = parent.object_id
  INNER JOIN sys.objects AS child
  ON child.object_id = fk.parent_object_id /* parent of FK, not of child row */
  WHERE parent.name = N'Parent table'
    AND child.name = N'Child table';

-- best practice: also join twice to sys.schemas to ensure proper schema, too

DECLARE @sql nvarchar(max) = N'ALTER TABLE dbo.[Child Table]
  DROP CONSTRAINT ' + QUOTENAME(@fkname) + N';';

PRINT @sql;

-- EXEC sys.sp_executesql @sql;

You can also have multiple foreign keys between the same two tables (and even FKs pointing to the same table), so you probably want other filters in there to make sure you drop the right FK. If you name your constraints instead of letting the system name them, this should become much easier.

  • please carefully read my first question: how to drop un-named foreign key constraint. Why not give a query (or a set of queries) which will perform such an operation with the explanation on why it is done this way? Did you take that query from MSDN? Or you just generate it based on you knowledge? – Igor Apr 25 '18 at 17:48
  • 1
    @Igor, I read your question, and am trying to help you solve your problem. If you want an answer for why such a facility does not exist, you're looking in the wrong place, as none of your peers here made that (non-)decision. – Aaron Bertrand Apr 25 '18 at 17:50
  • @igor this is a place where we help each other with information, and not only fix your work. Most of the times when I ask a question, people post a big text, and I can learn even more than I asked. – Racer SQL Apr 25 '18 at 17:51
  • 1
    @Igor it's the same for all constraints, and the very first example on that topic (for primary key) says: Because a constraint name is not specified, the system supplies the constraint name. – Aaron Bertrand Apr 25 '18 at 18:00
  • 2
    @Igor you didn't offend me, but be careful when you accuse people of not reading your question, especially when you might not have fully read their answer. :-) – Aaron Bertrand Apr 25 '18 at 18:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.