SQL Server INSERT INTO auto increment identity for a one-column table [duplicate]

rryofs0p 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(116)

This question already has answers here:

How do I insert a new record to a table containing just an IDENTITY column? (1 answer)
Insert multiple rows of default values into a table (2 answers)

Closed 17 days ago.

I have a table Person.

Id, Name, Phone
-------------------
1,  NULL, 123456789

This Person.Id is an auto increment identity. After a while, I found Name is useless for the current business and it always is NULL. So I deleted this column.

Later, I found one person can have multiple phone numbers. And since SQL Server doesn't support array type, I had to move this Phone to a separated table. So, there is just one column Id left.

Is a one column table good design?

I have enough reason keep this one-column table, because the Id is important for other tables (link).

But there is a problem for the INSERT query.

INSERT INTO Person(Id) OUTPUT Inserted.Id VALUES(DEFAULT)

The value either DEFAULT or NULL wouldn't work with the error message:

DEFAULT or NULL are not allowed as explicit identity values.

Leave the parentheses empty also doesn't work.

sql-server

来源：https://stackoverflow.com/questions/76693283/insert-into-auto-increment-identity-for-a-one-column-table

1条答案

按热度按时间

093gszye1#

Instead of VALUES(DEFAULT) , omit the column name and specify DEFAULT VALUES to assign defaults for all columns (only column in this case). Example with the OUTPUT clause:

CREATE TABLE dbo.Person(
    ID int NOT NULL IDENTITY CONSTRAINT PK_Person PRIMARY KEY
);
INSERT INTO Person OUTPUT Inserted.Id DEFAULT VALUES;

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server INSERT INTO auto increment identity for a one-column table [duplicate]

1条答案

相关问题

热门标签

最新问答