Create database scripts where tables have prepopulated data across environment-CodePudding

I have a table named, '[UserTypes]' where Primary id is [uniqueidentifier]. and I have a reference in another table called '[Users]'. Users table has UserTypeId as foreignKey.

CREATE TABLE [UserTypes] (
    [Id] [uniqueidentifier] DEFAULT NEWID() PRIMARY KEY,
    [UserName] [varchar](100) NOT NULL,
    [UserCD] [varchar](40) NOT NULL
GO

INSERT INTO [dbo].[UserTypes]
           ([UserName], [UserCD])
     VALUES
           ('Administrator','ADMI'), 
           ('NonPrimary','NONP'),
GO

-- ID got generated in [UserTypes] is '80D1EEE7-0BCC-48A7-A741-29A1D8B6E580' for 'ADMI'

CREATE TABLE [Users] (
    [Id] [uniqueidentifier] DEFAULT NEWID() PRIMARY KEY,
    [UserTypeId] [uniqueidentifier] NOT NULL,
    [UserName] [varchar](100) NOT NULL, 
    CONSTRAINT Users_UserTypeId_UserType_Id FOREIGN KEY (UserTypeId)
    REFERENCES UserTypes(Id))
GO

INSERT INTO [dbo].[Users]
           ([UserTypeId], [UserName])
     VALUES
           ('80D1EEE7-0BCC-48A7-A741-29A1D8B6E580','Kushal Seth')
GO

This '80D1EEE7-0BCC-48A7-A741-29A1D8B6E580' is the userTypeId of 'ADMI' from the userType Table.

My problem is, Suppose, I need to run this script in a new DB, then my ID for 'ADMI' will be different in 'UserTypes' table. and the script will throw error while inserting into the 'Users' table.

One option I have is to declare the variable and select the ID from UserType Table and assign to this variable, and later use that in the insert query of [Users] table.

Is this the only approach? or is there a better way to design such tables. any Design suggestions would really be appreciated.

CodePudding user response：

Suppose, I need to run this script in a new DB, then my ID for 'ADMI' will be different in 'UserTypes' table.

So remove the Guid PK from UserTypes, and make 'UserCD' the primary key.

CREATE TABLE [UserTypes] 
(
    [UserCD] [varchar](40) NOT NULL PRIMARY KEY,
    [UserName] [varchar](100) NOT NULL
)

GO

INSERT INTO [dbo].[UserTypes]
           ([UserName], [UserCD])
     VALUES
           ('Administrator','ADMI'), 
           ('NonPrimary','NONP')

Which is better in every concevable way.

CodePudding user response：

Guids are very useful if you have distributed dataservers that must keep uniqueness even when they aren't connected permanently.

But you can do following, as you know the the uusercd is ADMI, you can catch the ID

CREATE TABLE [UserTypes] (
    [Id] [uniqueidentifier] DEFAULT NEWID() PRIMARY KEY,
    [UserName] [varchar](100) NOT NULL,
    [UserCD] [varchar](40) NOT NULL)

INSERT INTO [dbo].[UserTypes]
           ([UserName], [UserCD])
     VALUES
           ('Administrator','ADMI'), 
           ('NonPrimary','NONP')

GO

-- ID got generated in [UserTypes] is '80D1EEE7-0BCC-48A7-A741-29A1D8B6E580' for 'ADMI'

CREATE TABLE [Users] (
    [Id] [uniqueidentifier] DEFAULT NEWID() PRIMARY KEY,
    [UserTypeId] [uniqueidentifier] NOT NULL,
    [UserName] [varchar](100) NOT NULL, 
    CONSTRAINT Users_UserTypeId_UserType_Id FOREIGN KEY (UserTypeId)
    REFERENCES UserTypes(Id))

INSERT INTO [dbo].[Users]
           ([UserTypeId], [UserName])
     VALUES
           ((SELECT [Id] FROM [UserTypes] WHERE [UserCD] = 'ADMI'),'Kushal Seth')

SELECT * FROM Users
GO

Id                                   | UserTypeId                           | UserName   
:----------------------------------- | :----------------------------------- | :----------
53f2a6e0-af71-4dab-8a99-821510681a37 | 6acfc89f-f4e2-4ac1-9989-b3a9c062cf0a | Kushal Seth

db<>fiddle here