emi_sastra
asked on
EMPTY FOREIGN KEY PROBLEM
Hi All,
I have a parent table :
TMPARENT :
1. MemberId
2. MemberName
TMCHILD :
1. MemberId
2. SponserId
3. UplineMemberId
Key : MemberId
Foreign Key : SponserId, UplineMemberId of MemberId at TMPARENT.
Error at creation :
There are no primary or candidate keys in the referenced table 'TMPARENT' that match the referencing column list in the foreign key 'FK_TMCHILD_SponserId'. Could not create constraint. See previous errors.
What is the problem ?
Thank you.
I have a parent table :
TMPARENT :
1. MemberId
2. MemberName
TMCHILD :
1. MemberId
2. SponserId
3. UplineMemberId
Key : MemberId
Foreign Key : SponserId, UplineMemberId of MemberId at TMPARENT.
Error at creation :
There are no primary or candidate keys in the referenced table 'TMPARENT' that match the referencing column list in the foreign key 'FK_TMCHILD_SponserId'. Could not create constraint. See previous errors.
What is the problem ?
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
They both are varchar(3)'s, so no idea.
Just out of curiosity, what's the purpose of establishing a default value of 'ID' on a primary key?
I also don't see where you're referencing HighestRankId to RankId in the above code, like ...
"HighestRankId VarChar(3) DEFAULT 'ID' REFERENCES othertablename.RankId," & _
Just out of curiosity, what's the purpose of establishing a default value of 'ID' on a primary key?
I also don't see where you're referencing HighestRankId to RankId in the above code, like ...
"HighestRankId VarChar(3) DEFAULT 'ID' REFERENCES othertablename.RankId," & _
ASKER
ALTER TABLE TMMEMBERDATA ADD CONSTRAINT FK_TMMEMBERDATA_HighestRan
I have remove 'ID' from PRIMARY KEY.
What's wrong ?
Thank you.
I have remove 'ID' from PRIMARY KEY.
What's wrong ?
Thank you.
What exactly is the error message you are getting..
ASKER
RankId is not the same data type as referencing column HighestRankId
Thank you.
Thank you.
ASKER
Column TMRANKQUALIFICATION.RankId
Thank you.
Thank you.
No clue. You might want to drop constraint and re-create.
Also, I'm going off the grid for a couple of hours, so any expert that wants to pick up from here go ahead..
Also, I'm going off the grid for a couple of hours, so any expert that wants to pick up from here go ahead..
ASKER
Ok. I have no idea either.
Thank you.
Thank you.
The foreign key being referenced doesn't necessarily need to be a primary key. A unique index is enough.
Btw, there are two bytes of overhead for a varchar column (to store the actual length), so instead of varchar(3) you should just use char(3). char(3) also processes slightly faster than varchar(3).
Btw, there are two bytes of overhead for a varchar column (to store the actual length), so instead of varchar(3) you should just use char(3). char(3) also processes slightly faster than varchar(3).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Another problem.
strSQLCommand = "CREATE TABLE " & strTableFile & "(" & _
"RankId VarChar(3) DEFAULT 'ID' PRIMARY KEY," & _
"RankName VarChar(30) DEFAULT ''," & _
"CrtId VarChar(10) DEFAULT ''," & _
"CrtDate DateTime," & _
"UpdId VarChar(10) DEFAULT ''," & _
"UpdDate DateTime)"
strSQLCommand = "CREATE TABLE " & strTableFile & "(" & _
"MemberId Char(8) DEFAULT '' PRIMARY KEY ," & _
"HighestRankId VarChar(3) DEFAULT 'ID'," & _
"CrtId Char(10) DEFAULT ''," & _
"CrtDate DateTime," & _
"UpdId Char(10)DEFAULT ''," & _
"UpdDate DateTime)"
RankId is not the same data type as referencing column HighestRankId
What's wrong ?
Thank you.