July 20, 2016 at 6:23 am
I am looking for the ALTER TABLE .....?
Where I can limit values of a column CHAR(1) to only accept the following values ( 'Y' , 'N' , NULL )
July 20, 2016 at 6:39 am
You can do it using CHECK Constraint.
July 20, 2016 at 6:40 am
Try this syntax:
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
[WITH VALUES]
July 20, 2016 at 6:50 am
The basic syntax is this -
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (column_name condition);
Example -
ALTER TABLE employees
ADD CONSTRAINT check_value
CHECK (fieldname IN ('Y', 'N', NULL));
Example-
ALTER TABLE employees
ADD CONSTRAINT check_length
CHECK (LEN(fieldname) < 1)
GO
You can probably combine the constraints into one if you like. Hope this helps....
July 20, 2016 at 6:54 am
July 20, 2016 at 7:10 am
Luis Cazares (7/20/2016)
Ever thought about using a bit column?
+1
You won't have to worry about constraints with a bit. You can map it to whatever you want on the front end.
July 20, 2016 at 7:13 am
RVarn (7/20/2016)
The basic syntax is this -ALTER TABLE table_name
ADD CONSTRAINT constraint_name
CHECK (column_name condition);
Example -
ALTER TABLE employees
ADD CONSTRAINT check_value
CHECK (fieldname IN ('Y', 'N', NULL));
Example-
ALTER TABLE employees
ADD CONSTRAINT check_length
CHECK (LEN(fieldname) < 1)
GO
You can probably combine the constraints into one if you like. Hope this helps....
Length should be restricted by the data type. You wouldn't have a char(100) with a constraint to allow only 7 characters. Also, the length check you included will only allow empty values as you missed the equal sign. There's also no need for it, as you're only allowing 3 possibilities which already comply with the length check.
On the check value constraint, you don't need to include NULL as NULL values are not affected by CHECK constraints.
CREATE TABLE CheckTest(
fieldname char(1));
ALTER TABLE CheckTest
ADD CONSTRAINT check_value
CHECK (fieldname IN ('Y', 'N'));
GO
INSERT INTO CheckTest VALUES( 'Y');
GO
INSERT INTO CheckTest VALUES( 'N');
GO
INSERT INTO CheckTest VALUES( 'S');
GO
INSERT INTO CheckTest VALUES( '');
GO
INSERT INTO CheckTest VALUES( NULL);
GO
SELECT * FROM CheckTest;
GO
DROP TABLE CheckTest;
July 20, 2016 at 7:17 am
ALTER TABLE employees
ADD CONSTRAINT check_length
CHECK (LEN(fieldname) < 1)
Should be
ALTER TABLE employees
ADD CONSTRAINT check_length
CHECK (LEN(fieldname) = 1)
But this could be handled by using a nullable char(1) data type:
ALTER TABLE employees
ALTER COLUMN fieldname char(1) NULL
All that said, a bit column as Luis suggested would certainly be the way to go. 😉
-- Itzik Ben-Gan 2001
July 20, 2016 at 8:11 am
To: RVARN:
Example -
ALTER TABLE employees
ADD CONSTRAINT check_value
CHECK (fieldname IN ('Y', 'N', NULL));
--The above does not work. It allows any value. The trick is you need to take that NULL out. See the example provided by Luis C. That works fine.
July 20, 2016 at 8:18 am
Thanks for the clarification, I should have tested it prior 😉
July 20, 2016 at 8:20 am
mw112009 (7/20/2016)
To: RVARN:Example -
ALTER TABLE employees
ADD CONSTRAINT check_value
CHECK (fieldname IN ('Y', 'N', NULL));
--The above does not work. It allows any value. The trick is you need to take that NULL out. See the example provided by Luis C. That works fine.
And that's because a CHECK constraint doesn't require the condition to return true, it just prevents values that would return false. That's the way they allow the handling of NULL values which would return unknown most of the times.
July 21, 2016 at 12:06 pm
The bit and varying bit datatypes were deprecated in SQL: 2003 for good reasons. You can read some of this: https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/.
In the case of SQL Server. It is even weirder. Originally BIT was a data type with two values, {0, 1} but later Microsoft made it a numeric data type. All numeric datatypes in SQL can have a null! This messed up a lot of code. When people try to ported over to that next release.
SQL is what I would call a "predicate language"; instead of looking for assembly language style flags in the data like we did in the 1960's, we use predicates to determine the current state of the data.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
July 21, 2016 at 12:23 pm
CELKO (7/21/2016)
SQL is what I would call a "predicate language"; instead of looking for assembly language style flags in the data like we did in the 1960's, we use predicates to determine the current state of the data.
There's no indication here to know if the column is a flag or an attribute. I'd feel more inclined to say that it's an attribute as it allows NULLs.
July 21, 2016 at 1:02 pm
CELKO (7/21/2016)
The bit and varying bit datatypes were deprecated in SQL: 2003 for good reasons. You can read some of this: https://www.simple-talk.com/sql/t-sql-programming/bit-of-a-problem/.
Yes, but the SQL standards also have a BOOLEAN data type, which T-SQL has not yet implemented. Until such time as it is implemented, BIT is a reasonable alternative.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply