Meaningless Foreign KeysPosted: September 5, 2007
Jonathan Lewis wrote an article about Meaningless Keys. It’s an old article, but I just read it today and I found it very inspiring. So immediately I started thinking about my latest project.
For the project, I created a customers table. Each customer has a unique name, but it also has a meaningless ID, generated as sequence, and this meaningless id is used as a foreign key in other tables.
Why do I have this meaningless ID? Well, by now it was a habit, but I can still remember the reason – you don’t want to duplicate the same piece of data in multiple tables. Thats a known best practice for developers. But why? Because you don’t want to waste space – but in this case it is just varchar(50) column in a table that will never reach 100,000 rows, the space is not the issue. Also because if we will want to change the customer name we will have to change it in many places. We will have to hunt down its usage in all the child tables and update them as well. Thats the real bummer – it will complicate the application and make it more bug prone.
So, maybe Jonathan Lewis got it wrong? Or maybe I got him wrong? Maybe my meaningless key is not such a bad idea after all? Too bad, it would have been nice using the customer name as the key and avoiding all these join operations. If I could just solve the update problem. Why can’t I create a foreign key constraint with “on update cascade”?
Actually, I can.
Oracle didn’t provide this feature as built-in, but Tom Kyte wrote an update cascade package that does exactly what I just describe – it updates all the child references when you update a key on the parent table. I used it, it works great.
I would have given an example here, but this is Tom Kyte we are talking about, and of course he gave a great example that is much clearer than what I can put here. So just go check it out.