Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 5 of 5 total |
Foreign Key can't target a view |
Wed, Sep 11 2013 4:25 PM | Permanent Link |
Barry | 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 |
Thu, Sep 12 2013 7:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Sep 12 2013 11:17 PM | Permanent Link |
Barry | 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 |
Fri, Sep 13 2013 5:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Sep 17 2013 12:51 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |