CODEWITHSUNDEEP
sqlsql-server
user1932119
user1932119

Reputation: 35

Alpha Numeric Primary

I was planning to have a primary key with 'LIBBOOKS-1214' but I cant do it. I want it whenever I will input an another info it will generate that as my primary key.

Here is my code:

CREATE TABLE tblBookInfo
(
AccessionNumber NVARCHAR(MAX)
DECLARE @RandomNumber NVARCHAR(10);
DECLARE @AccNo NVARCHAR(MAX)
DECLARE @Upper INT;
DECLARE @Lower INT

SET @Lower = 1
SET @Upper = 9999

Select @RandomNumber = Round (((@Upper - @Lower -1)* RAND() + @Lower), 0)

SET @AccNo = 'LIBBOOKS' + @RandomNumber
SELECT @AccNo

Set AccessionNumber = @AccNo
SELECT AccessionNumber
)

Thank you for your help! Much appreciated. I'm still a newbie in SQL-Server.

Upvotes: 1

Views: 2370

Answers (3)

marc_s
marc_s

Reputation: 754538

The easiest way to do this in SQL Server would be to have:

  • a column if type INT IDENTITY which automagically handles the numeric part of your identifier by producing sequential numbers when rows are inserted into your table

    CREATE TABLE dbo.tblBookInfo
 ( ID INT IDENTITY(1,1) NOT NULL,
    .... other columns here.....
 )

  • a computed, persisted column that combines that alphanumeric prefix and the identity column - something like

    ALTER TABLE dbo.tblBookInfo
ADD AlphaNumID AS 'LIBBOOKS-' + CAST(ID AS VARCHAR(5)) PERSISTED

It's up to you whether you define the primary key to be on the ID column (that would be my preference - there's really no need to include that prefix into every index entry and store it many times over) or whether you really must have it on the AlphaNumID column. To speed searches up, you can always put a separate nonclustered index on AlphaNumID , too.

Upvotes: 1

Karl
Karl

Reputation: 3372

One option would be to use a concatenated primary key consisting of 2 columns, one will hold the Alpha string the other the numeric. Then your numeric column could use an Identify field as proposed by MarkD.

Another option is to use a varchar field and write a function that determines the numeric value for the alpha-numeric string. E.g. you pass into the function 'LIBBOOKS' and it returns a string 'LIBBOOKS- then you insert this. You could do this in a trigger that fires on each insert.

In you psudoe code you mix the create table and the start of a function to create a alpha-numeric string. If you go done this path create you table as normal and then write a peice of code to create the primary key. Finally puyt it together, e.g. create a trigger to call the code to create the primary key or do this in you application, etc depending on your design.

If you are going to use a random value remember to check if for uniqueness before attempting to insert with it.

Upvotes: 0

MarkD
MarkD

Reputation: 5316

If you want to build a table that generates it's own PRIMARY KEYS, you need to do something like this:

USE tempdb
GO

IF OBJECT_ID('tempdb.dbo.MyTable') IS NOT NULL DROP TABLE MyTable

CREATE TABLE MyTable
(
    BookID  INT IDENTITY(1,1) NOT NULL,
    Title   VARCHAR(50) NULL,
    CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED 
    (
        BookID ASC
    )
) ON [PRIMARY]

INSERT INTO MyTable (Title) VALUES
('SQL Syntax Explained'),
('Sensible Posting explained')

SELECT *
FROM MyTable

It should be noted that a CLUSTERED INDEX will be built on the PRIMARY KEY as is the default behaviour in SQL Server when working through an create table SSMS GUI. It is NOT mandatory.

It really is quite difficult to guess at what it is you're trying to achieve with your code. If you could explain it a little better, we could certainly help you.

As a footnote regarding PRIMARY KEYS, it is regarded by many as best practice to use an integer type number (TINYINT, SMALLINT, INT, BIGINT) as your PK. When you do this, you are said to be creating an Artificial Primary Key. In a nutshell, they are manageable values that INDEX well and perform well on JOINS of equality.

Upvotes: 0

Related Questions