CODEWITHSUNDEEP
sql-serverconstraints
Lenny
Lenny

Reputation: 917

column with specified length value

I want to create a column with SQL Server which would accept values with exact 6 digits. Values with more or less number of digits would be not accepted.

Upvotes: 1

Views: 47

Answers (3)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

You could use a simple CHECK constraint and check column length. This was already told, but solution wasn't posted, so here I am:

CREATE TABLE dbo.Test
(
    YourSixDigitColumn INT
    , CONSTRAINT CK_CheckLength CHECK (LEN(YourSixDigitColumn) = 6)
);

If I'd try to insert one of these:

INSERT INTO dbo.Test
VALUES (12345);

INSERT INTO dbo.Test
VALUES (1234567);

It would throw me this nasty error message:

Msg 547, Level 16, State 0, Line 7 The INSERT statement conflicted with the CHECK constraint "CK_CheckLength". The conflict occurred in database "master", table "dbo.Test", column 'YourSixDigitColumn'. The statement has been terminated.

And this SQL Statement works flawlessly:

INSERT INTO dbo.Test
VALUES (123456);

Upvotes: 3

David Rushton
David Rushton

Reputation: 5030

You can use SQL Server's Check Constraints for this. These are a great way of limiting the allowed values within a column.

If your six digits are always a whole number your rule would look this:

ColumnA >= 100000 AND ColumnA <= 999999.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

Use either char(6) or Decimal(6,0).

But to enforce that it can't be less than 6 digits, you'll need to use a check constraint.

Upvotes: 1

Related Questions