asked
How to correct datatype in column
Ok, I am new to T-SQL. I declared a wrong datatype for a column which had a CHECK constraint. Now I am trying to correct the datatype using ALTER statement but it is giving an error as something like CHECK incorrectly declared. I cannot drop the table because other tables are referencing from this table. Please tell me how can I change the datatype. below is the table I created: **CREATE TABLE STAFF.STAFF_DETAILS ( STAFF_GENDER TINYINT CHECK (STAFF_GENDER = 'M' OR STAFF_GENDER = 'F') ) ON STUDENT_FG** Now if you notice the datatype is wrongly selected as TINYINT whereas it should have been CHAR. So now I using the below statement to correct the column datatype **ALTER TABLE STAFF.STAFF_DETAILS ALTER COLUMN STAFF_GENDER CHAR CHECK(STAFF_GENDER = 'M' OR STAFF_GENDER = 'F')..** but it gives me an error. Please help.
Oh, and you might want to read "Falsehoods programmers believe about genders" -
http://www.cscyphers.com/blog/2012/06/28/falsehoods-programmers-believe-about-gender/. That's currently unavailable, but the google cache copy is at
http://webcache.googleusercontent.com/search?q=cache:R0aQjvHjSyoJ:www.cscyphers.com/blog/2012/06/28/falsehoods-programmers-believe-about-gender/+&cd=1&hl=en&ct=clnk&gl=us
In the middle of something, so I'll just say `sp_rename` the old column, add the real column, and delete the old column.
https://msdn.microsoft.com/en-us/library/ms188351.aspx
The problem is you can't alter a column in use by a constraint, so you'll need to drop the constraint first. This is easier if you use named constraints (which I'll show later), but you can still do it. I also recommend explicitly indicating your column width, so use "char(1)" instead of just "char". First, try altering the column. ALTER TABLE staff.staff_details ALTER COLUMN staff_gender char(1); That will return an error giving you the name of the constraint. Now, drop the constraint. ALTER TABLE staff.staff_details DROP CONSTRAINT {the name from the error}; Next, alter the column. ALTER TABLE staff.staff_details ALTER COLUMN staff_gender char(1); Finally, add the constraint (with a name). ALTER TABLE staff.staff_details ADD CONSTRAINT gender_CK CHECK (staff_gender IN ('M', 'F')) ;
Thanks, Asim. I'm always happy to help. Funny you should ask because I just started a blog yesterday, lol. Mine is at
blog.tomsql.com. Take a look at the answers and comments to this other question for some suggestions of good sources of information and continue to come here to ask (and answer) questions.
https://ask.sqlservercentral.com/questions/134993/tom-and-grant-i-hope-you-read-this.html
If my answer solved your problem, please mark it correct so others know. Thanks.
:-), That's called a good coincidence. I already started following your blogs. I have a long way to go before starting to answer questions but hope to be confident enough soon. As for the data type issue, yes it worked like charm! :-). Its solved now. And yes, thank you for the link. I got some freebies yeeahh!
19 People are following this question.
