locked
When I have two tables, one having the foreign key of the other, how do I tie the values from one to another RRS feed

  • Question

  • I have two tables, the second one contains a foreign key to the other. When I insert a record into the one with a primary key, I want SQL to automatically insert a record into the table with the foreign key as well. I am currently doing that in code behind (vb). How do I do that with SQL?


    DCSSR


    • Edited by DCSSR Saturday, January 19, 2013 4:51 PM
    Saturday, January 19, 2013 4:49 PM

Answers

  • For the want of a better modelling tool at my disposal at this time, I'll use the good ol' Excel :-)


    Though both of our implementations are debatable for pros and cons, I'll go for my idea of normalized tables because data integrity and maintenance are as important as performance.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Monday, January 21, 2013 8:04 AM

All replies

  • Hello, 

    The requirement is not so clear but as far as I understand, you have two tables and have FK references between them. Let's Say Table A has Primary Key and Table B has Foreign Key reference to Table A. The requirement is Whenever you insert data in Table A, you want to insert  it into Table B. 

    One way to Accomplish this is using Insert Trigger but what about the other columns in the table B. If this is the real requirement, you do not really need to use FK, a Trigger will automatically take care of that...Please give us more insights of your requirement, it helps to understand more in detail

    Alter Trigger Fk_Insert
    on PrimaryKeyTable
    After Insert
    As
    begin
    Insert into ForeginKeyTable(Sno)
    Select sno from Inserted
    End


    Hope it Helps!!

    Saturday, January 19, 2013 7:44 PM
  • I would have a stored procedure dedicated to INSERT command and there  I would perform two INSERT command like 

    CREATE PROCEDURE spInsert

    @id INT

    AS

    INSERT INTO Parent (id) VALUES (@id)

    INSERT INTO Child (id) VALUES (@id)


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, January 20, 2013 6:53 AM
  • thanks for the replies.  Hopefully this will make what I am looking for more clear.

    Let's say that I have two tables.

    Table 1: named tblUsers, with columns UserID as primary key, firstname, and last name

    Table 2: named tblPasswords, with columns PasswordID as primary key, UserID as foreign key to tblUsers, and Password

    Presently I am using stored procedures to accomplish it as was recommended above but was wandering if there was something more automated that I could use to accomplish the same thing.  When I delete the a record in tblUsers, I want the data deleted in tblPasswords that matches the corresponding record in tblUsers to delete automatically as well. I am also doing that with stored procedures but would rather have something that automatically does that for me. 

    Hopefully this clarifies what I am looking to do.


    DCSSR

    Sunday, January 20, 2013 3:38 PM
  • Hello,


    Thank you for the detials but may I ask why do you need to two tables for this?? I think you can just have one table TblUsers that has PassWord values(hashed,ofcourse) in that table..Let us know


    Hope it Helps!!

    Sunday, January 20, 2013 4:17 PM
  • I agree with Stan210, you do not need two tables, password value depends on the userid so why it should be in another table?

    >>>>I am also doing that with stored procedures but would rather have something that automatically does that for me. 

    Yes,  you can have a trigger on table1 which deletes matching rows in the table2 

    create trigger tr on table1 for delete

    as

    delete from table2 where userid in (select userid from deleted)


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, January 21, 2013 5:54 AM
  • You can make the FOREIGN KEY cascading to do this automatically. In the example below, when you delete a parent row, the child row is automatically deleted.

    More info on CASCADE at -
    http://msdn.microsoft.com/en-us/library/ms174979(SQL.105).aspx

    Triggers and Stored Procedures can also be used to do this, as suggested by others, but IMHO, cascading deletes are the cleanest possible implementation for this kind of requirement wherever possible.

    create table [dbo].[User]
    (
    UserId int identity constraint PK_User primary key,
    FirstName varchar(50),
    LastName varchar(50)
    );
    go
    create table [dbo].[UserPassword]
    (
    PasswordId int identity constraint PK_Password primary key,
    UserId int constraint FK_Password$for$User foreign key references [dbo].[User](UserId) ON DELETE CASCADE,
    UserPassword Varchar(50)
    );
    go
    insert into [dbo].[User] (FirstName, LastName) values
    ('Tom', 'Sawyer'),
    ('Joe', 'Harper');
    
    insert into [dbo].[UserPassword] (UserId, UserPassword) values
    (1, 'tOmpWd'),
    (2, 'joepwD');
    
    select * from [dbo].[User];
    select * from [dbo].[UserPassword];
    
    delete from [dbo].[User]
    where FirstName  = 'Tom' and LastName = 'Sawyer';
    
    select * from [dbo].[User];
    select * from [dbo].[UserPassword];

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)


    Monday, January 21, 2013 6:20 AM
  • Perhaps DCSSR meant it only as an example, but IMHO having UserIds and Passwords in different tables is a perfectly valid implementation (with a 1 to 1 relationship). There could be other columns in the Password table that are only dependent on the password e.g. PasswordSetDate, PasswordExpiryDate, IsDisabled etc.

    Different tables make even more sense when we would like to maintain a history of passwords for a user (1 to many relationship) where only one of them could be in an enabled state.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Monday, January 21, 2013 6:36 AM
  • You can have PassHistory table (when pass is changed you moved it to the history table) where  you see Userid, old pass and all other fields like you mentioned. Why do  you have another table? If the policy of the company to change the pass every week for 100 users ? Why make another JOIN with a large table?

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, January 21, 2013 6:47 AM
  • DCSSR, you need to be clear while stating your question. You initial post seems like you are looking for a simultaneous INSERT into a Parent and Child table. That would require you to capture the IDENTITY values from the new rows in the Parent table and use those while inserting rows in the Child table. It will be a good idea to do this in one transaction. Triggers and Stored Procedures can be used for this. Stan210 and Uri Dimant have given you  good suggestions about that.

    Your last post above mentions that you need to do simultaneous DELETE from the Parent and Child tables. Although doing simultaneous operations on Parent-child tables meet the same goal of maintaining data integrity, the implementations for INSERT and DELETE can be different. My example with cascading foreign keys is valid only for DELETEs.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Monday, January 21, 2013 6:48 AM
  • For the want of a better modelling tool at my disposal at this time, I'll use the good ol' Excel :-)


    Though both of our implementations are debatable for pros and cons, I'll go for my idea of normalized tables because data integrity and maintenance are as important as performance.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
    Thanks!
    Aalam | Blog (http://aalamrangi.wordpress.com)

    Monday, January 21, 2013 8:04 AM