CODEWITHSUNDEEP
sql-server-2005
ranga nathan
ranga nathan

Reputation: 163

Foreign Key error in table creation

I have created a table with composite primary key. When I referred one of the composite primary key column in other table I'm getting error and I'm not able to create the foreign key relationship.

Error Message:

There are no primary or candidate keys in the referenced table 't_app_arcv_tbl' that match the referencing column list in the foreign key 'fk_APPLICATION_CD'.

Table with composite primary key

Create table t_app_arcv_tbl
(
    APPLICATION_CD varchar(10),     
    TBL_NM varchar(50) not null ,
    ARCV_TBL_TYP_CD char(2) not NULL constraint fk_Arcv_TBL_TYP_CD foreign key references t_app_arcv_tbl_typ(ARCV_TBL_TYP_CD) , 
    ACT_ID Char(1) not NULL,
    OPER_ID varchar(50) not NULL,       
    PSTDT datetime not NULL         
    Constraint PK_AppName_TblNM PRIMARY KEY(APPLICATION_CD,TBL_NM)
)

The following table I'm trying to create with foreign key constraint

*

-- Create Table Script for archival process Tracking
create table t_app_arcv_log         
(
    APPLICATION_CD varchar(10) not NULL constraint fk_APPLICATION_CD foreign key references t_app_arcv_tbl(APPLICATION_CD),         
    TBL_NM varchar(50) constraint fk_TBL_NM foreign key references t_app_arcv_tbl(TBL_NM),
    XTRCT_DT datetime,
    ARCV_TYP_CD char(2) not NULL constraint fk_Arcv_TYP_CD foreign key references t_app_arcv_typ(ARCV_TYP_CD),          
    STRT_DT datetime,
    END_DT datetime,
    ROW_CNT_BFR int,
    ROW_CNT_AFT int,
    ARCV_STTS_CD char(1) not null,
    OPER_ID varchar(50) not NULL,       
    PSTDT datetime not NULL
)

Kindly help me how to create the table with foreign key relationship.

Thanks In Advance!

--Regards, Ranga

Upvotes: 0

Views: 99

Answers (1)

marc_s
marc_s

Reputation: 754478

If the main table defines a composite primary key

 PRIMARY KEY(APPLICATION_CD,TBL_NM)

then all the tables that want to reference that table must use the whole composite key as their FK reference. You cannot reference only parts of a primary key - it's the whole key or nothing.

You need both columns in your child table:

CREATE TABLE dbo.t_app_arcv_tbl
(
    APPLICATION_CD varchar(10),     
    TBL_NM varchar(50) not null ,
    ARCV_TBL_TYP_CD char(2) not NULL,
    TBL_NM varchar(50) not null ,  -- <== ADD THIS
    ACT_ID Char(1) not NULL,
    OPER_ID varchar(50) not NULL,       
    PSTDT datetime not NULL         
    Constraint PK_AppName_TblNM PRIMARY KEY(APPLICATION_CD,TBL_NM)
)

-- add foreign key constraint - it **MUST** define and use **BOTH** columns 
-- from the PK of the referenced table!
ALTER TABLE dbo.t_app_arcv_tbl
ADD CONSTRAINT fk_Arcv_TBL_TYP_CD 
FOREIGN KEY (ARCV_TBL_TYP_CD, TBL_NM)
REFERENCES dbo.t_app_arcv_tbl_typ

Upvotes: 1

Related Questions