GTIN-13 Check Digits: Validate the Final Digit in a CHECK Constraint

GTIN-13 check digits can reject a mistyped final digit, but they cannot establish product identity. Validate the complete input before storing it. A constraint cannot inspect characters already discarded by an earlier conversion.

Calculate GTIN-13 check digits

A GTIN-13 string has twelve leading digits and one check digit. The calculation alternates weights of one and three across those twelve positions. Add the weighted digits, then choose the final digit that makes the total divisible by ten.

For 4006381333931, the first twelve weighted digits total 89. The final digit is one. The calculation produces a consistency check. It supplies no evidence that the identifier was issued for the product being entered.

Keep the input contract visible

The demonstration accepts exactly thirteen ASCII digits and preserves a leading zero. A wider Unicode column keeps the original characters available for validation. The exact character and byte-length checks sit beside the arithmetic. NULL has a separate NOT NULL restriction.

CREATE TABLE #Gtin
    (
        Code nvarchar(64) NOT NULL,
        WeightedSum AS
          TRY_CONVERT(int,SUBSTRING(Code,1,1))
        + TRY_CONVERT(int,SUBSTRING(Code,2,1))*3
        + TRY_CONVERT(int,SUBSTRING(Code,3,1))
        + TRY_CONVERT(int,SUBSTRING(Code,4,1))*3
        + TRY_CONVERT(int,SUBSTRING(Code,5,1))
        + TRY_CONVERT(int,SUBSTRING(Code,6,1))*3
        + TRY_CONVERT(int,SUBSTRING(Code,7,1))
        + TRY_CONVERT(int,SUBSTRING(Code,8,1))*3
        + TRY_CONVERT(int,SUBSTRING(Code,9,1))
        + TRY_CONVERT(int,SUBSTRING(Code,10,1))*3
        + TRY_CONVERT(int,SUBSTRING(Code,11,1))
        + TRY_CONVERT(int,SUBSTRING(Code,12,1))*3 PERSISTED,
        CHECK
        (
            DATALENGTH(Code) = 26
            AND Code COLLATE Latin1_General_100_BIN2 NOT LIKE '%[^0-9]%'
            AND TRY_CONVERT(int,RIGHT(Code,1)) = (10-(WeightedSum%10))%10
        )
    );

The computed weighted sum makes the arithmetic visible. Its definition uses PERSISTED because this computed column participates in the CHECK constraint. The CHECK compares its required final digit with the stored final digit. It also rejects the wrong length and nondigit characters.

A CHECK condition can accept UNKNOWN. This example combines the digit contract with NOT NULL rather than treating the checksum expression as a NULL guard. Keep these rules separate when adapting the example.

Read the complete outcomes

This block tries a valid code, a changed final digit and a leading-zero value. It also tries letters, short input, oversized input, a trailing blank, NULL and non-ASCII digits. Each expected failure must have the intended error. Successful inserts must preserve the complete intended string.

DECLARE @Cases TABLE (CaseId int NOT NULL, CaseName varchar(30) NOT NULL, Code nvarchar(64) NULL);
INSERT @Cases VALUES
 (1,'Known valid',N'4006381333931'),
 (2,'Changed final digit',N'4006381333932'),
 (3,'Leading zero',N'0012345678905'),
 (4,'Letter',N'40063813339A1'),
 (5,'Short',N'400638133393'),
 (6,'Oversize',N'40063813339310'),
 (7,'Trailing blank',N'4006381333931 '),
 (8,'NULL',NULL),
 (9,'Empty',N''),
 (10,'Non-ASCII digits',N'４００６３８１３３３９３１'),
 (11,'Adjacent swap detected',N'0406381333931'),
 (12,'Difference-five original',N'1600000000001'),
 (13,'Difference-five swapped',N'6100000000001');

DECLARE @Outcome TABLE (CaseId int NOT NULL, Accepted bit NOT NULL, ErrorNumber int NULL);
DECLARE @Id int = 1, @Code nvarchar(64);
WHILE @Id <= 13
BEGIN
    SELECT @Code = Code FROM @Cases WHERE CaseId = @Id;
    BEGIN TRY
        INSERT #Gtin(Code) VALUES (@Code);
        INSERT @Outcome VALUES (@Id, 1, NULL);
    END TRY
    BEGIN CATCH
        INSERT @Outcome VALUES (@Id, 0, ERROR_NUMBER());
    END CATCH;
    SET @Id += 1;
END;

SELECT c.CaseName AS [Case],
       CASE WHEN c.Code IS NULL THEN NULL ELSE '"' + c.Code + '"' END AS CompleteInput,
       CASE WHEN o.Accepted = 1 THEN 'True' ELSE 'False' END AS Accepted,
       o.ErrorNumber AS Error,
       CASE WHEN g.Code IS NULL THEN NULL ELSE '"' + g.Code + '"' END AS StoredCode,
       DATALENGTH(g.Code) AS StoredBytes,
       g.WeightedSum
FROM @Cases AS c
JOIN @Outcome AS o ON o.CaseId = c.CaseId
LEFT JOIN #Gtin AS g ON o.Accepted = 1 AND g.Code = c.Code
ORDER BY c.CaseId;

These are all thirteen measured outcomes. Quoted text makes empty strings and the trailing blank visible. NULL represents a missing value. Error 547 is the CHECK failure. Error 515 is the separate NOT NULL failure.

A checksum does not catch every swap

The two sample strings 1600000000001 and 6100000000001 both satisfy this calculation. Swapping adjacent digits differing by five can preserve the weighted total modulo ten. Another tested adjacent swap changes the check. Neither result proves that a sample string identifies an issued product.

The rule also cannot provide uniqueness. A separate key or unique constraint answers that question. GS1 assignment rules answer a different question again. Keep the checksum, identifier assignment and database uniqueness requirements visible as separate controls.

Validate before narrowing the input

The next block explicitly converts 40063813339310 to varchar(13) before insertion. That conversion discards the final extra character. The resulting thirteen-digit value passes the arithmetic. The original fourteen-character input was never evaluated by the constraint.

SELECT CONVERT(varchar(13),N'40063813339310') AS NarrowedInput;
INSERT #Gtin(Code) VALUES (CONVERT(varchar(13),N'40063813339310'));
DROP TABLE #Gtin;

Check application parameters and staging conversions alongside the table definition. Preserve the complete input until its contract has been validated. Keep identifier text as text so leading zeros survive. A successful insert alone cannot prove that every original character reached the constraint.

Let the checksum catch most typing slips, and let other controls do the rest.

A check digit is not proof of identity, it is a test that catches many typing slips.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.