Hello folks. I just registered in the Yii Forum and this is my first post. I am a newbie in PHP and Yii and I was hoping you guys could me.
I have a table with a non-numeric primary key. The table looks something like this:
ClassificationCode Classification
E Extrapulmonary TB
P Pulmonary TB
I’m having problems with CRUD because I think it will only work with a numeric auto-incrementing primary key. How do I implement CRUD using the above table.
Thank you.
Herdie Hizon
Where are you having problems? Can you post some code?
Is it really extremely important that your tables have non-numeric primary/foreign keys. If possible just change them to numeric and make the primary keys auto_increment, it saves you a lot of hazzle and headache.
A piece of advice, it is considered a bad practice to use non-numeric keys specially from the maintenance point of view.
Imagine a situation where you have to use e.g. ‘john_doe’ as a primary key and this in turn is a foreign key in other 1000 tables, if you change ‘john_doe’ to ‘suzi_doe’ you will have to execute 1000 changes(updates).
Just a bit of clarification. Ideally, primary keys should be meaningless. Numeric vs non-numeric is less important. And since an auto incrementing key is the easiest way to make something meaningless, they are a common and useful choice. A meaningless primary key doesn’t prevent you from having any number of “unique” keys that might have meaning. For example, in a product table, you might store the manufacturer’s product code (unique per manufacturer, at least), but use a generated key as the primary key.
A non-numeric key like: ‘AZ00911’ can be a perfectly acceptable primary key. Let’s say for some reason you only want the primary key to be 7 characters long, but you’re afraid that you might run out of numbers if you use a numeric key. You can generate unique values by rotating the character portion of the key while looping through the numeric portion. If ‘AZ’, however, represents Arizona, then the key is no longer meaningless. This is more work than a simple, numeric key, but it can be done.
The maintenance issue isn’t really about the use of non-numeric keys. It’s about using “meaningful” data as the primary key. Meaningful data is subject to change. Using a name (as Omoro mentioned) as the primary key creates a problem when someone changes their name.