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

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

Answer 1

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

Answer 2

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

Answer 3

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.

 

Answer 4

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.aspx. Meanwhile, 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 [e-mail].