SQL Server Performance

FK column

Discussion in 'General Developer Questions' started by Reddy, Aug 31, 2005.

  1. Reddy New Member

    I have table2 with 3000 rows in it. Recently I added a column for it which is a FK for table1.Now that column appears to be NULL in the table2.
    How can I syncronize that column with the PK field of table1 so that the relation between the tables make sense.

    Thanks!
    "He laughs best who laughs last"

  2. Chappy New Member

    I dont understand, what can you use as a relation between the two tables if your FK is NULL?
    You would need a relation of some sort (even if its not defined as a key constraint) in order to join the tables in a sensible way, something like....

    update
    t2
    set
    t2.MyNewField = t1.SomeOtherField
    from
    table1 t1
    INNER JOIN table2 t2 ON (t1.?? = t2.??)
  3. Reddy New Member

    What I mean is there was no relation between the tables but now I assume that I need a relationship to these 2 tables. So I inserted a PK column of table1 into table2 and made a relationship.
    These 2 tables are already in use.
    How can I insert this field into table2 so that it has data which relates to the PK of table1

    Thanks!
    "He laughs best who laughs last"

  4. Reddy New Member

    OK I think I am confusing you,Let me ask u very clear

    I have 2 tables table1 and table2 which is in use and has some data in it and has no relationship between them.
    Now I need relationship between these 2 tables, how can I do tht without losing any data?


    Thanks!
    "He laughs best who laughs last"

  5. merrillaldrich New Member

    You'll have to add the column, then update all the records, to fill in the value for the foreign key, using some other criteria to match up the records in the two tables.

    --------------------------
    Yes, I am a geek. I know. I can't help it.
  6. Reddy New Member

    yeah even I thought the same thing but cud any one give me some update command that match up with this scenario.

    Thanks!
    "He laughs best who laughs last"

  7. Adriaan New Member

    Like merrillaldrich said, "fill in the value for the foreign key, using some other criteria to match up the records in the two tables."

    UPDATE FK_Table
    SET FK_Column = Main_Table.Key_Column
    FROM FK_Table
    INNER JOIN Main_Table
    ON .........................................

    Insert the criteria on the dotted line.
  8. Adriaan New Member

    ... and your biggest problem is probably that there is no existing connection between the two tables, so you're actually adding new data. It would then depend either on another column in your FK_Table, or on a third table, and the FK value is probably something you make up as you go along.
  9. Reddy New Member

    Adriaan
    Do u mean tht there shud be some column in the FK table that match up with a column in Main table,ie; having a column in both tables which are similar.

    Thanks!
    "He laughs best who laughs last"

  10. ghemant Moderator

    Hi Reddy,<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Reddy</i><br /><br />Adriaan<br />Do u mean tht there shud be some column in the FK table that match up with a column in Main table,ie; having a column in both tables which are similar.<br /><br />Thanks!<br />"He laughs best who laughs last"<br /><br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />what if you add column in table1 with int (identity) ,tehe same on table2 then remove identity from table2 then update appropriate records (with integration) and then create relation between table1 and table2 .. it will not loose you data <br />* before trying this i suggest to copy it as precausion<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Regards<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  11. Madhivanan Moderator

    Yes Thats correct

    Madhivanan

    Failing to plan is Planning to fail
  12. Adriaan New Member

    Does Table2 have a (set of) field(s) from which you can tell what goes into the new FK column? Like there's already a region code and a type of business, and this tells you the internal department (new FK)?

    Or does Table2 have a (set of) field(s) on which you can join another table, where you find a (set of) field(s) that tells you the new FK value?

    If there are no criteria like that, then the FK values will have to be done through data entry (users keying in data).
  13. Reddy New Member

    OK I'll give the structure of my tables and let me know how to update the table2 where I have added a FK column.
    Lets say I have 2 tables in this way.
    Table1
    (PK)DocumentHeadersIDint40
    FileHeadersIDint41
    DocNumchar160
    TrBatchPoNochar500
    RecordTypechar20
    TCodechar60
    HeaderAmountmoney80
    LineItemCountint40
    RefDocNumchar160
    HeaderLiquidationAmountmoney80
    PayDateBaseLineDatedatetime81
    DocDateInvoiceDatedatetime81
    PartnerNumchar100
    PartnerPayeechar100
    PaymentMethodchar20
    BankPartnerchar40
    PaymentMethodSupplchar20
    PaymentBlockchar10
    PaymentTermschar40
    HeaderTextchar500
    Noteschar790
    Parkchar10
    VendorValdnSkipchar10
    DocHeaderAddlDatachar650
    WarrNochar140
    BankNochar20
    ChkNochar80
    PayDatedatetime81
    PurDocNumchar100

    Table2
    (PK)DocumentTransactionsIDint40
    (FK)DocumentHeadersIDint41
    RecordTypechar20
    LineItemNumsmallint20
    Fundchar100
    GLAccountchar100
    CostCenterchar100
    IntOrderchar120
    WBSElementchar240
    Amountmoney80
    DrCrchar10
    Vendorchar100
    DueOndatetime81
    ItemTextchar500
    RefDocEMFundchar100
    EMFundLineItemsmallint20
    LiquidationAmountmoney80
    GAAPCodechar10
    ServicePeriodBeginDatedatetime81
    ServicePeriodEndDatedatetime81
    Assignmentchar180
    RefKey1char120
    RefKey2char120
    RefKey3char200
    TypeCodechar20
    DocDetailAddlDatachar380
    SetToCompletechar10


    Thanks!
    "He laughs best who laughs last"

  14. Reddy New Member

    Hemant
    what do u mean by "update appropriate records (with integration)"

    Thanks!
    "He laughs best who laughs last"

  15. Adriaan New Member

    I can't see any combination of fields in Table2 that look like they correspond to Table1, except RecordType. It looks like you should have had DocumentHeadersID as the FK in Table2 from the start, now there is no way of telling which rows in Table2 are related to which rows in Table1.
  16. Reddy New Member

    Yeah u r right, but the RecordType has diffrent data corresponding to the table.
    So how can I come out of this issue.

    Adriann, u know something..I have set up such relations as per requiements for a database which has 25 tables.It seems like I am in a gr8 problem.

    Thanks!
    "He laughs best who laughs last"

  17. Adriaan New Member

    Once you start entering data in two tables without a proper foreign key relationship, and no field(s) where the relationship is expressed in some other way, then there is no way of knowing which rows are related between the two tables.

    The entity of Table1 is a document, and that of Table2 is a transaction involving a document. You have 3000 transactions involving unnamed documents. - The transactions should never have been recorded without naming the document.
  18. Reddy New Member

    Hemant
    I didnt get you , can u make me clear how i can do tht?

    Adriaan
    If iam deleting all the records and inserting again, can I get the issue solved. How can I do INSERT after deleting all rows with these joins.

    Thanks!
    "He laughs best who laughs last"

  19. ghemant Moderator

    Hi Reddy ,


    quote:Table1
    Tab1Id int (identity,1,1) primary key
    (PK)DocumentHeadersIDint40
    FileHeadersIDint41
    DocNumchar160
    TrBatchPoNochar500
    RecordTypechar20
    TCodechar60
    HeaderAmountmoney80
    LineItemCountint40
    RefDocNumchar160
    HeaderLiquidationAmountmoney80
    PayDateBaseLineDatedatetime81
    DocDateInvoiceDatedatetime81
    PartnerNumchar100
    PartnerPayeechar100
    PaymentMethodchar20
    BankPartnerchar40
    PaymentMethodSupplchar20
    PaymentBlockchar10
    PaymentTermschar40
    HeaderTextchar500
    Noteschar790
    Parkchar10
    VendorValdnSkipchar10
    DocHeaderAddlDatachar650
    WarrNochar140
    BankNochar20
    ChkNochar80
    PayDatedatetime81
    PurDocNumchar100

    Table2
    Tab1Id int foreign key references table1(tab1Id)
    (PK)DocumentTransactionsIDint40
    (FK)DocumentHeadersIDint41
    RecordTypechar20
    LineItemNumsmallint20
    Fundchar100
    GLAccountchar100
    CostCenterchar100
    IntOrderchar120
    WBSElementchar240
    Amountmoney80
    DrCrchar10
    Vendorchar100
    DueOndatetime81
    ItemTextchar500
    RefDocEMFundchar100
    EMFundLineItemsmallint20
    LiquidationAmountmoney80
    GAAPCodechar10
    ServicePeriodBeginDatedatetime81
    ServicePeriodEndDatedatetime81
    Assignmentchar180
    RefKey1char120
    RefKey2char120
    RefKey3char200
    TypeCodechar20
    DocDetailAddlDatachar380
    SetToCompletechar10
    and by integrity means as you have mentioned that table2 has 3000 rows in it , then you have to check validation of existance of that records
    for e.g. now you have value in table1 as tab1id =1 , record type ='a' and in table2 tab1id=1 is their only if tab1id in table1 has correspondence value .


    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami

Share This Page