Creating table with Primary key ID column with auto increment

Asked

Viewed 3,207 times

0

I am using the SQL Server Management and I’m trying to create a table that has the ID which must be auto-incremented. However, I can’t use the property IDENTITY, and do everything in script, on the same hand.

I wonder how I can do this, since (for example) it does not work like this (as it is for Mysql):

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);
sql-server

  • I don’t get it, you want him to be Identity or want to create something to add value according to a special rule?

  • I want to make the property Identity "by hand", without using the property itself, and rather program what it does. Would I have to use create table and alter table, maybe? I’m not sure because I’m a database beginner

  • Without using the autoincrement provided by the database you would need to control this ID creation within your application.

  • @Marcellalves not necessarily, you can do this via bank tb

  • @Jefersonalmeida, how could he do via bank? Post an answer with this solution, because it is what the user is needing.

2 answers

3


One solution to this problem is to create a Function that will return you the next value of your PK, follow an example below as if it were a Identity adding the value of 1 in 1

Table

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Function to generate your customized Identity

CREATE FUNCTION dbo.ProximoIdPersons() 
RETURNS int 
AS 
BEGIN 
    DECLARE @lastval int 
    SET @lastval = (select max(ID) from Persons) 
    IF @lastval is null SET @lastval = 0
    return @lastval + 1
END

Insert that you would run

INSERT INTO Persons (ID, LastName, FirstName, Age)
VALUES (dbo.ProximoIdPersons(),'Almeida', 'Jeferson', 27)

  • In the case of this "Create Function" I create in another query?

  • 1

    @Bellon can be in another query, that you will only run once, it will create a function in your database and you will then be able to use this function

  • Ah, got it! Thank you very much, I’ll test

  • ID and Persons is not being recognized, which may be?

  • @Bellon in which passage he is not being recognized, you created the table before trying to create the function and do the Insert?

1

The creation script for SQL Server would look like this:

CREATE TABLE dbo.Persons
    (
    ID int NOT NULL IDENTITY (1, 1),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NULL,
    Age int NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Persons ADD CONSTRAINT
    PK_Persons PRIMARY KEY CLUSTERED 
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
ALTER TABLE dbo.Persons SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
  • 1

    Is that the goal is to make the property Identity "by hand".

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.