Access2000: Creating a calculated index

A

Arvi Laanemets

Hi


In some table I have primary key (ID) structured as CNNNNNNNNNNN. The first
letter of key determines the group, the item is belonging to. All groups,
along with their names, are listed in another table. I'm trying to
estabilish the relationship between two tables, but I haven't found a way to
define an index for first table, calculated as LEFT(ID,1). Is there a way
to do this, or I have to split primary key field ID, and use an composed
index as primary key?


Thanks in advance!
 
P

peregenem

Arvi said:
In some table I have primary key (ID) structured as CNNNNNNNNNNN. The first
letter of key determines the group, the item is belonging to. All groups,
along with their names, are listed in another table. I'm trying to
estabilish the relationship between two tables, but I haven't found a way to
define an index for first table, calculated as LEFT(ID,1).
Click to expand...

You can use a CHECK constraint to enforce this business rule e.g.

CREATE TABLE Groups (
group_ID CHAR(1) NOT NULL PRIMARY KEY,
CHECK (group_ID LIKE '[A-Z]')
)
;
INSERT INTO Groups VALUES ('A')
;
CREATE TABLE Test (
ID CHAR(12) NOT NULL PRIMARY KEY,
CHECK (ID LIKE
'[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
CHECK (1 <= (
SELECT COUNT(*)
FROM Groups
WHERE Groups.group_ID = LEFT$(Test.ID, 1)
))
)
;
INSERT INTO Test (ID) VALUES ('A12345678901')
;
--success: 'A' exists in Groups

INSERT INTO Test (ID) VALUES ('D12345678901')
;
--fails: 'D' does not exist in Groups
 
Top