Foreign/Primary Keys as CHAR - limitations?

[m227]

Hello,

What are limitations of Foreign/Primary Keys in MyDAC? I have tables:

Code: Select all

CREATE TABLE xLevel (
  symbol CHAR(1) NOT NULL PRIMARY KEY,
  name VARCHAR(32) NULL ) TYPE=InnoDB;

CREATE TABLE xUser (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  level CHAR(1) NOT NULL,
  firstname VARCHAR(32) NULL,
  lastname VARCHAR(32) NULL,
  INDEX x1(level),
  FOREIGN KEY(level) REFERENCES xLevel(symbol) ON DELETE NO ACTION ON UPDATE NO ACTION ) TYPE=InnoDB;

xLevel.symbol is a Foreign key for a Primary key xUser.level and it is a type of CHAR(1).

In TD when I put on form two TMyTables, two TMyDataSources, use custom fields for xUser table, and set the field properties for TStringField("level") as:

• KeyFields: level
  LookupDataSet: MyTableLevel (xLevel)
  LookupKeyFields: symbol
  LookupResultField: name
  FieldKinf:fkLookup

i get empty column in TCRDBGrid for xUser table... what's wrong? Windows XP SP2, Turbo Delphi, MySQL Server 5.0.45, MySQL Client 5.1.11, MyDAC 5.10.0.9.

Michal

[Ikar]

This problem is in the field naming. To avoid it, you should set properties for "level" fields to default values (or just recreate these fields) and create additional lookup field with FieldName, for example, "level_l".

[m227]

Thanx, I have found it out looking back to my previous project (simple one made month ago)... There must be additional lookup fields in addition to standard foreign keys in table definition (TMyTable).

Now it works flawelessly.