locked
Unable to create foreign key: failed to retrieve data for this request RRS feed

  • Question

  • I have created a simple database using SQL Server 2012.  I am trying to create a foreign key. In the Relationships dialog box, when I click the ellipsis, I get the following error message:

    Failed to retrieve data for this request.

    An exception occurred while executing a Transact-SQL statement or batch.

    Invalid column name 'is_memory_optimized'.

    Any suggestions on what I could be doing wrong?

    Saturday, January 18, 2014 4:28 PM

Answers

  • I am trying reproduce the error

    create table t1 (id int not null primary key)

    create table t2 (col1 int foreign key references t1 (nonecol))

    Msg 1770, Level 16, State 0, Line 2
    Foreign key 'FK__t2__col1__0559BDD1' references invalid column 'nonecol' in referenced table 't1'.
    Msg 1750, Level 16, State 0, Line 2
    Could not create constraint. See previous errors.


    drop table t1,t2

    Looks like you try to create a FK references to not existed column.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Kalman Toth Friday, February 7, 2014 2:22 PM
    • Marked as answer by Kalman Toth Wednesday, February 12, 2014 12:13 AM
    Sunday, January 19, 2014 6:54 AM
  • use the database diagram.

    you should be able to do this drag and drop.

    but you have to 'uncheck' everything in

    options>designers.

    please see link below:

    http://www.mssqltips.com/sqlservertip/1816/getting-started-with-sql-server-database-diagrams/

    When you save the diagram it should be done in the physical db as well.

    Your Pk and FK must have the same datatype and field length.


    http://joeydj.com/



    • Edited by JoeyDj Wednesday, January 29, 2014 2:13 PM
    • Proposed as answer by Kalman Toth Friday, February 7, 2014 2:22 PM
    • Marked as answer by Kalman Toth Wednesday, February 12, 2014 12:13 AM
    Wednesday, January 29, 2014 2:03 PM

All replies

  • Can you try using ALTER TABLE... ADD CONSTRAINT command instead and see if it works?

    http://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, January 18, 2014 6:19 PM
  • I am trying reproduce the error

    create table t1 (id int not null primary key)

    create table t2 (col1 int foreign key references t1 (nonecol))

    Msg 1770, Level 16, State 0, Line 2
    Foreign key 'FK__t2__col1__0559BDD1' references invalid column 'nonecol' in referenced table 't1'.
    Msg 1750, Level 16, State 0, Line 2
    Could not create constraint. See previous errors.


    drop table t1,t2

    Looks like you try to create a FK references to not existed column.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Kalman Toth Friday, February 7, 2014 2:22 PM
    • Marked as answer by Kalman Toth Wednesday, February 12, 2014 12:13 AM
    Sunday, January 19, 2014 6:54 AM
  • use the database diagram.

    you should be able to do this drag and drop.

    but you have to 'uncheck' everything in

    options>designers.

    please see link below:

    http://www.mssqltips.com/sqlservertip/1816/getting-started-with-sql-server-database-diagrams/

    When you save the diagram it should be done in the physical db as well.

    Your Pk and FK must have the same datatype and field length.


    http://joeydj.com/



    • Edited by JoeyDj Wednesday, January 29, 2014 2:13 PM
    • Proposed as answer by Kalman Toth Friday, February 7, 2014 2:22 PM
    • Marked as answer by Kalman Toth Wednesday, February 12, 2014 12:13 AM
    Wednesday, January 29, 2014 2:03 PM