question

Katie 1 avatar image
Katie 1 asked

PRIMARY - FORIEGN KEY ERROR

CREATE TABLE A(
    [MTR_KEY] [varchar](25) NOT NULL,  
    [FILE_NAME] [varchar](4000) NULL,  
    [VERSION] [numeric](2, 0) NOT NULL,  
    [LST_MODIFIED_DT] [datetime] NULL,  
    [PROCESS_STATE] [numeric](5, 2) NOT NULL,  
    [LST_MODIFIED_USER] [varchar](30) NULL,  
    [TRAN_DATE] [datetime] NOT NULL,  
    [ROW_STATE] [numeric](5, 2) NOT NULL,  
CONSTRAINT [XPKMTR_IMPORT] PRIMARY KEY NONCLUSTERED   
    ( [TRAN_DATE] ASC,  
      [MTR_KEY] ASC,  
      [VERSION] ASC  
    )WITH (PAD_INDEX  = OFF,  
STATISTICS_NORECOMPUTE  = OFF,  
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,  
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO  

CREATE TABLE B(  
(   INTERVAL  numeric(3)  NOT NULL ,  
    HR_VALUE  numeric(20,10)  NULL ,  
    LST_MODIFIED_DT  datetime  NULL ,  
    LST_MODIFIED_USER  varchar(30)  NULL ,  
    PROCESS_STATE  numeric(5,2)  NULL ,  
    ROW_STATE  numeric(5,2)  NOT NULL ,  
    TRAN_DATE  datetime  NOT NULL ,  
    VERSION  numeric(2,0)  NOT NULL ,  
    MTR_KEY  varchar(25)  NOT NULL   
)
go  

ALTER TABLE B  
    ADD CONSTRAINT  XPKMTR_DATA PRIMARY KEY  
    NONCLUSTERED (INTERVAL  ASC,TRAN_DATE  ASC,VERSION  ASC,MTR_KEY  ASC)  
go  


ALTER TABLE B  
    ADD CONSTRAINT  R_161A FOREIGN KEY (TRAN_DATE,VERSION,MTR_KEY)  
    REFERENCES A(TRAN_DATE,VERSION,MTR_KEY)  
        ON DELETE NO ACTION  
        ON UPDATE NO ACTION  
go

When i try to create the last constraint R_161A i get this error

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'A' that
match the referencing column list in the foreign key 'R_161A'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.

I am not sure why is this error coming. i am thinking primary keys on the table B are creating an issues. Any idea why is this error..

sql-server-2008t-sqladministrationforeign-key
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for putting up sample code. It made a huge difference in troubleshooting.
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered

the columns are in the wrong order, use:

ALTER TABLE B
ADD CONSTRAINT R_161A FOREIGN KEY ( [TRAN_DATE] , [MTR_KEY] , [VERSION]  )  
REFERENCES A ( TRAN_DATE , [MTR_KEY] , [VERSION] ) ON DELETE NO ACTION
        ON UPDATE NO ACTION  
    go

Also replaced VERSION with [VERSION] as its a reserved word.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Modified it slightly and it worked (after adjusting the create script, no length on mtr_key in table a)

ALTER TABLE B
ADD CONSTRAINT R_161A FOREIGN KEY ([TRAN_DATE] ,
[MTR_KEY] ,
[VERSION]) REFERENCES A([TRAN_DATE],[MTR_KEY],[VERSION])
ON DELETE NO ACTION
ON UPDATE NO ACTION
go
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Yup - SQL implementations rely on column order, a deviation from the theoretical SET-based standard where, mathematically speaking, it wouldn't matter.
1 Like 1 ·
gah, was editing on my VM and when I get back with the solution I've been beaten to it!!
0 Likes 0 ·
Thank you Grant!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.