CODEWITHSUNDEEP
sql-server
BakingCake
BakingCake

Reputation: 353

Is UNIQUEIDENTIFIER an auto-generated number when inserting values in a table?

I have an error when loading a procedure telling me

Cannot insert the value NULL into column 'requestID', table 'MCAST.a01.tbl_enrollmentRequests'; column does not allow nulls. INSERT fails.

Now requestID is a UNIQUEIDENTIFIER type of variable. Is UNIQUEIDENTIFIER an auto generated number or not? Below is a sample of my code where you can see requestID. 

CREATE PROCEDURE [a01].[usp_auditAcceptRequest]
    (@AccountID UNIQUEIDENTIFIER, 
     @GroupID UNIQUEIDENTIFIER, 
     @Reason NVARCHAR(45)
    )
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [a01].[tbl_enrollmentRequests] (requestDate, groupID, accountID)
    VALUES (SYSDATETIMEOFFSET(), @GroupID, @AccountID)

    DECLARE @RequestID UNIQUEIDENTIFIER 

    SET @RequestID = (SELECT requestID 
                      FROM [a01].tbl_enrollmentRequests 
                      WHERE groupID = @GroupID AND accountID = @AccountID)

    INSERT INTO [a01].[tbl_enrollmentAudits] (entryDate, requestID, groupID, accountID, accepted, reason)
    VALUES (SYSDATETIMEOFFSET(), @RequestID, @GroupID, @AccountID, 1, @Reason)

    DELETE FROM [a01].[tbl_enrollmentRequests]
    WHERE requestID = @RequestID
END;
GO

Here is where I am implementing the above procedure

BEGIN
DECLARE @AccountID UNIQUEIDENTIFIER;
DECLARE @GroupID UNIQUEIDENTIFIER;

(SELECT @AccountID = accountID 
FROM [a01].[tbl_userAccounts] WHERE accountUsername='saraht');

(SELECT @GroupID = groupID FROM [a01].[tbl_groups] WHERE groupName LIKE '%Foo%');

EXECUTE [a01].[usp_addRequest] @AccountID, @GroupID;
END;
GO

Thanks for your help !!

Upvotes: 21

Views: 44472

Answers (2)

Lucero
Lucero

Reputation: 60190

A uniqueidentifier is a normal column, and if you want to have a automatically assigned value you need to add a default to the column. Typically the functions used for the default are newid() or newsequentialid().

Edit based on the posted table definition; you could use this:

CREATE TABLE [a01].[tbl_enrollmentRequests](
  requestID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (NEWID()), 
  requestDate DATETIMEOFFSET NOT NULL, 
  groupID UNIQUEIDENTIFIER REFERENCES [a01].[tbl_groups] (groupID) NOT NULL, 
  accountID UNIQUEIDENTIFIER REFERENCES [a01].[tbl_userAccounts] (accountID) NOT NULL
);

That being said, you can also pre-generate a uniqueidentifier and assign that to a variable in the stored procedure prior to insertion, since the generated GUID can be assumed not to collide with any existing GUID. The benefit of this is that you know the id of the inserted row even without retrieving it from an OUTPUT clause.

A notice on performance: a significant number of rows with a clustered primary key of random GUIDs (as generated bynewid()) are a performance issue, since the inserts will cause many page splits to occur due to the randomness. The newsequentialid() function pretty much completely resolves the performance problem, but it makes the generated GUIDs guessable, so that this can only be used when "random" IDs are not required.

Upvotes: 38

TomTom
TomTom

Reputation: 62093

Is UNIQUEIDENTIFIER an auto generated number or not?

What do you ask us? You have a look at the table definition and see whether a default that sets a new uniqueidentifier is defined or not.

If it is not - then no.

If you try to insert null, then also not (as your insert overrides the default value).

---Edit:

As per the table definition you posted:

requestID UNIQUEIDENTIFIER PRIMARY KEY

no default value defined that sets it. So no.

Upvotes: -9

Related Questions