I'm new to SQL Server and would really appreciate it if you could help me out here.
我是SQL Server的新手，如果你能在这里帮助我，我会非常感激。
So are a healthcare provider and internally we assign an ID to each patient (for example, 1234). I'm currently constructing another database, and I just wonder can I use our internal IDs as primary key, given they are unique? If so, since I am not going to do any calculation on the primary key, can I set them to string/char datatype for primary key?
医疗保健提供者也是如此，我们在内部为每位患者分配一个ID（例如，1234）。我正在构建另一个数据库，我只是想知道我可以使用内部ID作为主键，因为它们是唯一的吗？如果是这样，因为我不打算对主键进行任何计算，我可以将它们设置为主键的字符串/ char数据类型吗？
5 个解决方案
#1
2
In short, yes you can but it is not recommended at all!
简而言之，是的，你可以，但根本不推荐！
To give you some heads up:
给你一些提醒：
- Primary keys should never change
- 主键永远不会改变
- You cannot use a natural key or a key form other system
- 您不能使用自然键或其他系统的键
- They cannot have any formula
- 他们不能有任何公式
- Use short but suitable key type
- 使用简短但合适的钥匙类型
If you have an external key that you want to use to find some patients, create another column for it and add UNIQUE Constraint to it. just don't forget to add index for that column
如果您有一个外部密钥要用于查找某些患者，请为其创建另一个列并为其添加UNIQUE约束。只是不要忘记为该列添加索引
Read this post of mine for more information:
阅读我的这篇文章了解更多信息：
http://pilpag.blogspot.dk/2016/06/relational-database-designsimple-rules.html
http://pilpag.blogspot.dk/2016/06/relational-database-designsimple-rules.html
#2
1
The conditions for a primary key are that the key is unique in the table and never NULL.
主键的条件是键在表中是唯一的，永远不是NULL。
Your patient id would appear to have these characteristics.
您的患者身份似乎具有这些特征。
That said, there are good reasons for developing a synthetic primary key (auto-incremented/identity/serial depending on the database). More importantly, the actual patient ID may be sensitive information. For instance, patients might use the id when logging in or it might be printed on invoices.
也就是说，有充分的理由开发合成主键（根据数据库自动递增/标识/串行）。更重要的是，实际的患者ID可能是敏感信息。例如，患者可能在登录时使用id，或者可能在发票上打印。
It might not be a good idea to have sensitive information repeated throughout the database. For this reason, an "internal" id would be used to refer to patients in table and all the sensitive information would be contained in one or a handful of tables.
在整个数据库中重复敏感信息可能不是一个好主意。出于这个原因，“内部”id将用于表示表中的患者，并且所有敏感信息将包含在一个或几个表中。
This would perhaps be more obvious if the "patient id" were a government id ("social security number") or email address.
如果“患者ID”是*ID（“社会安全号码”）或电子邮件地址，这可能会更明显。
#3
1
Yes, but the ID can also be numeric and a primary key - it doesn't have to be a string. As long as the ID is unique, you should be fine.
是的，但ID也可以是数字和主键 - 它不必是字符串。只要ID是唯一的，你应该没问题。
#4
0
Yes, you can use your internal IDs if they are unique;PK limit is 900 bytes for char/varchar data types.So if your IDs are int is fine. But if your IDs can change with time or can be reused them for more than one patient I strong recommend not to use them to avoid chaos. I prefer a surrogate key, like an identity
是的，如果它们是唯一的，你可以使用你的内部ID;对于char / varchar数据类型，PK限制是900字节。所以如果你的ID是int就可以了。但是，如果您的ID可以随时间变化，或者可以为多个患者重复使用，我强烈建议您不要使用它们来避免混乱。我更喜欢代理钥匙，比如身份
#5
0
If I understand correctly, you are assigning each patient a number so as to uniquely identify them. So a report would contain the patient number rather than only a patient's name which can be ambiguous. You won't ever change the patient numbers, because then you'd have to change this in all databases and would have to re-print all documents on the patient that are still needed. This makes this number a perfect primary key for a patient table in any of your databases.
如果我理解正确，您将为每位患者分配一个号码，以便唯一地识别它们。因此，报告将包含患者编号，而不仅仅是患者姓名，这可能是模棱两可的。您将永远不会更改患者数量，因为您必须在所有数据库中更改此数字，并且必须重新打印患者仍然需要的所有文档。这使得该数字成为任何数据库中患者表的完美主键。
You could use a generated technical ID instead as the table's primary key and have the patient number only as another field in the table (which would still have a unique constraint of course, because it is still the business key uniquely identifying a patient). Whether to do this or not is mainly a matter of personal preference and experience. I prefer natural keys over IDs (so I would make the patient number the primary key). This stems from having worked with rather large databases with thousands of tables and much hierarchy where the natural keys proved to result in faster queries, enhanced data consistency and easier maintenance. Others may have different experience, though.
您可以使用生成的技术ID作为表的主键，并将患者编号仅作为表中的另一个字段（当然，它仍然具有唯一约束，因为它仍然是唯一标识患者的业务键）。是否这样做主要取决于个人偏好和经验。我更喜欢自然键而非ID（因此我会将患者编号作为主键）。这源于使用具有数千个表和多层次结构的相当大的数据库，其中自然键被证明可以提高查询速度，增强数据一致性并简化维护。但其他人可能会有不同的经历。
So yes, the patient number seems to be the perfect natural primary key in my opinion.
所以是的，在我看来，患者数量似乎是完美的自然主要关键。
#1
2
In short, yes you can but it is not recommended at all!
简而言之，是的，你可以，但根本不推荐！
To give you some heads up:
给你一些提醒：
- Primary keys should never change
- 主键永远不会改变
- You cannot use a natural key or a key form other system
- 您不能使用自然键或其他系统的键
- They cannot have any formula
- 他们不能有任何公式
- Use short but suitable key type
- 使用简短但合适的钥匙类型
If you have an external key that you want to use to find some patients, create another column for it and add UNIQUE Constraint to it. just don't forget to add index for that column
如果您有一个外部密钥要用于查找某些患者，请为其创建另一个列并为其添加UNIQUE约束。只是不要忘记为该列添加索引
Read this post of mine for more information:
阅读我的这篇文章了解更多信息：
http://pilpag.blogspot.dk/2016/06/relational-database-designsimple-rules.html
http://pilpag.blogspot.dk/2016/06/relational-database-designsimple-rules.html
#2
1
The conditions for a primary key are that the key is unique in the table and never NULL.
主键的条件是键在表中是唯一的，永远不是NULL。
Your patient id would appear to have these characteristics.
您的患者身份似乎具有这些特征。
That said, there are good reasons for developing a synthetic primary key (auto-incremented/identity/serial depending on the database). More importantly, the actual patient ID may be sensitive information. For instance, patients might use the id when logging in or it might be printed on invoices.
也就是说，有充分的理由开发合成主键（根据数据库自动递增/标识/串行）。更重要的是，实际的患者ID可能是敏感信息。例如，患者可能在登录时使用id，或者可能在发票上打印。
It might not be a good idea to have sensitive information repeated throughout the database. For this reason, an "internal" id would be used to refer to patients in table and all the sensitive information would be contained in one or a handful of tables.
在整个数据库中重复敏感信息可能不是一个好主意。出于这个原因，“内部”id将用于表示表中的患者，并且所有敏感信息将包含在一个或几个表中。
This would perhaps be more obvious if the "patient id" were a government id ("social security number") or email address.
如果“患者ID”是*ID（“社会安全号码”）或电子邮件地址，这可能会更明显。
#3
1
Yes, but the ID can also be numeric and a primary key - it doesn't have to be a string. As long as the ID is unique, you should be fine.
是的，但ID也可以是数字和主键 - 它不必是字符串。只要ID是唯一的，你应该没问题。
#4
0
Yes, you can use your internal IDs if they are unique;PK limit is 900 bytes for char/varchar data types.So if your IDs are int is fine. But if your IDs can change with time or can be reused them for more than one patient I strong recommend not to use them to avoid chaos. I prefer a surrogate key, like an identity
是的，如果它们是唯一的，你可以使用你的内部ID;对于char / varchar数据类型，PK限制是900字节。所以如果你的ID是int就可以了。但是，如果您的ID可以随时间变化，或者可以为多个患者重复使用，我强烈建议您不要使用它们来避免混乱。我更喜欢代理钥匙，比如身份
#5
0
If I understand correctly, you are assigning each patient a number so as to uniquely identify them. So a report would contain the patient number rather than only a patient's name which can be ambiguous. You won't ever change the patient numbers, because then you'd have to change this in all databases and would have to re-print all documents on the patient that are still needed. This makes this number a perfect primary key for a patient table in any of your databases.
如果我理解正确，您将为每位患者分配一个号码，以便唯一地识别它们。因此，报告将包含患者编号，而不仅仅是患者姓名，这可能是模棱两可的。您将永远不会更改患者数量，因为您必须在所有数据库中更改此数字，并且必须重新打印患者仍然需要的所有文档。这使得该数字成为任何数据库中患者表的完美主键。
You could use a generated technical ID instead as the table's primary key and have the patient number only as another field in the table (which would still have a unique constraint of course, because it is still the business key uniquely identifying a patient). Whether to do this or not is mainly a matter of personal preference and experience. I prefer natural keys over IDs (so I would make the patient number the primary key). This stems from having worked with rather large databases with thousands of tables and much hierarchy where the natural keys proved to result in faster queries, enhanced data consistency and easier maintenance. Others may have different experience, though.
您可以使用生成的技术ID作为表的主键，并将患者编号仅作为表中的另一个字段（当然，它仍然具有唯一约束，因为它仍然是唯一标识患者的业务键）。是否这样做主要取决于个人偏好和经验。我更喜欢自然键而非ID（因此我会将患者编号作为主键）。这源于使用具有数千个表和多层次结构的相当大的数据库，其中自然键被证明可以提高查询速度，增强数据一致性并简化维护。但其他人可能会有不同的经历。
So yes, the patient number seems to be the perfect natural primary key in my opinion.
所以是的，在我看来，患者数量似乎是完美的自然主要关键。