Modifying the type of a referenced primary key

[daph]

Hi everyone,

I have a table which has a primary key
USERNAME VARCHAR2(50)

I also have a lot of tables that refer to that primary key, fields are also type VARCHAR2(50)

Is there a way to ensure that if the primary key's type is change from VARCHAR2(50) to VARCHAR2(150), that all the refrenced fields from the other tables will have to be updated also?

Presently, if I update the primary key OR the reference fields types, I get no error or warning.


Any help would be appreciated,

Thank a lot!

Daph

 

[nbateman]

Daph,

There is probably some fancy way of doing what you want with triggers or something, but honestly, this will be the least of your problems.

A name is really just about the worst possible primary key in the world. Until you add a simple sequential numeric key or something like it, and use that to connect the related tables, you will suffer literally forever with names that change, mis-spellings, capital letters or not, added spaces, hyphens, etc. etc. etc.

Sorry, probably not what you wanted to hear, but honestly, spend you effort once changing the design of the key and it will solve not just this problem but a thousand more.

Nick.

 

[daph]

That's ok

For the primary key, it is unique. It is a unique login that is used on the network. No two will be alike. It's not just a common name.

Thanks for your reply anyways, I appreciate it!

Daph

 

[sem]

I agree with Nick. Primary key field is reproduced in each related table thus wasting a lot of space. It's really a weak design, even if now your database contains relatively small number of records. And you hit one of the demostrations of its weakness (in future you may hit others).

Regards, Dima

 

[jee]

If, in spite of all the advice against it, you want to continue using your username as a primary key, you can write a script to do the updates automatically. You can access the USER_CONSTRAINTS to identify the tables that have FK contraints that tie back to your username and then use the USER_CONS_COLUMNS view to identify the columns in the tables that need to be expanded. This assumes you are only dealing with one schema. If you are dealing with multiple schemas, use the ALL_CONTRAINTS and ALL_CONS_COLUMNS views.

BTW: When I first started designing databases about 8 years ago, like you I preferred to use "meaningful" primary keys instead of "artificial" keys. They do make it easier to understand the raw data. However I learned through experience that almost always (there are no abolute rules in database design) an artificial key is preferable. That is what the Oracle sequence generators were designed for. There are two main reasons for using artificial primary keys:

1) Performance - unless your tables are tiny, your 150 character column as a PK/FK will really slow your performance.

2) Ease of future maintenance - believe me, you may think you can see into the future and you just know that nothing will ever change again that might cause you to have to modify your PK/FKs, but trust me - it isn't so. I thought that way too.

 

[nbateman]

Yep, we've all been through it.

I understand something of where you're coming from though Daph.
Your names are actually logins and can therefore at least be assumed to be unique (not like a normal name with several John Smiths for example).
As well as unique though, primary keys really have to un-changing as well, and only you currently know how static your user names are likely to be. If they are something like accounts123 for example, you may be OK. If however they are something like jane.ayre, expect jane to want a change when she marries John Smith. Just one example.

You can of course also achieve uniqueness with a unique constraint rather than a primary key.


One other thing I was just curious about: You have user logins that are longer than 50 characters? How long does the password need to be!? I suspect there are aspects to your data, that we just do not appreciate.

Good luck, whatever you do.

Nick.