SQLTeam.com | Weblogs | Forums

Incorrect datatype

tsql
sql2012

#1

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 smoothing 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.


#2

Can you drop the check constraint then alter then table and recreate the constraint?


#3

ok, yes. I will give it a try and see if this works. :slight_smile:


#4

It worked buddy! thanks a ton. :smile: