Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: Foreign Key constraint key conflict

    Hi

    I have these tables as follows:

    StudentFinance
    ---------------
    - financeID (PK)
    - financeEntityID (FK)
    - studentID (FK)
    - entityID (FK)

    FinanceEntity
    -------------
    - financeEntityID (PK)
    - name

    FinanceEntity table has rows like :
    - deposit
    - payment method
    - etc

    each of these are tables and their respective PKs are foreign keys in StudentFinance represented by entityID. These tables have FK constraints to other tables. The problem is when I try to add a row to StudentFinance I get a number of 'Foreign key constraint conflicts with other tables'. Point to note that most of these tables are empty at the moment. How can this be resolved and where should i start inserting rows first? Thanks.
    Last edited by kpeeroo; 11-26-09 at 23:01.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    The problem is when I try to add a row to StudentFinance I get a number of 'Foreign key constraint conflicts with other tables'.
    You will first have to populate the referenced tables with the values you will be using.

    If You will use FinanceEntityA, you will first have to add FinanceEntityA to the FinanceEntity table.
    If You will use Student 'John', you will first have to add Student 'John' to the Student table.
    And so on...

    This is a recursive process: When you want to add Student 'John' to the Student table, you may need to supply his place of birth, so you may first have to populate the Villages table before you will be able to add 'John'.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    Thanks for the reply. That is what I tried to do but in vain. I take the entity 'Deposit' for example:

    StudentFinance
    ---------------
    financeID-----financeEntityID----studentID----entityID
    1---------------1--------------1------------1

    Deposit
    -------
    depositID-----studentID---------deposit-------date
    1------------1---------------1000--------27/11/09

    CreditCard
    ----------
    ccID---------cardTypeID-------accountNumber-----cvc---expiryDate
    NULL----------NULL--------------NULL-------------NULL----NULL

    depositID and ccID are both FK to entityID. But when I try to enter the StudentFinance first row as shown above, it conflicts with CreditCard ccID 'foreign key constraint'? Any idea why? Thanks

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    depositID and ccID are both FK to entityID.
    I don't see what you mean, given the sample data. Perhaps the naming is putting me off.

    Can you give the DDL of all the tables and foreign keys? Perhaps a graphical representation too of the relations between the tables, if you have it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    Thanks for your reply and sorry for the confusion wim. Here is part of the database diagram. I've added just the finance section. I'm using VWD 2008 and exporting diagrams is quite a pain. I hope you will get the diagram properly there. Just click on it and zoom. Thanks.
    Attached Thumbnails Attached Thumbnails finance_dbtables.jpg   finance_dbtables_zoom.jpg  
    Last edited by kpeeroo; 11-27-09 at 11:58.

  6. #6
    Join Date
    Jul 2009
    Posts
    168
    I think Mark did specify that the 'entityID' of the StudentFinance table will not be able to enforce foreign key constraint as it links up to many tables. This was the thread:
    http://www.dbforums.com/database-con...ml#post6429744

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •