如何自动增加varchar [英] How to autoincrement a varchar
问题描述
我可以在一个表格中创建一个主键,如c0001,c0002和供应商s0001,s0002 ol>
数据库设计中的想法是保持每个数据元素分离。每个元素都有自己的数据类型,约束和规则。那个 c0002
不是一个字段,而是两个。与 XXXnnn
相同。这是不正确的,它将严重限制您使用数据的能力,并使用数据库功能和设施。
将其分解成两个离散数据项:
column_1 CHAR(1)
column_2 INTEGER
然后将AUTOINCREMENT设置为是的,您的主键可以是(column_1,column_2)
code>,所以你没有失去任何意义 c0002
为您而设。
从不将供应商和客户(无论c和s的含义)放在同一张表中。如果你这样做,你将不会有一个数据库表,你将有一个平面文件。以及由此产生的各种问题和限制。
这意味着,规范化数据。你会得到以下结论:
-
一个表
或组织
包含公共数据(名称,地址
...) - code>客户包含客户特定的数据(
CreditLimit
...) - 一个表对于
供应商
包含供应商特定的数据(PaymentTerms
...) - 没有含糊或可选的列,因此没有空值
- 对使用或SQL函数没有限制
。
当您需要添加列时,只能在需要的地方进行操作,而不会影响平面文件的所有其他操作。效果范围仅限于变更范围。
Can I make a primary key like 'c0001, c0002' and for supplier 's0001, s0002' in one table?
The idea in database design, is to keep each data element separate. And each element has its own datatype, constraints and rules. That
c0002
is not one field, but two. Same withXXXnnn
or whatever. It is incorrect , and it will severely limit your ability to use the data, and use database features and facilities.Break it up into two discrete data items:
column_1 CHAR(1)
column_2 INTEGERThen set AUTOINCREMENT on
column_2
And yes, your Primary Key can be
(column_1, column_2)
, so you have not lost whatever meaningc0002
has for you.Never place suppliers and customers (whatever "c" and "s" means) in the same table. If you do that, you will not have a database table, you will have a flat file. And various problems and limitations consequent to that.
That means, Normalise the data. You will end up with:
- one table for
Person
orOrganisation
containing the common data (Name, Address
...) - one table for
Customer
containing customer-specific data (CreditLimit
...) - one table for
Supplier
containing supplier-specific data (PaymentTerms
...) - no ambiguous or optional columns, therefore no Nulls
- no limitations on use or SQL functions
.
And when you need to add columns, you do it only where it is required, without affecting all the other sues of the flat file. The scope of effect is limited to the scope of change.
- one table for
这篇关于如何自动增加varchar的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!