none
How foreign key references two more tables?

    Question

  • Hi

          I have created 3 tables with primary keys.

    create table dbo.test_index(id int identity(1,1) not null , name varchar(255) Constraint Pk_Ind_Id primary key(id) )
    create table dbo.Test_Const (id int identity(100,1)not null ,name varchar(50) Constraint Pk_Id primary key(id))
    create table dbo.test_key(kid int identity(1,1) not null ,id int,kname varchar(255) Constraint Pk_key_Id primary key(kid) )

    Now i want to refer the column 'id' in table 'dbo.test_index'  and also the column 'id' in table 'dbo.Test_Const'  as foreign key for the table 'dbo.test_key' column 'id'.

    Can any one assist me how is it possible with example?

    Thanks in advance.

    SathyJay


    • Edited by SathyJay Monday, July 22, 2013 11:17 AM
    Monday, July 22, 2013 10:45 AM

All replies

  • ALTER TABLE dbo.test_key
    WITH CHECK ADD  CONSTRAINT [test_key_FK_01] 
    FOREIGN KEY(id)
    REFERENCES dbo.test_index (id)
    GO


    ALTER TABLE dbo.test_key
    WITH CHECK ADD  CONSTRAINT [Test_Const_key_FK_01] 
    FOREIGN KEY(id)
    REFERENCES dbo.Test_Const (id)
    GO

    Thanks- Prajesh Please mark the post as answered if it answers your question

    • Proposed as answer by Prajesh Monday, July 22, 2013 10:54 AM
    Monday, July 22, 2013 10:54 AM
  • If I understood correctly, you need to insert into Key table only the id available in both index and cost. If thats the case, please use the below script:

    Drop table dbo.test_index,dbo.Test_Const,dbo.test_key
    create table dbo.test_index(id int identity(1,1) not null , name varchar(255) Constraint Pk_Ind_Id primary key(id) )
    create table dbo.Test_Const (id int identity(1,1)not null ,name varchar(50) Constraint Pk_Id primary key(id))
    create table dbo.test_key(kid int identity(1,1) not null ,id int,kname varchar(255) Constraint Pk_key_Id primary key(kid) )
    
    ALTER TABLE dbo.test_key 
    ADD CONSTRAINT FK_test_key_test_index FOREIGN KEY (id) 
        REFERENCES dbo.test_index (id) 
        
    ALTER TABLE dbo.test_key 
    ADD CONSTRAINT FK_test_key_Test_Const FOREIGN KEY (id) 
        REFERENCES dbo.Test_Const (id)   
    
    Insert into Test_Const Select 'SQL'   
    Insert into Test_Const Select 'SQL2'   
    Insert into test_index Select 'Index'
    Insert into test_index Select 'Index2'
    
    Insert into test_key Select 1,'SQL1'  
    Insert into test_key Select 2,'SQL1In' 
    
    Select * From dbo.test_key 
    Select * From dbo.test_index 
    Select * From dbo.Test_Const 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 10:55 AM
  • Hi Latheesh,

    sorry I was forget to mention the identity value as following,

    create

    table dbo.Test_Const(id int identity(100,1)not null ,name varchar(50) Constraint Pk_Id primary key(id))

    Thanks,

    SathyJay

    Monday, July 22, 2013 11:16 AM
  • That should be fine. The only thing you need to be careful is that, if the key is present in both tables(CONST and INDEX), then ONLY the constraint will pass the value to insert into your KEY table else, it will throw an exception.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, July 22, 2013 11:20 AM
  • Yes, the key is present in either one table. How to resolve this exception with out creating the extra lookup tables?

    Thanks,

    SathyJay

    Monday, July 22, 2013 11:29 AM