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)
);
At least if you can provide a settings to enable primary key update would
be very helpful for us.
Regards.
Venkat
On Monday, 23 September 2013 16:14:44 UTC-4, Lukas Eder wrote:
>
> Hi Venkat,
>
> 2013/9/23 Venkat Sadasivam <[email protected] <javascript:>>
>
>> Ran into a show stopper issue of not able to update primary key column
>> values.
>>
>
> This is a show stopper to you? Could you elaborate a little on what makes
> this an important missing feature?
>
>
>> I feel jOOQ should provide a way to update primary key value through
>> Record objects.
>>
>> http://www.jooq.org/javadoc/latest/org/jooq/UpdatableRecord.html#store()
>>
>> Can I have your thoughts on this?
>>
>
> Sure. Just as mentioned on the store() method, the rationale behind this
> is simple:
>
> 1. jOOQ assumes working on a normalised schema. Changing primary key
> values is usually not expected practice.
> 2. If the primary needs to be changed, then this is mostly due to
> scripting / batching behaviour, where regular SQL is better suited than
> CRUD.
>
> Now, the above is certainly debatable. There is room for changing such
> behaviour in a major release, or enhancing it in a minor release. But I'd
> really like to understand the use case behind changing primary key values,
> first. In some databases, this may result in table locks, which usually not
> desireable...
>
> 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.
