Hi Aram, Sure, I understand the use case in your case. The comment on #7155 was a snark at database designs where *tables* themselves do not enforce data integrity through constraints. I've seen many such schemas in my life. The explanation never justified the decision... :-) But it wasn't about views. Few databases allow constraints on views.
Thanks, Lukas On Thu, Sep 13, 2018 at 4:37 PM <[email protected]> wrote: > > Perfect. > > On the note for 7155 -- in mysql (I know it's not your favourite rdbms) a > view doesn't have a primary key, even though the base table does. > > That's why I asked if an enhancement request can be added to do a single > column synthetic key. > > example: > > TABLE employes > ------------------------------ > empId VARCHAR(64) PRIMARY KEY > jobTitle VARCHAR(64) > > > SHOW INDEX FROM employees; > > employee | 0 | PRIMARY | 1 | empId ... > > > > > CREATE VIEW engineers AS SELECT * FROM `employees` WHERE `jobTitle` = > 'Engineer'; > > > > SHOW INDEX FROM engineers; > Empty set (0.00 sec) > > > > Thank you very much. > > On Thursday, September 13, 2018 at 2:59:03 AM UTC-4, Lukas Eder wrote: >> >> Hi Aram, >> >> Strictly speaking, an index is not a requirement for a unique constraint, >> even if from a performance perspective, it would be silly not to have an >> index backing a constraint - and most databases automatically create such >> an index when the constraint is created. >> >> So, I agree, the syntheticPrimaryKeys feature should also produce >> "syntheticIndexes". I have created these feature requests: >> >> - https://github.com/jOOQ/jOOQ/issues/7858 >> <https://www.google.com/url?q=https%3A%2F%2Fgithub.com%2FjOOQ%2FjOOQ%2Fissues%2F7858&sa=D&sntz=1&usg=AFQjCNH2fgiuf0Y9GPjYduow_Y5aB4Hhsg>: >> Specification of <syntheticIndexes> >> - https://github.com/jOOQ/jOOQ/issues/7859: Auto creation of >> <syntheticIndexes> from all <syntheticPrimaryKeys> >> >> For completeness sake, there should also be an option to generate: >> >> - https://github.com/jOOQ/jOOQ/issues/7860: Synthetic unique keys >> - https://github.com/jOOQ/jOOQ/issues/7155: Synthetic foreign keys >> >> I hope this helps, >> Lukas >> >> On Wed, Sep 12, 2018 at 7:37 PM <[email protected]> wrote: >> >>> >>> Hi, >>> >>> We're doing a new project which uses spring-data and we're using Jooq >>> 3.11.1 as our generator only for now. >>> >>> As part of the generation, there are several tables that are "filtered >>> views" which means they don't have an independent PrimaryKey, but do have >>> the original "id" column which is a unique key in the base table. >>> >>> It does produce a @UniqueConstraint but Spring-data is still not happy >>> with that. Should the use of syntheticPrimaryKeys be adding an @Index >>> annotation as well? >>> >>> As an enhancement request-- it would be great if one column was enough >>> to kick off the annotation since we already have a unique field, just not >>> marked as a primary key. >>> >>> Aram >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "jOOQ User Group" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> For more options, visit https://groups.google.com/d/optout. >>> >> -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
