2013/9/25 Venkat Sadasivam <[email protected]> > Hi Lukas: > > Yes, it is debatable. If someone updates primary key value then all parent > and child tables have to be updated so I agree that updating primary key > column is not a good idea in general. This case is applicable for 80% of > tables in our schema, for 80% tables we do not allow anyone to change > primary key values. > > However a few tables require primary key value to be updated mainly during > data entry, for example the below table CityName is primary key as the > value has to be unique during the data entry if someone made a typo then we > should allow them to update the newly INSERTED record hence we need a way > to update primary columns as well. > > CREATE TABLE City( > CityName VARCHAR(100) NOT NULL > CONSTRAINT PK_City PRIMARY KEY (CityName) > ); > > Another example, in our schema many mapping table require primary key > column values require to be updated. For the below sample table data entry > person have to choose employee, department and then assign a role. If they > select a wrong role then our application should allow them to correct them > without loosing data. > > CREATE TABLE EmployeeDept( > EmployeeId INT NOT NULL, > DepartmentId INT NOT NULL, > EmployeeRole INT NOT NULL, > Comments VARCHAR(4000), > CreatedDate DATE, > LastModifiedDate DATE > CONSTRAINT PK_EmployeeDept PRIMARY KEY (EmployeeId, DepartmentId , > EmployeeRole) > ); >
I understand those use-cases, and they tend to follow relational principles. But from a usability and design perspective, it is often much simpler to have single-column technical IDs and put a unique constraint on whatever other column combination constitutes a "key". In both examples, the fact that column values *can* be modified after insertion, suggests that those columns should not be part of the primary key. Of course, I undestand that legacy applications might have such issues. > At least if you can provide a settings to enable primary key update would > be very helpful for us. > I will think about this: https://github.com/jOOQ/jOOQ/issues/2764 One reason for the current design is the fact that Record.original() wasn't available from the beginning. Cheers Lukas -- 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/groups/opt_out.
