January 30, 2018 at 10:43 pm
Dear Expert,
I am trying to create the following three tables. The first two tables are created successfully but while creating third table i.e. TEST table, I am getting an error "There are no primary or candidate keys in the referenced table 'DBO.TestMaster' that match the referencing column list in the foreign key 'FK_CIFBlob_Lookup'." Please suggest where I went wrong.
IF OBJECT_ID('[DBO].[TEST1]') IS NULL
BEGIN
CREATE TABLE TEST1(LookupCode NVARCHAR(2)NOT NULL,
FullDesc NVARCHAR(24)NOT NULL
)
ALTER TABLE TEST1 ADD CONSTRAINT PK_CIFDocLookup PRIMARY KEY (LOOKUPCODE)
END
GO
IF OBJECT_ID('[DBO].[TESTMaster]') IS NULL
BEGIN
CREATE TABLE TESTMaster (BR NVARCHAR(6) NOT NULL,
CID NVARCHAR(6)NOT NULL,
LookupCode NVARCHAR(2) NOT NULL
)
ALTER TABLE [DBO].[TESTMaster] ADD CONSTRAINT PK_CIFBlobMaster PRIMARY KEY (BR,CID,LOOKUPCODE)
ALTER TABLE [DBO].[TESTMaster] WITH CHECK ADD CONSTRAINT [FK_CIFBlobMaster_Lookup] FOREIGN KEY([LookupCode])REFERENCES [DBO].[TEST1] ([LookupCode])
END
GO
GO
IF OBJECT_ID('[DBO].[TEST]') IS NULL
BEGIN
CREATE TABLE TEST(BR NVARCHAR(6) NOT NULL,
CID NVARCHAR(6)NOT NULL,
LookupCode NVARCHAR(2) NOT NULL,
ID INT NOT NULL,
Picture IMAGE
)
ALTER TABLE [DBO].[TEST] ADD CONSTRAINT PK_CIFBlob PRIMARY KEY (BR,CID,LOOKUPCODE,ID)
ALTER TABLE [DBO].[TEST] WITH CHECK ADD CONSTRAINT [FK_CIFBlob_Lookup] FOREIGN KEY(BR,CID,LOOKUPCODE)REFERENCES [DBO].[TESTMaster] (BR,CID,LOOKUPCODE)
END
Regards,
January 30, 2018 at 11:30 pm
Hi
Just check that your PK (PK_CIFBlobMaster) was actually created on the table dbo.TestMaster. I ran your code and it created all 3 tables with all the PK and FK's correctly.
January 31, 2018 at 2:44 am
Indeed.. i created a new database and tried to create the table and it created successfully. May be there was something wrong in the existing database.
Thanks..
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply