DB:是否要使用身份栏?[英] DB: To use identity column or not?

本文是小编为大家收集整理的关于DB:是否要使用身份栏?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

在设计桌子时,我的同事在这里说,我应该避免使用标识列,因为它特定于SQL Server和MS Access,但是我的观点有所不同,因为它使我的编码变得更加简单.

我是否应该使用身份列?如果不是什么,什么是从应用程序代码创建身份列的最佳方法?

推荐答案

您不能完全将应用程序与数据库供应商离婚.如果您这样做,您将无法获得数据库为您提供的任何功能的优势.

我会说使用身份列.如果您转到Oracle(例如),则可以使用序列.几乎没有大变化.

我不知道您正在使用哪种技术,但是有助于使用Hibernate或Ibatis(我认为它们都适用于Java和.net)的工具,这将您与您分开一点数据库实现详细信息.然后,如果您更改数据库供应商,则无需更改应用程序代码,只需配置即可. (理论上,至少!)

其他推荐答案

使用身份列!

它确实将您的"应用程序逻辑"与"业务逻辑"分开.

假设您使用"电子邮件"作为主要键(在"业务逻辑"方面确实有意义).当该电子邮件不再存在并且您的用户想编辑您的电子邮件时,您会陷入麻烦.

其他推荐答案

除非您完全了解其陷阱,否则请勿使用身份列,并且没有有效的理由使用序列或模拟序列发生器.

序列更加灵活,并且没有身份列具有的缺点/限制.

来自 :

自动施加的疼痛

在数据上复制 保留身份值

自动插入键可以成为 当您需要复制整体时痛苦 桌和保留主要钥匙 价值.试图直接插入 身份列将导致 错误提出.通常是 供应商提供了一些声明 允许您暂时放下 约束,因此您可以插入现有 值.在MSSQL中,您可以发行 命令

"在上设置Identity_insert产品".

其他供应商将要求您掉落 约束,然后重新启用它.

更多痛苦 - 如何检索价值 新插入的行?

此外,服务器通常 提供不同的方法来检索 新的身份列值 插入行.对于mysql,这是 last_insert_id()函数和 MSSQL是@@ Identity EG选择 @@ Identity.

MS SQL Server 2011将支持序列.

如果使用不支持序列的RDBMS服务器(例如2011年之前或MySQL),则有两种替代方法:

  1. 切换到 postgresql .这是最简单的选择.您将获得企业级,完全开源的RDBMS服务器.如果需要,您也可以得到商业支持.
  2. 使用支持模拟序列的数据访问库. jpa 2.0 实现,例如 eclipselink , Hibernate 制作这对您来说非常琐碎(表策略) .它与上述并不相互排斥.与PostgreSQL一起使用JPA 2.0将使您的生活变得比使用MySQL的RAW JDBC更容易.

本文地址:https://www.itbaoku.cn/post/597754.html

问题描述

While designing a table my colleague here says that I should avoid identity column as it is specific to SQL Server and MS Access, But I differ with his views as it makes my coding simpler.

Should I use identity column or not? If not what is best way to create the identity columns from application code?

推荐答案

You can't completely divorce an application from the database vendor. If you do you won't be able to take advantages of whatever features your database provides you.

I'd say use the identity column. If you move over to Oracle (for example), you can use a Sequence. Hardly a big change.

I don't know what technology you're using, but one thing that would help is using a tool such as Hibernate or iBATIS (I think they're both available for Java and .NET) which separates you a bit from the database implementation details. Then if you change database vendor you won't need to change application code, just configuration. (In theory, at least!)

其他推荐答案

Use Identity column!

It does separate your "Application Logic" from "Business Logic."

Let's say you use "email" as primary key (which does make sense in term of "business logic"). You'll get into trouble when that email no longer exists and your user wants to edit your email.

其他推荐答案

Do not use identity column unless you're fully aware of its pitfalls and has no valid reason to use SEQUENCE or an emulated sequence generator.

Sequences are much more flexible and do not have the disadvantages/restrictions that Identity columns have.

From http://www.jumpingbean.co.za/blogs/mark/identity_autoincrement_fields_and_database_sequences :

The pain with auto-incrementing

Copying over data while preserving identity value

Auto-incrementing keys can become a pain when you need to copy whole tabels and preserve the primary key value. Trying to insert directly into an identity column will result in a error being raised. Typically the vendor provides some statements that allow you to temporarily drop the constraint so you can insert existing values. In MSSQL you can issue the command

"SET IDENTITY_INSERT products ON".

Other vendors will require you to drop the constraint and then re-enable it.

More pain- how to retrieve value of newly inserted rows?

In addition the server usually provides different ways to retrieve the identity column value for a newly inserted row. For MySQL this is the LAST_INSERT_ID() function and for MSSQL it is @@identity eg select @@identity.

MS SQL Server 2011 will support SEQUENCE.

If you use a RDBMS server that doesn't support SEQUENCE (like MSSQL pre-2011 or MySQL), there are two alternatives:

  1. Switch to PostgreSQL. This is the easiest option, really. You get enterprise-grade, fully open source RDBMS server. And you can have commercial support too if you want.
  2. Use a data access library that supports emulating SEQUENCE. JPA 2.0 implementations e.g. EclipseLink, Hibernate make this very trivial for you (TABLE strategy). It's not mutually exclusive with the above. Using JPA 2.0 with PostgreSQL will make your life so much easier than, say, raw JDBC with MySQL.