-1

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.

Zhang
  • 3,030
  • 2
  • 14
  • 31
  • A `Person` table without so much as a `Name` column seems a bit ....... useless to me. Even if you have your ID's in there - how can you keep apart person #317 and person #781 and know who those people are, if you don't even store as much as a name for them?!? – marc_s Jul 15 '23 at 10:38
  • 3
    If your `Person` table really consists of nothing but the `Id` identity column - you can insert values using `INSERT INTO dbo.Person DEFAULT VALUES;` – marc_s Jul 15 '23 at 10:40

1 Answers1

4

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;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71