sql-server: Primärschlüssel basierend auf Zähler und einem anderen Spaltenwert erzeugen

Ich schaffe eine Kundentabelle mit einer übergeordneten Tabelle, die Firma ist. Es wurde diktiert (chagrin), dass ich einen Primärschlüssel für die Kundentabelle erstellen soll, die eine Kombination aus der Firmen-ID ist, die eine vorhandene Varchar (4) Spalte in der Kundentabelle ist, zB customer.company

Der Rest des Varchar (9) Primärschlüssels ist ein null gepolsterter Zähler, der durch die Anzahl der Kunden innerhalb dieses Unternehmens erhöht wird.

ZB, wo Firma = MSFT und dies ist eine erste Einfügung eines MSFT-datasatzes: Die PK wird MSFT00001 auf nachfolgenden Inserts der PK wäre MSFT00001, MSFT00002 etc. Dann, wenn Firma = INTL und seine erste Aufzeichnung eingefügt wird, wäre der erste datasatz INTL00001

Ich begann mit einem anstelle von Trigger und ein udf, dass ich aus anderen Stackoverflow-Antworten erstellt.

ALTER FUNCTION [dbo].[GetNextID] ( @in varchar(9) ) RETURNS varchar(9) AS BEGIN DECLARE @prefix varchar(9); DECLARE @res varchar(9); DECLARE @pad varchar(9); DECLARE @num int; DECLARE @start int; if LEN(@in)<9 begin set @in = Left(@in + replicate('0',9) , 9) end SET @start = PATINDEX('%[0-9]%',@in); SET @prefix = LEFT(@in, @start - 1 ); declare @tmp int; set @tmp = len(@in) declare @tmpvarchar varchar(9); set @tmpvarchar = RIGHT( @in, LEN(@in) - @start + 1 ) SET @num = CAST( RIGHT( @in, LEN(@in) - @start + 1 ) AS int ) + 1 SET @pad = REPLICATE( '0', 9 - LEN(@prefix) - CEILING(LOG(@num)/LOG(10)) ); SET @res = @prefix + @pad + CAST( @num AS varchar); RETURN @res END 

Wie würde ich schreiben, anstatt Trigger, um die Werte einzufügen und diesen Primärschlüssel zu erhöhen. Oder soll ich es aufgeben und eine Rasenmacherei starten?

Sorry für diese tmpvarchar variablen SQL-server gab mir seltsame Ergebnisse ohne es.

Während ich mit den naysayers einverstanden bin, neigt das Prinzip, "das zu akzeptieren, das nicht geändert werden kann", das gesamte Stressniveau, IMHO, zu senken. Versuchen Sie den folgenden Ansatz.

Nachteile

  • Einreihige Einsätze nur. Sie werden keine Bulk-Inserts für Ihre neue Kundentabelle machen, da Sie die gespeicherte Prozedur jedes Mal ausführen müssen, wenn Sie eine Zeile insert möchten.
  • Ein gewisses Maß an Konkurrenz für die Schlüsselerzeugungstabelle, also ein Potenzial für die Blockierung.

Auf der Oberseite, aber dieser Ansatz hat keine Rennbedingungen, die mit ihm verbunden sind, und es ist nicht zu ungeheuerlich ein Hack, um wirklich und wirklich meine Empfindungen zu beleidigen. Damit…

Zuerst starten Sie mit einer Schlüsselgenerierungstabelle. Es wird 1 Zeile für jede Firma enthalten, die Ihren Firmenbezeichner und einen Integer-Zähler enthält, auf den wir jedes Mal, wenn ein Insert durchgeführt wird, stoßen werden.

 create table dbo.CustomerNumberGenerator ( company varchar(8) not null , curr_value int not null default(1) , constraint CustomerNumberGenerator_PK primary key clustered ( company ) , ) ( create table dbo.CustomerNumberGenerator ( company varchar(8) not null , curr_value int not null default(1) , constraint CustomerNumberGenerator_PK primary key clustered ( company ) , ) 

Zweitens brauchst du eine gespeicherte Prozedur wie diese (in der Tat, vielleicht möchten Sie diese Logik in die gespeicherte Prozedur integrieren, die für das Einfügen des Kundenrekordes verantwortlich ist. Mehr dazu in ein wenig). Diese gespeicherte Prozedur akzeptiert eine Firmenkennung (zB 'MSFT') als einziges Argument. Diese gespeicherte Prozedur macht folgendes:

  • Setzt die Firmen-ID in kanonische Form (z. B. Großbuchstaben und beschnitten von führenden / schleppenden Whitespace).
  • Fügt die Zeile in die Schlüsselgenerierungstabelle ein, wenn sie noch nicht existiert (atomare Operation).
  • In einer einzigen atomaren Operation (Update-statement) wird der aktuelle Wert des Zählers für das angegebene Unternehmen abgerufen und dann erhöht.
  • Die Kundennummer wird dann in der angegebenen Weise generiert und an den Anrufer über eine 1-Zeilen / 1-Spalte SELECT statement zurückgegeben.

Bitte schön:

 create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go - create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go - create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go - create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go - create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go ) create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go - create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go - create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go - create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go - create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go Rückgabe 0 create procedure dbo.GetNewCustomerNumber @company varchar(8) as set nocount on set ansi_nulls on set concat_null_yields_null on set xact_abort on declare @customer_number varchar(32) -- -- put the supplied key in canonical form -- set @company = ltrim(rtrim(upper(@company))) -- -- if the name isn't already defined in the table, define it. -- insert dbo.CustomerNumberGenerator ( company ) select id = @company where not exists ( select * from dbo.CustomerNumberGenerator where company = @company ) -- -- now, an interlocked update to get the current value and increment the table -- update CustomerNumberGenerator set @customer_number = company + right( '00000000' + convert(varchar,curr_value) , 8 ) , curr_value = curr_value + 1 where company = @company -- -- return the new unique value to the caller -- select customer_number = @customer_number return 0 go 

Der Grund, warum du das in die gespeicherte Prozedur integrieren möchtest, die eine Zeile in die Kundentabelle einfügt, ist, dass sie das Zusammenwachsen alles zu einer einzigen Transaktion macht. ohne dass, Ihre Kundennummern können / werden Lücken bekommen, wenn ein Insert scheitert Land wird zurückgerollt.

Wie andere vor mir sagten, mit einem Primärschlüssel mit berechneten Auto-Inkrement-Werten klingt wie eine sehr schlechte Idee!

Wenn du es erlaubt bist und wenn du mit den Downsides leben kannst (siehe unten), würde ich folgendes vorschlagen:

Verwenden Sie eine normale numerische Auto-Inkrement-button und eine char (4) Spalte, die nur die Firmen-ID enthält.
Dann, wenn du aus der Tabelle auswählst, benutzt du row_number auf der Spalte Auto-Inkrement und kombinierst das mit der Firmen-ID, so dass du eine zusätzliche Spalte mit einem "Schlüssel" hast, der wie Sie wollte (MSFT00001, MSFT00002, … )

Beispieldaten:

 create table customers ( Id int identity(1,1) not null, Company char(4) not null, CustomerName varchar(50) not null ) insert into customers (Company, CustomerName) values ('MSFT','First MSFT customer') insert into customers (Company, CustomerName) values ('MSFT','Second MSFT customer') insert into customers (Company, CustomerName) values ('ABCD','First ABCD customer') insert into customers (Company, CustomerName) values ('MSFT','Third MSFT customer') insert into customers (Company, CustomerName) values ('ABCD','Second ABCD customer') ( create table customers ( Id int identity(1,1) not null, Company char(4) not null, CustomerName varchar(50) not null ) insert into customers (Company, CustomerName) values ('MSFT','First MSFT customer') insert into customers (Company, CustomerName) values ('MSFT','Second MSFT customer') insert into customers (Company, CustomerName) values ('ABCD','First ABCD customer') insert into customers (Company, CustomerName) values ('MSFT','Third MSFT customer') insert into customers (Company, CustomerName) values ('ABCD','Second ABCD customer') ) create table customers ( Id int identity(1,1) not null, Company char(4) not null, CustomerName varchar(50) not null ) insert into customers (Company, CustomerName) values ('MSFT','First MSFT customer') insert into customers (Company, CustomerName) values ('MSFT','Second MSFT customer') insert into customers (Company, CustomerName) values ('ABCD','First ABCD customer') insert into customers (Company, CustomerName) values ('MSFT','Third MSFT customer') insert into customers (Company, CustomerName) values ('ABCD','Second ABCD customer') 

Dies wird eine Tabelle erstellen, die so aussieht:

 Id Company CustomerName ------------------------------------ 1 MSFT First MSFT customer 2 MSFT Second MSFT customer 3 ABCD First ABCD customer 4 MSFT Third MSFT customer 5 ABCD Second ABCD customer ------------------------------------ Id Company CustomerName ------------------------------------ 1 MSFT First MSFT customer 2 MSFT Second MSFT customer 3 ABCD First ABCD customer 4 MSFT Third MSFT customer 5 ABCD Second ABCD customer 

Führen Sie nun die folgende Abfrage aus:

 select Company + right('00000' + cast(ROW_NUMBER() over (partition by Company order by Id) as varchar(5)),5) as SpecialKey, * from customers * select Company + right('00000' + cast(ROW_NUMBER() over (partition by Company order by Id) as varchar(5)),5) as SpecialKey, * from customers 

Das gibt die gleiche Tabelle zurück, aber mit einer zusätzlichen Spalte mit deinem "Sonderschlüssel":

 SpecialKey Id Company CustomerName --------------------------------------------- ABCD00001 3 ABCD First ABCD customer ABCD00002 5 ABCD Second ABCD customer MSFT00001 1 MSFT First MSFT customer MSFT00002 2 MSFT Second MSFT customer MSFT00003 4 MSFT Third MSFT customer 

Sie können eine view mit dieser Abfrage erstellen und lassen Sie alle diese view verwenden, um sicherzustellen, dass jeder die Spalte "Sonderschlüssel" sieht.

Allerdings hat diese Lösung zwei Nachteile:

  1. Sie benötigen mindestens SQL server 2005, damit row_number funktioniert.
  2. Die Zahlen in der Sondertaste ändern sich, wenn Sie Firmen aus der Tabelle löschen. Also, wenn du nicht willst, dass sich die Zahlen ändern, muss man sicherstellen, dass aus dieser Tabelle immer nichts gelöscht wird.