question

Asim1589 avatar image
Asim1589 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.
tsqldatabase-designtablesdatatypesalter-table
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

1 Answer

·
Tom Staab avatar image
Tom Staab answered
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')) ;
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks so much Tom, I loved the explanation. Are there any blogs you maintain for T-SQL for Database Design, Please share your knowledge.
0 Likes 0 ·
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
0 Likes 0 ·
If my answer solved your problem, please mark it correct so others know. Thanks.
0 Likes 0 ·
:-), 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!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.