SQL Server Add, Drop and ReName Foreign Keys function have a syntax error - by FHankFreeman

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 752083 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 7/2/2012 6:32:45 PM
Access Restriction Public


Using the following valid syntax, where I want to drop and add back the same Foreign Key,  I have found what I consider a bug in the Microsoft key name generation where an extra ']' right bracket character is needed.  The SS software requires this extract ']', which can be found at the literal string part of _[PremisesID]']')];, where the errant ']' is denoted in the string for clarity.
Therefore, I consider this a Microsoft bug.

  ALTER TABLE [APP].[Accounts] DROP  CONSTRAINT [FK1_Premises_PremisesID_(Accounts_[PremisesID]])];
 ALTER TABLE [APP].[Accounts] ADD CONSTRAINT [FK1_Premises_PremisesID_(Accounts_[PremisesID]])] FOREIGN KEY(PremisesID) REFERENCES  [APP].[Premises](PremisesID);

Additional information supporting my point of view.
•	If I query sys.objects the foreign key is properly named so the error is in the code to drop, add or rename the foreign key.
select * from sys.objects where name = 'FK1_Premises_PremisesID_(Accounts_[PremisesID])' and type ='F'
Sign in to post a comment.
Posted by Umachandar [MSFT] on 7/18/2012 at 11:18 AM
The behavior you are seeing is by design. Right bracket needs to be escaped while the left bracket doesn't. See below:


When you are using characters that are not listed as qualified identifiers SQL Server allows any character in the current code page to be used in a delimited identifier. However, indiscriminate use of special characters in an object name may make SQL statements and scripts difficult to read and maintain. For example, you can create a table with the name Employee], where the closing square bracket is part of the name. To do this you have to escape the closing square bracket using two more square brackets as shown in the following:

CREATE TABLE [Employee]]]
EmployeeID int IDENTITY (1,1) NOT NULL,
FirstName varchar(30),
LastName varchar(30)

Umachandar, SQL Programmability Team