No Fks in SQL db - Uniface Software

  • I have a database which is supplied by a vendor, they say we cannot have db foreign keys as the front end is Uniface and the FKs are handled there.

    So in the BE db we have got data that would violate the FKs, also we have no way of seeing relationships between tables. Will this also affect execution plans of queries?

    Thanks for any assistance.

  • A lack of Referential Integrity (foreign keys, constraints, etc) will certainly affect execution plans, and not in a good way.  From their Wikipedia page: "Uniface applications are database- and platform-independent".  Meaning complete crap no matter what database system you try to pair them with.  

    The company was founded in 1986, when it might have made sense to aim for platform independence rather than specialize in FoxPro or DBase or one of the other options back then.  In the 30 years since they should have figured out how to use foreign keys and other features that adhere to SQL standards.

    I'm saddled with a different 80's-era 4GL system that, in the name of "platform independence", can't use IDENTITY and even has it's own half-assed roll-your-own record locking that SQL is unaware of.  A system we got from another vendor had the most ridiculously complicated system of enforcing referential integrity, with cascading updates and deletes, through triggers instead of foreign keys.  Where these triggers were used they worked, and the designer must have been a genius because a lesser mortal could not have gotten it to the first beta version.  I think the original genius must have cashed in and moved on (or had a breakdown), because tables added later by whatever muppets they replaced him with used foreign keys instead of triggers.  Sometimes on the same columns.  And they were missing around 1500 indexes to support the foreign keys (hard to tell about the triggers).

    Sorry for the long rant.  There may be a counter example somewhere, but to me "platform-independent" means "we have no #($*&% clue how to design a database".

  • I've worked with systems like that before, as far as seeing the relationships between tables they're probably expecting to look in whatever development tool they have for the system.  As far as relational integrity it may or may not actually impact the application, and you also would need out why it's off is it off because the application itself is screwing it up or because people were messing with the data manually?  The system I worked with did a good job of maintaining integrity on its own.

    As far as performance?  It depends on what kind of queries it's running and whether they've built indexes appropriately.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply