Indexing

Q:
The last line below gives ‘Data type mismatch‘ error. I have also tried ‘(RID_Exe+MachineName)’ as per the docs but then I get ‘Expression was expected’ error.

Code:

create table SWMEXEMACHINES
alter table SWMEXEMACHINES add column RID_Exe numeric(18,0) not null 
alter table SWMEXEMACHINES add column MachineName char(30) not null 
alter table SWMEXEMACHINES add column Enabled bit null 
alter table SWMEXEMACHINES add primary key (RID_Exe,MachineName) tag pk_SWMEXEMACHINES

A:
You are trying to create an index on mixed data types. You need to make sure the expression results in a fixed length character string. You cannot concatenate a string to a numeric value in Lianja.

Code:

alter table SWMEXEMACHINES add primary key (str(RID_Exe),MachineName) tag pk_SWMEXEMACHINES

The indexes are much more functional in Lianja than other SQL databases as they can be created on very complex expressions.

---

Q:
I imported a VFP DBC into Lianja. Where do I configure the Indexes? I don’t see where do I set them.
A:
After you imported your VFP dbc the indexes that you had are recreated in Lianja format.
You can open the database with OPEN DATABASE in the console, open a table with USE, then use LIST STATUS to view the indexes.
You can create new indexes using the VFP-style INDEX ON command or the SQL CREATE INDEX command.

---

Q:
Would you say that I don’t need to create indexes in new tables I create in Lianja? Do LIanja needs the indexes to be created or Lianja creates them as needed?
A:
In most cases Lianja will build the indexes as you develop your app visually. There are cases however (e.g for SQL optimization) where you need to create indexes manually.

---

Q:
I cannot get this working. As far as I can tell FK’s need to work on indexes (where I am accustomed to adding them to columns). This example may look awkward but it shows what I want to achieve:

Code:

CREATE TABLE supplier (SuppId i PRIMARY KEY, SuppRef c(10) UNIQUE, SuppName c(40) UNIQUE)
CREATE TABLE purchase_order (POId i PRIMARY KEY, PO_SuppRef c(10), POtotal n(10,2))
CREATE INDEX sup_idx1 ON Supplier (SuppRef)
CREATE INDEX po_idx1 ON purchase_order (PO_SuppRef)
ALTER TABLE purchase_order ADD FOREIGN KEY po_idx1 TAG po_fk1 REFERENCES supplier TAG sup_idx1

But this gives an error about ‘TAG’ being an unrecognized phrase. Please help with the code to join
purchase_order.PO_SuppRef -> supplier.SuppRef
A:
Try this:

Code:

CREATE TABLE supplier;
 (SuppId int PRIMARY KEY, SuppRef char(10) UNIQUE, SuppName char(40) UNIQUE)
CREATE TABLE purchase_order;
 (POId int PRIMARY KEY,;
  PO_SuppRef char(10) FOREIGN KEY REFERENCES supplier TAG SuppRef,;
  POtotal num(10,2))

Note: UNIQUE creates a Tag index with the same name as the field.
Also, using in-built Lianja UI functionality such as the Relationship Builder, Instant Search, Instant Selections and Grid sorting indexes are generated and selected automatically.
The index is only created by

Code:

ALTER TABLE purchase_order

Code:

ADD FOREIGN KEY (po_suppref) [TAG fk1]

or when you specify the column FOREIGN KEY constraint when creating a column.

The persistent relationship on a particular column is handled by FOREIGN KEY …. REFERENCES or ALTER COLUMN … REFERENCES and does not create an index as an index is not required in the column’s table. An index in the referenced table is required and must be specified in the REFERENCES clause.

---

---