It would be nice if a constraint could target a View as well as a Table.

I have a single table called refCodes that has a list of all possible lookup codes along with a Code_Type column. So if Code_Type='STATE' then there are 50 records with Code_Value='AL', 'AB', ... 'NY' etc.

This same refCodes table would also have different code types like Code_Type='AREA CODE' with records for each area code.

So instead of having 40 or 50 different reference tables, one for each code type, I have them all in one table. This works great when accessing it from Delphi code, but I can't define a constraint in the database. The Constraints > Parameters page only references Target Table and not Target View.

I'd like to create a view called vStates and that would reference refCodes where Code_Type='STATE'. As long as the view has the index column (Code_Type) for the constraint, I don't see why it won't work.

Just my 2 cents.

Barry

Barry


The only constraints I use are primary key & unique - I loath database enforced RI - so forgive if this is total rubbish but foreign key constraints require an index on the columns in the reference table on the columns you're checking against and its not possible to create an index on a view is it?

As a possible workround what about using a computed column to hold the Code_Type eg

EXECUTE IMMEDIATE 'CREATE TABLE "vState"
(
"Code_Type" VARCHAR(20) COLLATE "ANSI_CI",
"Code_Value" VARCHAR(20) COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("Code_Type","Code_Value")
)

EXECUTE IMMEDIATE 'CREATE TABLE "Test"
(
"ID" INTEGER,
"STATE_CODE" VARCHAR(20) COLLATE "ANSI_CI" COMPUTED ALWAYS AS ''STATE'',
"vType" VARCHAR(20) COLLATE "ANSI_CI" COMPUTED ALWAYS AS ''STATE'',
CONSTRAINT "fkTest" FOREIGN KEY ("vType","STATE_CODE") REFERENCES "vState" ("Code_Type","Code_Value")
  ON UPDATE NO ACTION ON DELETE NO ACTION
)



Roy Lambert

Roy,

>The only constraints I use are primary key & unique - I loath database enforced RI -<
RI only becomes a problem when it's working.

> so forgive if this is total rubbish but foreign key constraints require an index on the columns in the reference table on the columns you're checking against and its not possible to create an index on a view is it?<

It is not necessary to explicitly create an index on the view, because the view can reference the index of the underlying table. It does so when the view is sorted or when an index column appears in the Where clause.

>As a possible workround what about using a computed column to hold the Code_Type<
Hmmm, interesting. That should work, thanks.

Barry

Barry

>>The only constraints I use are primary key & unique - I loath database enforced RI -<
>RI only becomes a problem when it's working.

and the tables get corrupted

<<It is not necessary to explicitly create an index on the view, because the view can reference the index of the underlying table. It does so when the view is sorted or when an index column appears in the Where clause.>>

I do hope my memory is capable of holding all these new bits of information.

Roy

Barry,

<< It would be nice if a constraint could target a View as well as a Table.
>>

This is a little tough to do right now, but I can add it to the list for the
next catalog format change.  Currently, the constraints are defined at a
"base table" level, not at the level above, which is the "virtual table"
level that precedes both tables and views.  Columns, indexes, and triggers
are defined at the "virtual table" level.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com