locked
Reference one column to two columns as Foreign Key column and insert that table. RRS feed

  • Question

  • Hi Folks, i kinda having two questions. First i wanna know how to reference one column to two columns in other table and how to load that table. Let me show you the table layout first.

    BenefitCodeConfigSummaries : (BenefitCodeConfigSummaryId(PK), BenefitCodeId(FK) ,PrimaryBenefitCodeId,SecondayBenefitCodeId, GroupConfigBenefitSummaryId(FK), EffectiveDate, TermDate) 

    BenefitCode : (BenefitCodeId(PK), BenefitCode, ActiveFlag )

    GroupConfigBenefitSummary : (GroupConfigBenefitSummaryId(PK), EffectiveDate, TermDate)

    I want to reference column BenefitCodeId to table BenefitSummary for two columns PrimaryBenefitCodeId and SecondaryBenefitCodeId.

    Here's my Code :

    *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*

    IF EXISTS ( SELECT  1
                FROM    sys.foreign_keys
                WHERE   OBJECT_ID = OBJECT_ID(N'[Facets].[FK_BenefitCodeConfigSummaries_BenefitCode_BenefitCodeId]')
                        AND parent_object_id = OBJECT_ID(N'[Facets].[BenefitCodeConfigSummaries]') )
       ALTER TABLE [Facets].[BenefitCodeConfigSummaries] DROP CONSTRAINT [FK_BenefitCodeConfigSummaries_BenefitCode_BenefitCodeId]

    Go
    IF EXISTS ( SELECT  1
                FROM    sys.foreign_keys
                WHERE   OBJECT_ID = OBJECT_ID(N'[Facets].[FK_BenefitCodeConfigSummaries_GroupConfigBenefitSummary_GroupConfigBenefitSummaryId]')
                        AND parent_object_id = OBJECT_ID(N'[Facets].[BenefitCodeConfigSummaries]') )
       ALTER TABLE [Facets].[BenefitCodeConfigSummaries] DROP CONSTRAINT [FK_BenefitCodeConfigSummaries_GroupConfigBenefitSummary_GroupConfigBenefitSummaryId]

    Go

    /****** Object:  Table [Facets].[BenefitCodeConfigSummaries]    Script Date: 05/12/2011 14:13:08 ******/
    IF EXISTS (SELECT *
               FROM   sys.objects
               WHERE  object_id =
                      Object_id(N'[Facets].[BenefitCodeConfigSummaries]'
                      )
                      AND TYPE IN ( N'U' ))
      DROP TABLE [Facets].[BenefitCodeConfigSummaries] 

    GO
    SET ansi_padding OFF
    /****** Object:  Table [Facets].[BenefitCodeConfigSummaries]    Script Date: 05/12/2011 13:37:48 ******/
    SET ansi_nulls ON

    GO

    SET quoted_identifier ON

    GO

    SET ansi_padding ON

    GO

    IF NOT EXISTS (SELECT *
                   FROM   sys.objects
                   WHERE  object_id =
                          Object_id(N'[Facets].[BenefitCodeConfigSummaries]')
                          AND TYPE IN ( N'U' ))
      BEGIN
          CREATE TABLE [Facets].[BenefitCodeConfigSummaries]
            (
    [BenefitCodeConfigSummaryId] SmallInt  NOT NULL Identity(-32768,1),
    [BenefitCodeId]      SmallInt        NOT NULL,
    [PrimaryBenefitCodeId]          Char    (4)     NOT NULL,
    [SecondayBenefitCodeId]         Char    (4)     NOT NULL,
    [GroupConfigBenefitSummaryId]   SmallInt        NOT NULL,
    [EffectiveDate]     Datetime  NOT NULL,  
    [TermDate]      Datetime  NULL,  
    [InsertedBy]     Varchar   (128) NOT NULL DEFAULT (Suser_sname()),
    [InsertedTs]     Datetime  NOT NULL DEFAULT (getdate()),
    [UpdatedBy]      Varchar   (128) NOT NULL DEFAULT (Suser_sname()),
    [UpdatedTs]      Datetime  NOT NULL DEFAULT (getdate()),
     
    CONSTRAINT [PK_BenefitCodeConfigSummaries_BenefitCodeConfigSummaryId] PRIMARY KEY CLUSTERED (
               [BenefitCodeConfigSummaryId] ASC )WITH (pad_index = OFF,
               statistics_norecompute = OFF, ignore_dup_key = OFF, allow_row_locks =
               on,
               allow_page_locks = on) ON [PRIMARY],
               Check ((Isnull([PrimaryBenefitCodeId],0)= 0  And Isnull([SecondayBenefitCodeId],0)<> 0)
                     OR (Isnull([PrimaryBenefitCodeId],0)<>0 And Isnull([SecondayBenefitCodeId],0) = 0))
           
    )
          ON [PRIMARY]
      END

    Go
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys
    WHERE object_id = OBJECT_ID(N'[Facets].[FK_BenefitCodeConfigSummaries_BenefitCode_BenefitCodeId]')
    AND parent_object_id = OBJECT_ID(N'[Facets].[BenefitCodeConfigSummaries]'))
    ALTER TABLE [Facets].[BenefitCodeConfigSummaries]
    WITH CHECK ADD  CONSTRAINT [FK_BenefitCodeConfigSummaries_BenefitCode_BenefitCodeId] FOREIGN KEY([BenefitCodeId])
    REFERENCES [Lkup].[BenefitCode] ([BenefitCodeId])
    Go
    IF NOT EXISTS (SELECT * FROM sys.foreign_keys
    WHERE object_id = OBJECT_ID(N'[Facets].[FK_BenefitCodeConfigSummaries_GroupConfigBenefitSummary_GroupConfigBenefitSummaryId]')
    AND parent_object_id = OBJECT_ID(N'[Facets].[BenefitCodeConfigSummaries]'))
    ALTER TABLE [Facets].[BenefitCodeConfigSummaries]
    WITH CHECK ADD  CONSTRAINT [FK_BenefitCodeConfigSummaries_GroupConfigBenefitSummary_GroupConfigBenefitSummaryId] FOREIGN KEY([GroupConfigBenefitSummaryId])
    REFERENCES [Facets].[GroupConfigBenefitSummary] ([GroupConfigBenefitSummaryId])

    *~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*

    Thanks


    Thursday, July 14, 2011 2:54 PM

Answers

  • I have to ignore your code, because it is very hard to read, and can't seem to get its point.

    A Foreign Key constraint must have the same number of columns in both the referencing table as well as the referenced table, and these columns need to be compatible.

    You say you want to (BenefitCodeId) to (PrimaryBenefitCodeId, SecondaryBenefitCodeId). So what does that mean? Do you mean BenefitCodeId = PrimaryBenefitCodeId AND BenefitCodeId = SecondaryBenefitCodeId?

    In that case, you could do something like this:

     

    create table b(pid int,sid int,constraint pk primary key(pid,sid))
    create table a(id int primary key,sid as id persisted)
    
    alter table a
    add constraint fk
    foreign key (id, sid)
    references b (pid, sid)
    
    drop table a
    drop table b
    
    

    In the example above, a computed (persisted) column is used to get around the restriction that one column can only be mentioned once in a foreign key constraint.

    If you need something else, then please explain, and give some sample data and examples of what the Foreign Key constraint should allow and disallow.


    Gert-Jan
    Thursday, July 14, 2011 7:52 PM
  • The way I understood it is that we have two columns in one table that are FK to another table.

     

    So, say, we have contacts table (ContactID, ContactName)

    and we have

    Clients

    (ClientID,  PrimaryContactID, SecondaryContactID).

    Each field (Primary and Secondary) should refer to ContactID field in the parent table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 14, 2011 9:03 PM
  • From the error message, the column "BenefitCodeId" is not nullable so that we cannot insert NULL value in the column. For more information aobut foreign key constraint, please refer to http://msdn.microsoft.com/en-us/library/ms175464.aspxMeanwhile, when post code/script, you can use "Insert Code Block" button which could help us to read your code.
    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Friday, July 22, 2011 9:57 AM

All replies

  • I have to ignore your code, because it is very hard to read, and can't seem to get its point.

    A Foreign Key constraint must have the same number of columns in both the referencing table as well as the referenced table, and these columns need to be compatible.

    You say you want to (BenefitCodeId) to (PrimaryBenefitCodeId, SecondaryBenefitCodeId). So what does that mean? Do you mean BenefitCodeId = PrimaryBenefitCodeId AND BenefitCodeId = SecondaryBenefitCodeId?

    In that case, you could do something like this:

     

    create table b(pid int,sid int,constraint pk primary key(pid,sid))
    create table a(id int primary key,sid as id persisted)
    
    alter table a
    add constraint fk
    foreign key (id, sid)
    references b (pid, sid)
    
    drop table a
    drop table b
    
    

    In the example above, a computed (persisted) column is used to get around the restriction that one column can only be mentioned once in a foreign key constraint.

    If you need something else, then please explain, and give some sample data and examples of what the Foreign Key constraint should allow and disallow.


    Gert-Jan
    Thursday, July 14, 2011 7:52 PM
  • The way I understood it is that we have two columns in one table that are FK to another table.

     

    So, say, we have contacts table (ContactID, ContactName)

    and we have

    Clients

    (ClientID,  PrimaryContactID, SecondaryContactID).

    Each field (Primary and Secondary) should refer to ContactID field in the parent table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, July 14, 2011 9:03 PM
  • I didnt know how to write code so i went in gui and manually created foreign key on primary and secondary columns renferencing benefitCodeId from benefit code table(right click on table name-->modify-->Relationship-->Tables and columns specified) But when i try to load data in BenefitCodeConfigSummaries table, it gives error that can not insert NULL values in BenefitCodeId. I Have data in BenefitCode and GroupConfigBenefitSummary Table.

     


    Friday, July 15, 2011 12:49 PM
  • From the error message, the column "BenefitCodeId" is not nullable so that we cannot insert NULL value in the column. For more information aobut foreign key constraint, please refer to http://msdn.microsoft.com/en-us/library/ms175464.aspxMeanwhile, when post code/script, you can use "Insert Code Block" button which could help us to read your code.
    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Friday, July 22, 2011 9:57 AM