How To Restrict a Column To Only A Default Value

Question

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()

• ypercubeᵀᴹ: Why would you ever need that? Why have the column in the first place, if it's always going to have (and in all rows) the same value?
• ypercubeᵀᴹ: You can use a CHECK constraint but I'd think it's rather rare you'd need such a design.
• John L.: @TypoCubeTM I mean a column with a default value like NEWID()
• irimias: You can use check constraints to achieve that. You'll be able to make your own verification before altering data of your table. An example here.
• Mister Magoo: Don't forget that BCP and BULK INSERT by default disable triggers, so you are exposed that way. Have you considered using permissions to restrict access to the table and stored procedures to control what gets inserted?

Answer 1

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
*/

Answer 2

You could put a constraint on the table to prevent values other than that value from being inserted (example here uses 0)

CREATE TABLE dbo.ConstraintTest (C1 INT NOT NULL, C2 INT DEFAULT(0));
ALTER TABLE dbo.ConstraintTest ADD CONSTRAINT PK_ConstraintTest PRIMARY KEY (C1);
ALTER TABLE dbo.ConstraintTest ADD CONSTRAINT CHK_ConstraintTest_C2is0 CHECK (C2 = 0);
INSERT INTO dbo.ConstraintTest (C1) VALUES (1);
SELECT * FROM dbo.ConstraintTest;
INSERT INTO dbo.ConstraintTest (C1, C2) VALUES (2, 2);

Alternatively you could look at using a trigger to ensure that the value does not get set to something different.

• John L.: How would that work with a default value like NEWID()
• Nic: You would use an instead of trigger as demo'd in @laughing_vergil answer.