How to auto-increment varchar

Question

Is it possible to make a primary key, for example, 'c0001, c0002', and for the supplier s0001, s0002 'in the same table?

Answer 1

• The idea in database design is to save each data item separately. And each element has its own data type, restrictions and rules. This c0002 not one field, but two. Same thing with XXXnnn or whatever. This is not true, and it will seriously limit your ability to use data, as well as use the functions and capabilities of the database.

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

  Then set AUTOINCREMENT to column_2

  And yes, your primary key may be (column_1, column_2) , so you have not lost any c0002 value for you.

• Never place suppliers and customers (no matter what “c” and “s” mean) in the same table. If you do this, you will not have a database table, you will have a flat file. And the various problems and limitations associated with this.

  This means normalizing the data. You'll get:

  • one table for Person or Organisation containing common data ( Name, Address ...)
  • one table for Customer containing customer-specific data ( CreditLimit ...)
  • one table for Supplier containing supplier-specific data ( PaymentTerms ...)
  • there are no ambiguous or optional columns, therefore no Nulls
  • No restrictions on usage or SQL functions
    ,

  And when you need to add columns, you only do it where necessary, without affecting all other file requirements. The scope is limited to the scope of changes.

Answer 2

My approach:

• create an ID INT IDENTITY column and use it as your primary key (it is unique, narrow, static is ideal)

• if you really need an identifier with a letter or something, create a computed column based on ID INT IDENTITY

Try something like this:

 CREATE TABLE dbo.Demo(ID INT IDENTITY PRIMARY KEY, IDwithChar AS 'C' + RIGHT('000000' + CAST(ID AS VARCHAR(10)), 6) PERSISTED ) 

This table will contain ID values ​​from 1, 2, 3, 4........ , and IDwithChar will be something like C000001, C000002, ....., C000042 , etc.

In doing so, you have the best of both worlds:

• the correct, perfect primary key (and the clustering key) in your table, perfect for linking from other tables

• your personal identifier, correctly defined, calculated, always updated .....

Answer 3

Yes, actually these are two different questions, 1. Is it possible to use the varchar column as an auto-increment column with unique values, such as the number of rolls in a class

ANS: Yes, you can get it right by using the code snippet below without specifying the ID and P_ID values,

 CREATE TABLE dbo.TestDemo (ID INT IDENTITY(786,1) NOT NULL PRIMARY KEY CLUSTERED, P_ID AS 'LFQ' + RIGHT('00000' + CAST(ID AS VARCHAR(5)), 5) PERSISTED, Name varchar(50), PhoneNumber varchar(50) ) 

2. Two different increments in the same column,

ANS: No, you cannot use this in one table.

Answer 4

I prefer artificial primary keys. Your requirements can also be implemented as a unique index in a computed column:

 CREATE TABLE [dbo].[AutoInc]( [ID] [int] IDENTITY(1,1) NOT NULL, [Range] [varchar](50) NOT NULL, [Descriptor] AS ([range]+CONVERT([varchar],[id],(0))) PERSISTED, CONSTRAINT [PK_AutoInc] PRIMARY KEY ([ID] ASC) ) GO CREATE UNIQUE INDEX [UK_AutoInc] ON [dbo].[AutoInc] ( [Descriptor] ASC ) GO 

Answer 5

Assigning a domain value to a primary key is a practice that goes all the way to the point where Cobol programmers and dinosaurs walked the earth together. The practice has been preserved to this day most often in outdated inventory systems. This is basically a way to eliminate one or more columns of data and nesting the data from the excluded column (s) in a PK value.

If you want to keep the client and the supplier in the same table, just do it and use an auto-incrementing integer PK and add a ContactType column or something similar that can contain the values ​​of "S" and "C" or something else, you do not need a compound primary key.

You can always combine these columns (PK and ContactType) in reports, for example. C12345, S20000 (casting integers to a string), if you want to exclude a column to save space (i.e., on a printed or displayed page), and everyone in your organization understands the agreement that the first character of the object identifier is for ContactType code .

This approach will use the auto-increment capabilities built into the database engine to simplify your PC and associated code at the data level and make your program and database more reliable.

Answer 6

Instead of doing "c0001, c0002" for customers and "s0001, s0002" for suppliers in the same table, follow these steps:

• Create one Auto-Increment " id " field of the " int (10) unsigned " data type.
• Create another " type " field for the data type " enum ('c', 's') " (where c = Customer, s = Supplier).

As @PerformanceDBA noted, you can make the Primary Key Index for the two fields " id " and " type " so that your requirement is met using the correct methodology.

Answer 7

First, let's point out what you cannot do directly. If you try

 create table dbo.t1 ( id varchar(10) identity, ); 

The error message tells you which data types are supported directly.

Msg 2749, Level 16, State 2, Line 1 Die 'id'-Identitätsspalte muss vom Datentyp' int ',' bigint ',' smallint ',' tinyint 'oder' decimal 'bzw. 'numeric' mit 0 Dezimalstellen sein und darf keine NULL-Werte zulassen.

BTW: I tried to find this information in BOL or on MSDN and could not.

Now, knowing that you cannot do it the direct way, it is a good choice to execute the @marc_s sentence using computed columns.

Answer 8

 INSERT INTO Yourtable (yourvarcharID) values('yourvarcharPrefix'+( SELECT CAST((SELECT CAST(( SELECT Substring(( SELECT MAX(yourvarcharID) FROM [Yourtable ]),3,6)) AS int)+1) AS VARCHAR(20)))) 

Here the varchar column has the prefix "RX" and then 001 , so I selected substring after this prefix and increased only that number.

Answer 9

No. If you really need it, you will have to generate the identifier manually.