这样行有一个字母数字字段作为PrimaryKey的？ [英] Is this okay to have a Alphanumeric field as a PrimaryKey?
问题描述
我重写，包括重新设计数据库，它需要从Oracle到Oracle数据迁移新的时间表应用程序。
I am rewriting a new timesheet application including redesigning database and it will require data migration from Oracle to Oracle.
在旧体制现场EmployeeCod是一个主键，它是字母数字的形式，即UK001，UK002，FR001，FR002，US001。 Employee表也与时间表及其他表，其中的Emp code被称为一个FK。
In the old system field ‘EmployeeCod’ is a Primary Key and it is in Alphanumeric form i.e. ‘UK001’, ‘UK002’,‘FR001’,’FR002’, ‘US001’ . Employee table is also linked to timesheet and other tables where the EmpCode is being referred as a FK.
为使联接在新系统中，我想在雇员表添加一个新的INT列，将其设置为PK进行得更快。 （不知道这是否会做任何大的差别）
To make the JOINs perform faster in the new system I was thinking about adding a new INT column in the Employee table and set it to PK. (Don't know if it will make any big difference)
- Employee表中有600行的
- 的Emp code的数据类型是VARCHAR2（20）在旧的数据库，我可以减少VARCHAR2（6）在新系统中，并为公司消费后改变它的
我想知道，如果它是更好地保持对EMP code作为主键，这将使事情变得更容易在迁移数据或我应该添加一个INT列？
有人给我下我的previous线程的一个提醒：
Someone has given me following advise in one of my previous thread:
如果你需要创建AANNN的复合code，那么我想这个拆分成两个：CHAR的一个简单的preFIX'字段（2）和INT的标识字段，然后再打开的Emp code成的邻接两个，坚持指数上有一个计算字段（@克里斯）
"if you need to create a composite code of AANNN then I'd split this into two: a simple 'Prefix' field of CHAR(2) and an identity field of INT, then turn EmpCode into a computed field that concats the two and stick an index on there that (@Chris)"
我不知道，如果这个选项会工作的员工表链接到其他表也是如此。 （EMP code在其他表被用作FK）
ñ
I am not sure if this option would work as employee table is linked to other tables as well. (EmpCode is being used as FK in other tables) n
推荐答案
如果您添加此PK，还能使前者PK，你将有一些数据管理问题来处理。或者，也许你的客户。摆脱旧的PK中，如果有谁将会升级到新的数据库现有用户可能不feasable。
If you do add this PK, and also keep the former PK, you will have some data management issues to deal with. Or perhaps your customers. Getting rid of the old PK may not be feasable if there are existing users who will be upgrading to the new database.
如果员工code，前者PK所使用的数据的用户识别员工，那么你将不得不添加约束，以确保该领域是独一无二的。同时携带codeS会消灭你所期望的任何性能提升。
If EmployeeCode, the former PK is used by the users of the data to identify Employees, then you will have to add a constraint to make sure that this field is unique. Carrying both codes will wipe out any performance gains you were hoping for.
如果是我的话，我会离开不够好孤单。性能提升，如果有的话，将是微不足道的。
If it were me, I'd leave well enough alone. The performance gains, if any, will be trivial.
这篇关于这样行有一个字母数字字段作为PrimaryKey的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
