Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 752083
Opened: 7/2/2012 6:32:45 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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]])];
go
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'
Details
Sign in to post a comment.
Posted by Microsoft on 7/18/2012 at 11:18 AM
Hello,
The behavior you are seeing is by design. Right bracket needs to be escaped while the left bracket doesn't. See below:

http://msdn.microsoft.com/en-us/library/ms176027(v=SQL.105).aspx

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
Sign in to post a workaround.