Post reply

Foreign Key referencing a column with no unique index or constraint

  • October 12, 2012 at 9:17 am

    #263281

    I ran into an interesting situation. I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem. I found FK's that are referencing columns that do not have a unique constraint or a unique index. I don't know the history of the database but was there a time in SQL Server history where this would have been possible? I scripted out the tables and created it in a test database. When I run the script to create the FK I get the following message. I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2. Any ideas?

    ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1])

    REFERENCES [dbo].[table2] ([col2])

    There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.

    ---------------------------------------------------------------
    Mike Hahn - MCSomething someday:-)
    Right way to ask for help!!
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    I post so I can see my avatar :hehe:
    I want a personal webpage 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

  • October 12, 2012 at 9:29 am

    #1548726

    i tried to duplicate this, and couldn't drop any unqiue constraints because of the foreign keys.

    is it possible that the database was originally a SQL 2000 database?

    in that case, becasue you could update/delete system tables directly, it was possible to go and delete things without properly cleaning up things with references; maybe that's what happened?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 12, 2012 at 9:35 am

    #1548730

    I wish I knew.. the only thing I know is that the application that uses the database is written in VB 6.0 and it allowed users to create objects in the database at will. It's an old application that's in the process of getting a make over. I just wanted to know if there was ANY way to get a FK to a non unique index/key field. I haven't been able to re-produce it and I don't have SQL 2000.

    ---------------------------------------------------------------
    Mike Hahn - MCSomething someday:-)
    Right way to ask for help!!
    http://www.sqlservercentral.com/articles/Best+Practices/61537/
    I post so I can see my avatar :hehe:
    I want a personal webpage 😎
    I want to win the lotto 😀
    I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

  • October 12, 2012 at 1:56 pm

    #1548824

    Is that FK in enabled or disabled state? Maybe on some version it is possible to create initially disabled FK's but I didn't tried.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply