Auto-increment primary key in SQL tables
Although the following is not way to do it in GUI but you can get autoincrementing simply using the IDENTITY datatype(start, increment):
CREATE TABLE "dbo"."TableName"
(
id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
name varchar(20),
);
the insert statement should list all columns except the id column (it will be filled with autoincremented value):
INSERT INTO "dbo"."TableName" (name) VALUES ('alpha');
INSERT INTO "dbo"."TableName" (name) VALUES ('beta');
and the result of
SELECT id, name FROM "dbo"."TableName";
will be
id name
--------------------------
1 alpha
2 beta
for those who are having the issue of it still not letting you save once it is changed according to answer below, do the following:
tools -> options -> designers -> Table and Database Designers -> uncheck "prevent saving changes that require table re-creation" box -> OK
and try to save as it should work now
- Presumably you are in the design of the table. If not: right click the table name - "Design".
- Click the required column.
- In "Column properties" (at the bottom), scroll to the "Identity Specification" section, expand it, then toggle "(Is Identity)" to "Yes".
Right-click on the table in SSMS, 'Design' it, and click on the id column. In the properties, set the identity to be seeded @ e.g. 1 and to have increment of 1 - save and you're done.