How to concat a string with an ID in SQL Server?

Question

This is my T-SQL code.

CREATE TABLE [dbo].[AspNetUsers] 
(
    [Id]            NVARCHAR (128) NOT NULL,
    [Email]         NVARCHAR (256) NULL,
    [PasswordHash]  NVARCHAR (MAX) NULL,
    [UserName]      NVARCHAR (256) NOT NULL,
    [FirstName]     NVARCHAR(50) NOT NULL DEFAULT /*what goes in here*/, 
    [LastName]      NVARCHAR(50) NOT NULL DEFAULT 'LastName', 

    CONSTRAINT [PK_dbo.AspNetUsers] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO

CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
    ON [dbo].[AspNetUsers]([UserName] ASC);

I want FirstName and LastName to be not allowed as nulls, but I keep getting an error saying that I should either add defaults or either I should allow them to have nulls (which I don't want to do at all). So I thought I would add defaults. I want to take the Id and concat it with the word FirstName and LastName for the respective columns.

For example if my Id is 345, I want my defaults for FirstName and LastName to be (of nvarchar type) 345FirstName and 345LastName respectively.

So how do I do this?

Answer 1

DEFAULT constraint must be constant, you can't use column names in DEFAULT definition context. I think the only way is to use triggers (I'm not sure if this is not overengineering). Take a look at this example:

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                   NVARCHAR (128) NOT NULL,
    [Email]                NVARCHAR (256) NULL,
    [PasswordHash]         NVARCHAR (MAX) NULL,
    [UserName]             NVARCHAR (256) NOT NULL,
    [FirstName] NVARCHAR(50) NOT NULL, 
    [LastName] NVARCHAR(50) NOT NULL, 
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE TRIGGER trInsert_AspNetUsers
ON [dbo].[AspNetUsers] INSTEAD OF INSERT
AS
  INSERT [dbo].[AspNetUsers]([Id], [Email], [PasswordHash], [UserName], [FirstName], [LastName])
  SELECT [Id], [Email], [PasswordHash], [UserName], COALESCE([FirstName], [Id]+'FirstName'), COALESCE([LastName], [Id]+'LastName')
  FROM inserted
GO

INSERT dbo.AspNetUsers VALUES('1', '2', '3','4',NULL,NULL)
INSERT dbo.AspNetUsers VALUES('5', '6', '7', '8', 'Paweł', 'Dyl')
SELECT * FROM dbo.AspNetUsers

SELECT returns following table:

Id  Email   PasswordHash UserName FirstName     LastName
1   2       3            4        1FirstName    1LastName
5   6       7            8        Paweł         Dyl

Answer 2

You can make this fields nullable and add after update, insert trigger:

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                   NVARCHAR (128) NOT NULL,
    [Email]                NVARCHAR (256) NULL,
    [PasswordHash]         NVARCHAR (MAX) NULL,
    [UserName]             NVARCHAR (256) NOT NULL,
    [FirstName] NVARCHAR(50) NULL, 
    [LastName] NVARCHAR(50) NULL, 
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
    ON [dbo].[AspNetUsers]([UserName] ASC);
GO

CREATE TRIGGER [UserNameTrigger]
ON [dbo].[AspNetUsers]
AFTER INSERT, UPDATE
AS 
    UPDATE anu
    SET [FirstName] = ISNULL(i.[FirstName],i.Id + 'FirstName'), 
        [LastName] = ISNULL(i.[LastName],i.Id + 'LastName')
    FROM [dbo].[AspNetUsers] anu
    INNER JOIN inserted i
        ON i.[Id] = anu.Id
GO

So if you run this query:

INSERT INTO [dbo].[AspNetUsers] 
VALUES
(NEWID(),'example1@example.com','qwerty','username1',NULL,NULL),
(NEWID(),'example2@example.com','qwerty','username2','John',NULL),
(NEWID(),'example3@example.com','qwerty','username3',NULL,'Smith'),
(NEWID(),'example4@example.com','qwerty','username4','Jason','Anderson')
GO

SELECT [Id]
      ,[Email]
      ,[PasswordHash]
      ,[UserName]
      ,[FirstName]
      ,[LastName]
  FROM [dbo].[AspNetUsers]
GO

You will get:

Id                                      Email                   PasswordHash    UserName    FirstName                                       LastName
07AA1C80-7386-4E48-9556-9D81E2FBFCB0    example2@example.com    qwerty          username2   John                                            07AA1C80-7386-4E48-9556-9D81E2FBFCB0LastName
6310E4E6-ADDB-4F50-9D1E-5C756059DB40    example1@example.com    qwerty          username1   6310E4E6-ADDB-4F50-9D1E-5C756059DB40FirstName   6310E4E6-ADDB-4F50-9D1E-5C756059DB40LastName
6F539B99-2B4C-440E-A96A-8F5D54A9B758    example3@example.com    qwerty          username3   6F539B99-2B4C-440E-A96A-8F5D54A9B758FirstName   Smith
9665747B-A216-46CC-9BA3-159512AAC80C    example4@example.com    qwerty          username4   Jason                                           Anderson