SQL71501: Primary Key Error

Question

Using SSMS in SQL Server 2012, I can make a table like this:

CREATE TABLE [dbo].[AircraftFuelTypes]

(
[AircraftFuelTypeKey] [UNIQUEIDENTIFIER] NOT NULL constraint DF_AircraftFuelTypes_AircraftFuelTypeKey DEFAULT(NEWSEQUENTIALID()),
[AircraftFuelTypeName] [NVARCHAR] (128)    NOT NULL,
       CONSTRAINT [PK_AircraftFuelTypes] PRIMARY KEY CLUSTERED ([AircraftFuelTypeKey] ASC)
)

However, within a project in Visual Studio 2012 Premium, that create table statement causes a build error:

SQL71501: Primary Key: [dbo].[PK_AircraftFuelTypes] has an unresolved reference to Column [dbo].[AircraftFuelTypes].[AircraftFuelTypeKey]

No amount of rebuilding solves this.  I have found a work-around, which is to place the PK constraint in-line with the column:

CREATE TABLE [dbo].[AircraftFuelTypes]

(
[AircraftFuelTypeKey] [UNIQUEIDENTIFIER] NOT NULL constraint DF_AircraftFuelTypes_AircraftFuelTypeKey DEFAULT(NEWSEQUENTIALID()) CONSTRAINT [PK_AircraftFuelTypes] PRIMARY KEY CLUSTERED,
[AircraftFuelTypeName] [NVARCHAR] (128)    NOT NULL
)

but I would prefer not to have to implement this as I have 150+ tables in the project written in this style, all of which throw an identical build error.  The project was migrated from Visual Studio 2010, where this syntax did not cause an error.

Is there a setting of some sort that will allow this syntax to build, or am I resigned to having to modify all of my create table statements?

By the way, I realize that it is customary to have every constraint in a separate file, but I find that style particularly offensive, as I much prefer having everything in one file where I can see it at the same time.  That is why I write create table statements as shown in my example with all constraints "in line."

Answer 1

Hi Charles,

I am sorry that I can't reproduce your issue on my side, with the above given SQL scripts I can build and publish my project successfully. I think maybe other configurations in your project prevent you building and publishing your project.

Would you mind send me one simple project which can reproduce your issue? I would like to see whether I will get the same error with it. You can send it to me via email: v-vison at Microsoft dot com.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hello.  Thank you for your reply.

I created a new, simple project with only this one table that I was going to send to you and sure enough, it builds with no errors.  I started looking at what the differences could be.

In the case of the large project that does not build, every time I open it I am informed that the project(s), (need migration).  As I mentioned, these projects are originally from VS 2010.  I do reload the project and allow the Review Project and Solution Changes dialog to do what it does.  The changes it reports are:

<?xml version="1.0" encoding="UTF-16"?><?xml-stylesheet type='text/xsl' href='_UpgradeReport_Files/UpgradeReport.xslt'?><UpgradeReport>
<Properties><Property Name="Solution" Value="ZEDS_DB">
</Property><Property Name="Solution File" Value="C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB.sln">
</Property><Property Name="Date" Value="Tuesday, September 04, 2012">
</Property><Property Name="Time" Value="9:09:05 AM">
</Property><Property Name="Log Number" Value="5">
</Property></Properties><Event ErrorLevel="3" Project="ZEDS_DB" Source="ZEDS_DB\ZEDS_DB.dbproj" Description="UnSafeUpgrade">
</Event><Event ErrorLevel="0" Project="C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\ZEDS_DB.dbproj" Source="ZEDS_DB\ZEDS_DB.dbproj" Description="Beginning Upgrade">
</Event><Event ErrorLevel="0" Project="C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\ZEDS_DB.dbproj" Source="ZEDS_DB\ZEDS_DB.dbproj" Description="The permissions file 'C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\Properties\Database.sqlpermissions' was converted to Transact-SQL in the file 'C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\Permissions_3.sql'.">
</Event><Event ErrorLevel="0" Project="C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\ZEDS_DB.dbproj" Source="ZEDS_DB\ZEDS_DB.dbproj" Description="Project was successfully converted: 'C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\ZEDS_DB.dbproj' to 'C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\ZEDS_DB.sqlproj'">
</Event><Event ErrorLevel="3" Project="C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\ZEDS_DB.dbproj" Source="ZEDS_DB\ZEDS_DB.dbproj" Description="Converted">
</Event><Event ErrorLevel="1" Project="C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\ZEDS_DB.dbproj" Source="ZEDS_DB\ZEDS_DB.dbproj" Description="In order to use the upgraded database project of type .sqlproj in Visual Studio 2010, install SQL Server Data Tools for Visual Studio 2010 from the following location: http://go.microsoft.com/fwlink/?LinkID=237127&amp;clcid=0x409. Some file types in Visual Studio 2010 database projects of type .dbproj may not be supported in SQL Server Data Tools database projects (.sqlproj). Please refer to the link for more information about SQL Server Data Tools.">
</Event><Event ErrorLevel="3" Project="C:\zeds\Development\mainline\ZEDS_DB\ZEDS_DB\ZEDS_DB.dbproj" Source="ZEDS_DB\ZEDS_DB.dbproj" Description="Converted">
</Event></UpgradeReport>

At this point, rebuilding the solution results in the PK errors I mentioned originally.

I cannot send you the entire project.  Is there a specific file that you would like to see, or could you speculate what the "other configurations" you mention may be?  It is interesting to me that the project created originally in 2012 has a very simple "Build" property sheet, whereas the one migrated from 2010 has a much more extensive property sheet.  I am curious as to why they may be different.

Thank you.

Answer 3

Hi Charles,

Thanks for your response. However, I still can't reproduce your issue even try to upgrade VS2010 database project to VS2012 SSDT project.

As far as I know that if you try opening SSDT project (.sqlproj) in VS2010, you will get a prompt to ask you install the required software. So in this current scenario, you need to download and install SSDT tools to open and work .sqlproj project in VS2010.

Go: http://msdn.microsoft.com/en-us/data/hh297027

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us