Create Reference Problem

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I have tables structure below:

1. TMGLACCNO

 strTableField = "CREATE TABLE TMGLACCNO (" & _
                        "AccNo Char(11)PRIMARY KEY," & _
                        "Name Char(70) DEFAULT '' ," & _
                        "Global Char(1) DEFAULT '' ," & _
                        "GlobalAcc Char(11) DEFAULT '' ," & _
                        "GLTypeCode Char(2) DEFAULT '' ," & _
                        "Propotional Char(1) DEFAULT '' ," & _
                        "CrtId Char(10) DEFAULT '' ," & _
                        "CrtDate DateTime," & _
                        "UpdId Char(10)DEFAULT '' ," & _
                        "UpdDate DateTime)"

2. TDJURNAL
  strTableField = "CREATE TABLE TDJURNAL (" & _
            "BuktiJurnalNoSeq Char(32) PRIMARY KEY," & _
            "BuktiJurnal Char(30) DEFAULT ''," & _
            "Noseq Char(2) DEFAULT ''," & _
            "AccNo Char(11) DEFAULT ''," & _
            "KetDetJurnal VarChar(120) DEFAULT ''," & _
            "Debet Money DEFAULT 0," & _
            "Kredit Money DEFAULT 0," & _
            "DetStsCheck Char(1) DEFAULT 'T'," & _
            "CrtId Char(10) DEFAULT ''," & _
            "CrtDate DateTime," & _
            "UpdId Char(10) DEFAULT ''," & _
            "UpdDate DateTime)"

I create table TDJURNAL at run time, thus this table is empty.

I have below error when try to :

There are no primary or candidate keys in the referenced table 'TMGLACCNO' that match the referencing column list in the foreign key 'FK_TDJURNAL201001_AccNo'. Could not create constraint. See previous errors.

ALTER TABLE TDJURNAL201001 WITH CHECK ADD CONSTRAINT FK_TDJURNAL201001_AccNo  FOREIGN KEY(AccNo) REFERENCES TMGLACCNO(AccNo)

What's the problem?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Does the table [TDJURNAL201001] actually exist?

Author

Commented:
Yes, it is.

The problem rise after create TDJURNAL201001 and alter table.

Thank you.
That's really strange. There is nothing obviously wrong syntactically. Can you put your application in debug mode and step through creating the JURNAL table. And then manually, from within Query Analyzer, try to issue the ALTER TABLE statement. Or better yet, run SP_HELP 'TDJURNAL201001' and post the results if you could.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Author

Commented:
Yes, it is strange.

SP_HELP result.

TDJURNAL201001      dbo      user table      28/01/2010 9:28:28

Using references at create table also have the same problem

strTableField = "CREATE TABLE TDJURNAL (" & _
            "BuktiJurnalNoSeq Char(32) PRIMARY KEY," & _
            "BuktiJurnal Char(30) DEFAULT ''," & _
            "Noseq Char(2) DEFAULT ''," & _
            "AccNo Char(11) DEFAULT '' REFERENCE TMGLACCNO(AccNo)," & _
            "KetDetJurnal VarChar(120) DEFAULT ''," & _
            "Debet Money DEFAULT 0," & _
            "Kredit Money DEFAULT 0," & _
            "DetStsCheck Char(1) DEFAULT 'T'," & _
            "CrtId Char(10) DEFAULT ''," & _
            "CrtDate DateTime," & _
            "UpdId Char(10) DEFAULT ''," & _
            "UpdDate DateTime)"

Thank you.
Here is what I am not understanding -and forgive me if I am overlooking something.
Where in your CREATE TABLE are you appending the yyyymm (i.e. 201001) to the TDJURNAL table name?
Next take a look at the screenshot I am including from th the SP_HELPDB command. Is there an ACCNO column listed as in my sample? Could you run the same for both of your tables and attach screenshots. We'll get through this! :)

Untitled-picture.png

Author

Commented:
Where in your CREATE TABLE are you appending the yyyymm (i.e. 201001) to the TDJURNAL table name?
Yes.

What's the command to get the above result.


Thank you.
SP_HELP 'TDJURNAL201001'
GO
SP_HELP 'TMGLACCNO'
go

Author

Commented:
My real command to create table is below :

strTableFile = "TDJURNAL" & strYearMonth
        Class_Create_TDJURNAL = strTableFile

        If Not blnCreateTable Then Exit Function

        strTableField = "CREATE TABLE " & strTableFile & "(" & _
            "BuktiJurnalNoSeq Char(32) PRIMARY KEY," & _
            "BuktiJurnal Char(30) DEFAULT ''," & _
            "Noseq Char(2) DEFAULT ''," & _
            "AccNo Char(11) DEFAULT '' ," & _
            "KetDetJurnal VarChar(120) DEFAULT ''," & _
            "Debet Money DEFAULT 0," & _
            "Kredit Money DEFAULT 0," & _
            "DetStsCheck Char(1) DEFAULT 'T'," & _
            "CrtId Char(10) DEFAULT ''," & _
            "CrtDate DateTime," & _
            "UpdId Char(10) DEFAULT ''," & _
            "UpdDate DateTime)"


Thank you.

Author

Commented:
Please see the screen shot.

Thank you.
TDJURNAL2010.PNG
TMGLACCNO.PNG
TMGLACCNO.AccNo is not a PRIMARY KEY

Author

Commented:
Yes, it is.

You solve my problem.

Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial