如何自动增加一个varchar

2021-12-26 00:00:00 sql-server database-design

我可以在一张表中创建一个像c0001、c0002"和供应商s0001、s0002"这样的主键吗?

Can I make a primary key like 'c0001, c0002' and for supplier 's0001, s0002' in one table?

推荐答案

  1. 数据库设计的理念是将每个数据元素分开.每个元素都有自己的数据类型、约束和规则.c0002 不是一个字段，而是两个.与 XXXnnn 或其他类似.这是不正确的，它将严重限制您使用数据的能力，以及使用数据库功能和设施的能力.

  1. 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 with XXXnnn or whatever. It is incorrect , and it will severely limit your ability to use the data, and use database features and facilities.

将其分解为两个离散的数据项:
column_1 CHAR(1)
column_2 INTEGER

Break it up into two discrete data items:
column_1 CHAR(1)
column_2 INTEGER

然后在 column_2

是的，你的主键可以是 (column_1, column_2)，所以你没有失去 c0002 对你的任何意义.

And yes, your Primary Key can be (column_1, column_2), so you have not lost whatever meaning c0002 has for you.

永远不要将供应商和客户(无论c"和s"是什么意思)放在同一张表中.如果这样做，您将没有数据库表，您将拥有一个平面文件.以及随之而来的各种问题和限制.

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:

  • 一个用于 Person 或 Organisation 的表格，其中包含公共数据(Name, Address...)
  • Customer 的一张表，其中包含客户特定的数据 (CreditLimit...)
  • 一个用于 Supplier 的表格，其中包含特定于供应商的数据(PaymentTerms...)
  • 没有歧义或可选的列，因此没有空值
  • 对使用或 SQL 函数没有限制
    .
  • one table for Person or Organisation 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.

相关文章