i am trying to add a constraint to a datatype of char(1)
i would like the user to only be able to enter Y or N or n or Y
i clicked on check constraint in the CHECK CONSTRAINT EXPRESSION window what am i supposed to enter?
views:
23answers:
3
+2
A:
It is always better to make such changes manually, not via GUI.
ALTER TABLE YourTable
ADD CONSTRAINT CHK_YourTable_YourColumn_YesOrNo CHECK(YourCOlumn IN ('Y', 'N'))
Edit: GUI can issue suboptimal DDL, and with little practice you can be just as efficient with manual scripts as with GUI, and you know exactly what is happening. Also you really want to store all your DDL in version control, including the script for changes.
AlexKuznetsov
2010-10-08 17:57:51
@alex:sashok, pochemu? why is manually better?
i am a girl
2010-10-08 17:58:34
@jenny: Because you can't guarantee you'll have GUI access, and operations are typically specific to the tool (IE: can't use the same process in TOAD for SQL Server, etc)
OMG Ponies
2010-10-08 17:59:49
+1, `why is manually better?`, SSMS quite often writes a massive script that generates a complete duplicate new table (with the new change) copies all the data in to it, then drops the original table and renames the new table to the original table name, also it will drop and add all the constraints and indexes, etc. It is much easier, faster, puts less strain on the database, and is more reliable to learn and use the much simpler ALTER TABLE command.
KM
2010-10-08 18:22:56
+1
A:
Using an ALTER TABLE statement:
ALTER TABLE dbo.YOUR_TABLE
ADD CONSTRAINT bool_check CHECK (LOWER(your_column) IN ('n', 'y')) ;
OMG Ponies
2010-10-08 17:58:55
A:
alter table TableName
add constraint CHK_TableName_ColumnName check (ColumnName in ('Y','N','y','n'))
ulty4life
2010-10-08 18:06:37