none
Adding 2 primary keys in mdf database in VS Community 2013 from Server Explorer

    Question

  • Hello,

    I have to update a Windows Forms application using VS Community 2013. I have to add a little database with customers and installations, so 1 customer can manage many installations and 1 installation can be managed by many customers. It is the first time that use VS 2013 so I don't know if I have done all steps well:

    • Right click in the project item -> Add -> New item -> Data -> Service-base Database. I don't know what "service-based" means, but is the only way I can see to add a database. This step added mydb.mdf file to the project.
    • Server Explorer -> Data Connections -> mydb.mdf -> Right click on Tables -> Add New Table. I have added Customer and Installation tables with no problem. These are T-SQL scripts:

    CREATE TABLE [dbo].[Customer]
    (
        [UserName] NVARCHAR(50) NOT NULL PRIMARY KEY, 
        [Password] NVARCHAR(50) NOT NULL, 
        [FullName] NVARCHAR(50) NOT NULL, 
        [Company] NVARCHAR(50) NULL, 
        [Contact] NVARCHAR(100) NULL
    )
    
    CREATE TABLE [dbo].[Installation]
    (
        [Imei] NVARCHAR(15) NOT NULL PRIMARY KEY, 
        [Ip] NVARCHAR(15) NOT NULL, 
        [Name] NVARCHAR(50) NOT NULL
    )

    Now, I have to create CustomerInstallation table with 2 primary keys. The script:

    CREATE TABLE [dbo].[CustomerInstallation]
    (
        [UserName] NVARCHAR(50) NOT NULL , 
        [Imei] NVARCHAR(15) NOT NULL, 
        PRIMARY KEY ([UserName]),
        PRIMARY KEY ([Imei]), 
        CONSTRAINT [FK_CustomerInstallation_Customer] FOREIGN KEY ([UserName]) REFERENCES [Customer]([UserName]),
        CONSTRAINT [FK_CustomerInstallation_Installation] FOREIGN KEY ([Imei]) REFERENCES [Installation]([Imei])
    )

    When I click the Update button, I get the following error message:

    Update cannot proceed due to validation errors.
    Please correct the following errors and try again.

    SQL71533 :: A table or table-valued function ([dbo].[CustomerInstallation]) contains more than one primary key.
    SQL71531 :: The table or view ([dbo].[CustomerInstallation]) has more than one clustered index.

    So my question is: How can I add 2 primary keys to the table? Is it posible with this "service-based" database?

    Thank you for all,

    Jon.

    PD: I didn't know in what forum write this question, please, move this thread to the appropriate forum. Sorry for the inconvenience.

    • Edited by Jon 123 Wednesday, February 11, 2015 10:32 AM
    Wednesday, February 11, 2015 10:24 AM

Answers

  • I found the correct forum for this thread (SQL Server > SQL Server Data Tools), I have already written there.
    • Marked as answer by Jon 123 Wednesday, February 11, 2015 12:15 PM
    Wednesday, February 11, 2015 12:15 PM