0

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

  • 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ᵀᴹ Jan 5 '17 at 16:33
  • You can use a CHECK constraint but I'd think it's rather rare you'd need such a design. – ypercubeᵀᴹ Jan 5 '17 at 16:35
  • @TypoCubeᵀᴹ I mean a column with a default value like NEWID() – John L. Jan 5 '17 at 16:36
  • 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. – irimias Jan 5 '17 at 16:38
  • 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? – Mister Magoo Jan 6 '17 at 0:11
2

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

*/
0

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.