Update value of primary key impossible

[Tom Tomm]

I created "Entity class from database" after which I generated "Master/Detail Sample form". But when I wanted to make changes to my table I got this error:

The attribute [homeUrl] of class [dbInterface.Place] is mapped to a primary key column in the database. Updates are not allowed.

However, the above sentence is a BIG lie by netbeans, because:

mysql> UPDATE Place SET homeUrl='kkkk' WHERE name='asd';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

So yeah, I can update field manually, but my auto-generated programm can't because it's stupid. Heh
The question is: how to make it smart and allow it?

Any help?

[chris webster]

Netbeans is trying to save you from yourself.

The point of a primary key is that you can rely on it to uniquely identify a particular record, and it should be stable because the PK is used in foreign keys from related tables as well, so it's a lot easier to maintain these relationships if you don't keep changing the PK. That's why many DBs will stop you changing the PK column values.

If you think you may have to change a primary key, then it probably isn't your primary key at all (although it may still be a unique key which you can define separately from the PK), so you should possibly consider using a surrogate key (a numeric ID) as your PK instead.

The key.
The whole key.
And nothing but the key.
So help me Codd.

[Tom Tomm]

chris webster wrote:Netbeans is trying to save you from yourself.

But I so much don't want to be saved.

chris webster wrote:
The point of a primary key is that you can rely on it to uniquely identify a particular record

That's why the primary key in my table is a combo of 2 fields. Not just one. And I do want to be able to change homerUrl field, because websites sometimes move to different adress, so being able to change stuff would be nice.

chris webster wrote:
and it should be stable because the PK is used in foreign keys from related tables as well, so it's a lot easier to maintain these relationships if you don't keep changing the PK. That's why many DBs will stop you changing the PK column values.

I deleted absolutely everything that had the word PK in it. I deleted all `PK` lines, because if I didn't the generated GUI didn't contain the primary key fields at all (not to insert, not even to view). So I copied a working example from google that uses IdClass. Now I can view fields, insert fields and delete fields. Can't update And for the record, if I re-generate the default class that has those pesky PK lines then my gui/view class doesn't work at all.

chris webster wrote:
If you think you may have to change a primary key, then it probably isn't your primary key at all (although it may still be a unique key which you can define separately from the PK), so you should possibly consider using a surrogate key (a numeric ID) as your PK instead.

Adding something that is artificial and completly useless is: 1. Bad programming practice 2. Even worse programming practice 3.The homeUrl is being used in various other tables as a foreign key, so doing smth like: delete the row, insert new one won't work, because of the cascade effects it has. And sinnce MySQL itself does support updating primary key and if MySQL itself thinks it's ok, then who is Netbeans to judge it.

chris webster wrote:
The key.
The whole key.
And nothing but the key.
So help me Codd.

I've heard that sentence spoken by my sql teacher some time ago...

[chris webster]

Hi Tom,

You can replace the PK with a surrogate key, because that is clearly what you need. You haven't found a usable primary key, just a unique but unstable composite key that is already causing you problems. Using the URL as a foreign key also looks like a pretty bad idea - how many times do you want to have to change it in your database if the real-world URL changes?

The standard way of dealing with this situation in a relational database is to use a numeric surrogate key that can be guaranteed to be both unique and stable. Plus it's a lot easier to handle numeric IDs in FKs where you have a fairly deep tree of child entities.

I've no idea what "good programming practice" is in Java-land, although I believe that Java uses some kind of Object IDs internally to uniquely identify object instances rather than relying on an arbitrary and unstable combination of attribute values. And just because you can do something e.g. in MySQL, doesn't always mean it's a good idea.

So if you want to build a maintainable database system, why not use the same techniques that the rest of the database industry has been using for 30 years?

Anyway, good luck with it.

Cheers,
Chris

[Rob Spoor]

Tom Tomm wrote:

chris webster wrote:
The point of a primary key is that you can rely on it to uniquely identify a particular record

That's why the primary key in my table is a combo of 2 fields. Not just one. And I do want to be able to change homerUrl field, because websites sometimes move to different adress, so being able to change stuff would be nice.

If the URL needs to change now and then, then it's not a good candidate for the primary key (as Chris already mentioned). Primary key values should get their values upon insertion and afterwards never change. Doing so could jeopardize the entire integrity of your data model; if you don't enforce foreign keys then linked records all of a sudden link to nothing. As suggested, pick a different primary key. If you can't find a single column that is both unique and will remain unchanged, use an auto-incremented numeric surrogate key.

On a side note, I also wouldn't recommend using a unique key for the URL, as that would block you from adding the same site using two names (unless that's what you want).

chris webster wrote:
If you think you may have to change a primary key, then it probably isn't your primary key at all (although it may still be a unique key which you can define separately from the PK), so you should possibly consider using a surrogate key (a numeric ID) as your PK instead.

Adding something that is artificial and completly useless is: 1. Bad programming practice 2. Even worse programming practice 3.The homeUrl is being used in various other tables as a foreign key, so doing smth like: delete the row, insert new one won't work, because of the cascade effects it has. And sinnce MySQL itself does support updating primary key and if MySQL itself thinks it's ok, then who is Netbeans to judge it.

As someone who has been both a professional developer and a database administrator for over 7 years, trust me on this: auto-incremented numeric keys are far from bad practice. It's a technique used in more applications than you can even think of.

As for your third point, it clearly shows why using the URL as a primary key is a bad idea. If you need to change it in the main table, you need to modify the same value in all linked tables as well. If you have your database system enforce these relationships then it won't even allow you to modify the URL. By using the surrogate key you only need to modify the URL in one location - the main table. All other tables will no longer have the URL directly, but can still access it from the main table through joins.

[Martin Vashko]

Tom, I can serve some personal experience. We've a system where some entities (buildings and other large industrial devices) are identified by numerical codes used throughout the organization. Everyone there knows the codes from their own head, since they use them daily, and these codes are also used to communicate with other systems. When we started to develop it years ago, we were repeatedly assured these codes are stable, identical across the organization and don't ever change. A building would have to be torn down and rebuilt to have its code ever changed - or so were we said. We therefore chose that code as an ideal primary key. So the system has been built and deployed and is growing ever since.

Soon after it emerged out the codes are not so immutable after all. A device has been brought from testing regime to production regime and has been assigned a new code due to the change. Then a new database system has been added to the mix and their codes miraculously didn't match our codes, to great amazement of everyone involved. In short, we had to implement updates of primary keys, and since we're on Oracle which does not support cascaded updates, we had to build it on our own. Its actually not that difficult, as there are deferrable constraints in Oracle and our code uses metadata to figure out what has to be changed and where (so when adding a new table to the schema we don't need to touch the renumbering code), but some effort it was.

However, the system and the amount of data it manages has grown over the years (we now keep lots of values for every hour in the interval of several years for many of the entities) and is now used 24/7. And since so much data is affected by primary key change, it takes so much time it is no longer feasible to do so during normal operations. We'd have to take the system offline for an hour or so, only to change one single number. Ouch. Of course, the inevitable has come and we're now planning an upgrade which will separate the entity code from its primary key.

You're now at the beginning of your journey. You even know the columns of your potential PK are not immutable. You don't need to make that mistake.

I can see others have already answered your specific objections. I'd just comment on "what MySQL itself thinks is ok" - MySQL does not think anything, it just gives you tools. Every tool can be used or abused. In MySQL's case, a cascaded update can save you some development effort if you get your database design wrong and find out too late that a primary key is actually mutable. But even MySQL will not magically make the update cascade of millions of records happen fast, if you end up having them.

[Wendy L Gibbons]

I agree with you, cascading,combined keys are a wonderful idea, in fact a lovely idea. When I was taught them I wondered why everyone didn't use them...
But such a pain in reality, unless you have a well written database layer to handle it for you if you ever do want to change your key value.

Of course you could scrap netbeans and write your own java.

[Tom Tomm]

Thank you for your help. You are so so so so so pro. Or even more pro that I can comprehend Thank you again.

--
That table maybe one day will reach max 1000 entries, so updating every row would take maybe 1 second for a fast computer? So I have never ever thought of performance issues. And that's is's because of them java forbids what it forbids.

--
I clicked on "Resolved"

--
I already read PMs and filled the form needed, but it said it is awaiting moderation.

[chris webster]

Glad you feel your problem has been resolved, Tom.

But you might want to take the trouble to learn a bit more about relational data modelling before you start working on real-world database systems where you can't get away with this kind of thing so easily. As Martin pointed out (and I've been there too, Martin ), it can be a real PITA having to unpick and re-work badly designed key relationships in an existing system. Much better to get them right in the first place than leave a mess for the next person to clear up.

Anyway, relational databases are like any other technology: if you want to get the most out of them you need to understand how they work.

All the best.
Chris