Have Geek Brain

Autoincrement in sql access not working

How to create a table with auto-incremental access. Here's what I do but don't work.

CREATE TABLE People_User_Master(  
    Id INTEGER primary key AUTOINCREMENT,
    Name varchar(50),
    LastName varchar(50), 
    Userid varchar(50) unique,
    Email varchar(50),
    Phone varchar(50),
    Pw varchar(50),
    fk_Group int,
    Address varchar(150)  
)
+2


a source to share


4 answers


Try adding a constraint at the end

CREATE TABLE People_User_Master(   
Id AUTOINCREMENT 
  , Name varchar(50)   
, LastName varchar(50)   
, Userid varchar(50) unique   
, Email varchar(50)   
, Phone varchar(50)   
, Pw varchar(50)   
, fk_Group int   
, Address varchar(150)
, CONSTRAINT id_pk PRIMARY KEY(Id)


)

Updated to match the actual answer (no definition INTEGER

in column AUTOINCREMENT

allowed). Remains PRIMARY KEY

on the same line as Id AUTOINCREMENT

.

+1


a source


Remove INTEGER (it conflicts with AUTOINCREMENT)

CREATE TABLE People_User_Master(  
Id AUTOINCREMENT primary key ,
Name varchar(50),
LastName varchar(50), 
Userid varchar(50) unique,
Email varchar(50),
Phone varchar(50),
Pw varchar(50),
fk_Group int,
Address varchar(150)


)

+2


a source


You can do this using IDENTITY (Jet4 + supported)

CREATE TABLE People_User_Master
(
ID IDENTITY (1, 1), 
Name ..

Failed,

ID AUTOINCREMENT,

Should work (note that you are not specifying the type)

+1


a source


It might work, but doesn't seem to work if it tries to insert an INSERT INTO with ID into the Column clause. This seems to override AUTOINCREMENT MS Access.

This is an attempt to insert a record with ID = 1 bypassing AUTOINCREMENT

INSERT INTO People_User_Master 
       (Id, Name, LastName, Userid, Email, Phone, Pw, fk_Group, Address)
VALUES (1, "John", "Smith", "JS100", "JS@MyMail.Net", 12345678, "****","","")
Run codeHide result


Omitting the identifier allows automatic AUTOINCREMENT.

INSERT INTO People_User_Master 
       (Name, LastName, Userid, Email, Phone, Pw, fk_Group, Address)
VALUES ("John", "Smith", "JS100", "JS@MyMail.Net", 12345678, "****","","")
Run codeHide result


0


a source






More articles:


All Articles