PRIMARY - FORIEGN KEY ERROR

Question

question

Katie 1 asked May 6, '10

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-2008 t-sql administration foreign-key

1

10 |1200

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

Grant Fritchey ♦♦ commented · May 06, 2010 at 06:59 PM

Thanks for putting up sample code. It made a huge difference in troubleshooting.

0 ·

Answer 1 · 2010-05-06T19:16:10Z

Fatherjack answered May 6, '10

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

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

Answer 2 · 2010-05-06T18:58:58Z

Grant Fritchey answered May 6, '10

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

10 |1200

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

Blackhawk-17 commented · May 07, 2010 at 10:22 AM

Yup - SQL implementations rely on column order, a deviation from the theoretical SET-based standard where, mathematically speaking, it wouldn't matter.

1 ·

Fatherjack ♦♦ commented · May 06, 2010 at 07:18 PM

gah, was editing on my VM and when I get back with the solution I've been beaten to it!!

0 ·

Katie 1 commented · May 07, 2010 at 10:18 AM

Thank you Grant!

0 ·

question