Sql-server – How To Restrict a Column To Only A Default Value

sql server

When I add a "default value" expression to a column, if I do not specify a value for that column during an insert, the column automatically gets its default value. But if a value is supplied during insert, that value is used. How do you restrict a column to only have a default value so that an insert statement cannot write an arbitrary value in that column?

EDIT:
The default value I have in mind is NEWID()

Best Answer

For this sort of specific requirement, I would use an INSTEAD OF trigger set on the table. Here's a simple example showing how this would work.

-- I chose this table structure (nullable elements) so no error would be thrown on insert, 
--  and because no values are expected from the user for these columns
CREATE TABLE Bob (
    dataElement varchar(200) not null,
    LastUpdate datetime,
    UnchangeableID UniqueIdentifier
    )
GO

CREATE TRIGGER tr_MakeItHard1 ON Bob
INSTEAD OF INSERT 
AS
BEGIN
    INSERT INTO Bob (DataElement, LastUpdate, UnchangeableID) 
    SELECT dataElement, GETDATE(), NewID()
    FROM inserted
END

GO
CREATE TRIGGER tr_MakeItHard2 on Bob
INSTEAD OF UPDATE
AS
BEGIN
    -- Here, you can either choose to allow manual updates of the LastUpdate value 
    -- from the insert, or only update the data here. I chose to only allow updates
    -- here.
    Update Bob
    SET dataElement = i.dataElement,
        LastUpdate = GetDate()
    FROM inserted i
END
GO

DECLARE @uid uniqueidentifier

SELECT @uid = NewID()

INSERT INTO BOB values ('Data Element Insert', '20100101', @uid)

SELECT b.*,
    @uid as Generated
FROM Bob b

DECLARE @uid uniqueidentifier
SELECT @uid = NewID()

UPDATE Bob
SET dataElement = 'Data Element Update',
    LastUpdate = '20120101',
    UnchangeableID = @uid

SELECT b.*,
    @uid as Generated
FROM Bob b

/*
Results of the queries -- the top row is from the INSERT, the row below is from the UPDATE

dataElement             LastUpdate              UnchangeableID                          Generated
Data Element Insert     2017-01-05 08:59:12.187 7829000B-5BC8-4488-A006-DCB2933C5C95    3670E5AA-FBD4-49C0-88CF-8E2DE9F89BFC
Data Element Update     2017-01-05 09:04:57.527 7829000B-5BC8-4488-A006-DCB2933C5C95    86E9617D-F238-4E46-BE8F-85A318853593

*/
Related Question