x

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..

more ▼

asked May 06, 2010 at 06:30 PM in Default

avatar image

Katie 1
1.4k 132 164 205

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

May 06, 2010 at 06:59 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered May 06, 2010 at 07:16 PM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered May 06, 2010 at 06:58 PM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

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

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

Thank you Grant!

May 07, 2010 at 10:18 AM Katie 1

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

May 07, 2010 at 10:22 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x1089
x133
x42

asked: May 06, 2010 at 06:30 PM

Seen: 2797 times

Last Updated: May 06, 2010 at 07:04 PM

Copyright 2017 Redgate Software. Privacy Policy