none
Entity Framework 'Update Model From Database' is not picking up Foreign Keys RRS feed

  • Question

  • After updating the model from the database, the tables are imported but the foreign keys are not.  The change to the database is at the end of this post.  I am using Visual Studio 2012 and SQL Server 2012.  I have had this problem after each change to the schema over the last three months.  I have been able to import the schema properly only after making the database over from scratch.  Do you have any suggestions?


    CREATE TABLE DownTimeType (downTimeTypeId int IDENTITY(1, 1) NOT NULL, DownTimeCategoryId int NOT NULL, downTimeTypeDescription varchar(40) NOT NULL, PRIMARY KEY (downTimeTypeId));

    CREATE TABLE DowntimeEquipment (downtimeEquipmentId int IDENTITY(1, 1) NOT NULL, downtimeId int NOT NULL, equipmentID int NOT NULL, primaryFailureDevice bit NOT NULL, PRIMARY KEY (downtimeEquipmentId));

    CREATE TABLE Downtime (downtimeId int IDENTITY(1, 1) NOT NULL, downtimeDescription int NULL, dowtimeStart datetime NOT NULL, downtimeEnd datetime NULL, PersonID int NOT NULL, downTimeTypeId int NOT NULL, PRIMARY KEY (downtimeId));

    CREATE TABLE DownTimeEquipmentType (downTimeEquipmentTypeId int NULL, downTimeTypeId int NOT NULL, equipmentTypeID int NOT NULL);

    CREATE TABLE DownTimeCategory (downTimeCategoryId int IDENTITY(1, 1) NOT NULL, downTypeCategoryDescription varchar(40) NOT NULL, PRIMARY KEY (downTimeCategoryId));

    CREATE UNIQUE INDEX DownTimeType_downTimeTypeId ON DownTimeType (downTimeTypeId);

    CREATE UNIQUE INDEX DowntimeEquipment_downtimeEquipmentId ON DowntimeEquipment (downtimeEquipmentId);

    CREATE UNIQUE INDEX Downtime_downtimeId ON Downtime (downtimeId);

    CREATE UNIQUE INDEX DownTimeCategory_downTimeCategoryId ON DownTimeCategory (downTimeCategoryId);

    CREATE UNIQUE INDEX DownTimeType_downTimeTypeId ON DownTimeType (downTimeTypeId);

    ALTER TABLE Downtime ADD CONSTRAINT FKDowntime815189 FOREIGN KEY (downTimeTypeId) REFERENCES DownTimeType (downTimeTypeId);

    ALTER TABLE Downtime ADD CONSTRAINT FKDowntime462076 FOREIGN KEY (PersonID) REFERENCES Person (PersonID);

    ALTER TABLE DowntimeEquipment ADD CONSTRAINT FKDowntimeEq399935 FOREIGN KEY (equipmentID) REFERENCES Equipment (equipmentID);

    ALTER TABLE DowntimeEquipment ADD CONSTRAINT FKDowntimeEq836126 FOREIGN KEY (downtimeId) REFERENCES Downtime (downtimeId);

    ALTER TABLE DownTimeEquipmentType ADD CONSTRAINT FKDownTimeEq728694 FOREIGN KEY (equipmentTypeID) REFERENCES EquipmentType (equipmentTypeID);

    ALTER TABLE DownTimeEquipmentType ADD CONSTRAINT FKDownTimeEq101050 FOREIGN KEY (downTimeTypeId) REFERENCES DownTimeType (downTimeTypeId);

    ALTER TABLE DownTimeType ADD CONSTRAINT FKDownTimeTy140122 FOREIGN KEY (DownTimeCategoryId) REFERENCES DownTimeCategory (downTimeCategoryId);

    ALTER TABLE Activity ADD CONSTRAINT FKActivity925770 FOREIGN KEY (processUnitEquipmentTypeID) REFERENCES ProcessUnitEquipmentType (processUnitEqupmentTypeID);

    I realized that I was missing a primary Key, so I added the following:

    ALTER TABLE DownTimeEquipmentType alter column downTimeEquipmentTypeId int NOT NULL;
    ALTER TABLE DownTimeEquipmentType ADD UNIQUE (downTimeEquipmentTypeId);
    CREATE UNIQUE INDEX DownTimeEquipmentType_downTimeEquipmentTypeId ON DownTimeEquipmentType (downTimeEquipmentTypeId);

    I recreated the database from scratch and the refresh model worked perfectly.  Here are the lines from the schema creation for the new database:

    CREATE TABLE DownTimeType (downTimeTypeId int IDENTITY(1, 1) NOT NULL, DownTimeCategoryId int NOT NULL, downTimeTypeDescription varchar(40) NOT NULL, PRIMARY KEY (downTimeTypeId));

    CREATE TABLE DownTimeCategory (downTimeCategoryId int IDENTITY(1, 1) NOT NULL, downTypeCategoryDescription varchar(40) NOT NULL, PRIMARY KEY (downTimeCategoryId));

    CREATE TABLE DownTimeEquipmentType (downTimeEquipmentTypeId int IDENTITY(1, 1) NOT NULL, downTimeTypeId int NOT NULL, equipmentTypeID int NOT NULL, PRIMARY KEY (downTimeEquipmentTypeId));

    CREATE TABLE Downtime (downtimeId int IDENTITY(1, 1) NOT NULL, downtimeDescription int NULL, dowtimeStart datetime NOT NULL, downtimeEnd datetime NULL, PersonID int NOT NULL, downTimeTypeId int NOT NULL, PRIMARY KEY (downtimeId));

    CREATE TABLE DowntimeEquipment (downtimeEquipmentId int IDENTITY(1, 1) NOT NULL, downtimeId int NOT NULL, equipmentID int NOT NULL, primaryFailureDevice bit NOT NULL, PRIMARY KEY (downtimeEquipmentId));

    ALTER TABLE DownTimeType ADD CONSTRAINT FKDownTimeTy140122 FOREIGN KEY (DownTimeCategoryId) REFERENCES DownTimeCategory (downTimeCategoryId);

    ALTER TABLE DownTimeEquipmentType ADD CONSTRAINT FKDownTimeEq101050 FOREIGN KEY (downTimeTypeId) REFERENCES DownTimeType (downTimeTypeId);

    ALTER TABLE DownTimeEquipmentType ADD CONSTRAINT FKDownTimeEq728694 FOREIGN KEY (equipmentTypeID) REFERENCES EquipmentType (equipmentTypeID);

    ALTER TABLE DowntimeEquipment ADD CONSTRAINT FKDowntimeEq836126 FOREIGN KEY (downtimeId) REFERENCES Downtime (downtimeId);

    ALTER TABLE DowntimeEquipment ADD CONSTRAINT FKDowntimeEq399935 FOREIGN KEY (equipmentID) REFERENCES Equipment (equipmentID);

    ALTER TABLE Downtime ADD CONSTRAINT FKDowntime462076 FOREIGN KEY (PersonID) REFERENCES Person (PersonID);

    ALTER TABLE Downtime ADD CONSTRAINT FKDowntime815189 FOREIGN KEY (downTimeTypeId) REFERENCES DownTimeType (downTimeTypeId);

    CREATE UNIQUE INDEX DownTimeType_downTimeTypeId ON DownTimeType (downTimeTypeId);

    CREATE UNIQUE INDEX DownTimeCategory_downTimeCategoryId ON DownTimeCategory (downTimeCategoryId);

    CREATE UNIQUE INDEX DownTimeEquipmentType_downTimeEquipmentTypeId ON DownTimeEquipmentType (downTimeEquipmentTypeId);

    CREATE UNIQUE INDEX Downtime_downtimeId ON Downtime (downtimeId);

    CREATE UNIQUE INDEX DowntimeEquipment_downtimeEquipmentId ON DowntimeEquipment (downtimeEquipmentId);

    Do you think that the order of the Alter vs the Create Unique Index matters?


    The Real Flash Gordon




    Wednesday, March 20, 2013 1:27 PM

All replies

  • You appear to have posted to the wrong forum??

    I'm moving this to the Visual Studio forums, where it may get a more useful response :)


    Noel Paton | Nil Carborundum Illegitemi | CrashFixPC | The Three-toed Sloth
    No - I do not work for Microsoft, or any of its contractors.

    Wednesday, March 20, 2013 3:47 PM
  • Hi,

    I involved the specific Entity Framework expert to further look at this issue. I am moving your thread into the ADO.NET Entity Framework and LINQ to Entities Forum for dedicated support. Thanks for your understanding.

    Best Regards,


    Jack Zhai [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 22, 2013 6:11 AM
  • Hi  Gordon,

    I am not very clear about your problem. Do you mean you changed the database and refresh the entity data model, but Entity Framework does not find refresh the changes?

    It seems that the tsql you used is creating a unique index instead of primary key or foreign key.

    Good day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 26, 2013 2:50 AM
  • You are correct.  I changed the database and refreshed the model, the the EF does not find the foreign keys.

    Here is some of the code:

    ALTER TABLE DownTimeType ADD CONSTRAINT FKDownTimeTy140122 FOREIGN KEY (DownTimeCategoryId) REFERENCES DownTimeCategory (downTimeCategoryId);

    ALTER TABLE DownTimeEquipmentType ADD CONSTRAINT FKDownTimeEq101050 FOREIGN KEY (downTimeTypeId) REFERENCES DownTimeType (downTimeTypeId);

    ALTER TABLE DownTimeEquipmentType ADD CONSTRAINT FKDownTimeEq728694 FOREIGN KEY (equipmentTypeID) REFERENCES EquipmentType (equipmentTypeID);

    ALTER TABLE DowntimeEquipment ADD CONSTRAINT FKDowntimeEq836126 FOREIGN KEY (downtimeId) REFERENCES Downtime (downtimeId);

    ALTER TABLE DowntimeEquipment ADD CONSTRAINT FKDowntimeEq399935 FOREIGN KEY (equipmentID) REFERENCES Equipment (equipmentID);

    ALTER TABLE Downtime ADD CONSTRAINT FKDowntime462076 FOREIGN KEY (PersonID) REFERENCES Person (PersonID);

    ALTER TABLE Downtime ADD CONSTRAINT FKDowntime815189 FOREIGN KEY (downTimeTypeId) REFERENCES DownTimeType (downTimeTypeId);


    Does this code not define foreign keys?  This is pasted from above.  What statement should I use instead?

    Thanks!


    The Real Flash Gordon

    Thursday, March 28, 2013 2:54 PM