1

i have table. which has 5 columns in that 3 of the columns makes primary key combinations.

table (cola, colb, colc, cold, cole)

i want to update one of the column which is in primary key group. how to do that?

its giving primary key constraint error.

Mohamed Saligh
  • 12,029
  • 19
  • 65
  • 84

3 Answers3

2

You should disable do your modification an re enable the constraints that are linked to your primary key. (Unique, non-null, etc...)

Take a look at this website

Spredzy
  • 4,982
  • 13
  • 53
  • 69
2

If you really need to maintain uniqueness over these three columns, then define a unique constraint on the three columns making up your current PK, and then define a new surrogate primary key column.

davek
  • 22,499
  • 9
  • 75
  • 95
  • 1
    Why? A primary key is just a candidate key. It doesn't make a difference which uniqueness constraint you use to enforce keys. As for adding a surrogate, well that all depends on how you are going to use it. There is no point adding a surrogate just for the sake of it. Stability (not "immutability") is a useful property of any key but it isn't an absolute requirement. Even surrogate keys need to be updated sometimes. – nvogel Dec 17 '10 at 10:58
  • 4
    If you are having to update the primary key for anything other than exceptional circumstances (data migration, for example), then I would suggest there is something wrong with the design: http://stackoverflow.com/questions/2499246/how-to-update-primary-key/2499507#2499507 – davek Dec 17 '10 at 11:03
  • 1
    I don't agree. There are good reasons why you might want to do it. But in any case your suggested solution doesn't make any practical difference. Updating a constraint enforced by a PRIMARY KEY constraint is exactly the same as updating a key enforced by a UNIQUE key constraint. If you really meant to say something about *foreign keys* then I think you could have made that clear - there might not be any foreign keys referencing the compound key and even if there are then whether they need to updated depends on the requirements. So I think your answer does not help at all. – nvogel Dec 17 '10 at 11:35
  • 1
    A primary should identify an item through it's entire lifetime. Updating a primary key implicitly acknowledges that you have some out-model knowledge that the before update and after update values truly represent the same item. What truly defines the row isn’t in your model. – Rob van Laarhoven Dec 17 '10 at 12:50
  • 1
    A database is a set of facts. ANY useful update implies some outside knowledge about the facts identified in the database. Keys are no different in this respect and are just as updateable as any other attributes. Take the example of a key on user name. There's no reason why I shouldn't change my user name if I choose to. At one point my account is identified by one user name and at a later point it is identified by a different user name. An important feature of the relational model is that information is indentified ONLY by its attributes without needing "special" structures to identify data. – nvogel Dec 17 '10 at 13:08
  • 2
    Well that exactly IS the difference between a unique key and a primary key. If it can change it can not, by definition, be a primary key, like it or not. Think about references outside of your database to a specific item, how do you handle update of PK? – Rob van Laarhoven Dec 17 '10 at 13:11
  • 1
    A primary key is a candidate key. By definition there is no difference at all. Any of numerous database textbooks can confirm that definition for you. The choice of primary key is "arbitrary" (according to E.F.Codd) and "purely psychological" (according to Chris Date). The fact that you need to identify things outside the database is precisely why keys need to change. Real things DO change their identification - so their database representation must change too. This is not a problem - it's a feature. – nvogel Dec 17 '10 at 13:23
  • @Robert and @davek - you have obviously never worked with legacy databases. If you could not, by definition, modify a primary key, then Oracle would not let you do it. It's a design preference, definitely, but you're being too rigid. Agree with dportas on unique key, but it might be that we are trying to update the key in 2 operations and you need some sort of deferred constraint. We need more clarity on the question, though. – orbfish Dec 20 '10 at 17:13
  • You have obviously never had a good discussion with someone. A belittleling remark does not count as an argument. I agree on it beeing a design preference. I disagree on : oracle allows it so it's a good design principle. – Rob van Laarhoven Dec 20 '10 at 21:43
  • @Robert : I think the point is not to let design preferences or dogma become a "definition" of what a key is or what you "must" or must not do with keys. In all cases, requirements should determine what you need to do. There is nothing wrong in principle with updating a key if it makes sense of some business requirement to do so. This matters because there is such an enormous amount of rubbish and bad advice written about database issues and particularly about keys. – nvogel Dec 21 '10 at 16:49
  • @dportas : agreed my statement was as little to blunt. But I still think it's a good rule. And as with everything : rules are there to be broken (if you know exactly what your dooing). I prefer a surrogate key over a natural key that can be updated. I'd like to discuss this further but I don't think this is the place. – Rob van Laarhoven Dec 23 '10 at 09:36
  • @Robert : Actually I think it's a useless "rule" because it's usually impossible to enforce or verify that a key cannot or never has been updated. Since you may always have a future business requirement to update a key it's actually a much better rule to assume that any key WILL be updated at some point in its life. Stability, familiarity and simplicity are useful and important properties to consider when implementing keys but "immutability" is not. – nvogel Jan 05 '11 at 12:27
0

Just in case you have to change the referncing data too. First note contrary to MS-SQL-Server there is no foreign Key contraint with on update cascade see How to create a Foreign Key with “ON UPDATE CASCADE” on Oracle?.

Than I would insert a new row in the primary table, update the referencing table to reference the new row and finally delete the original primary row.

Community
  • 1
  • 1
bernd_k
  • 11,558
  • 7
  • 45
  • 64