sql - Altering my datatypes in SQLite -

i following sqlite tutorial goal create simple student database uml diagram.

so lately i'm going through hell trying figure out how alter data types , hoped help. i'll post same question posted instructor.

unfortunately, messed on datatypes when trying follow instructor. video, if helps, can found here: http://www.youtube.com/watch?v=07nbszniwu8

++begin transcript++

---

thanks far, seems have mystery. first thing did copy uml, took break. upon return, figured i'd started uml while reloading video, first things did create student , sex_type table so:

sqlite> create table student(name varchar(23),    ...> sex character(1),    ...> id_number integer primary key);  sqlite> create table sex_type(sex_id text primary key, sex_type integer); 

but realized, "oops forgot indicated want sex_id not null well."

"i'm forgot under ensure foreign key(sex) references sex_type(sex_id)."

so thought, reviewed sql books, , recalled known alter command. however, no matter how slice it, along these lines:

sqlite> alter table sex_type modify column sex_id text primary key not null;  error: near "modify": syntax error  sqlite> alter table sex_type change column sex_id text primary key not null;**  error: near "change": syntax error  sqlite> alter table sex_type drop sex_id;  error: near "drop": syntax error 

always same supposed syntax error. thing has worked tonight:

sqlite> alter table sex_type rename gender;  sqlite> alter table gender_id rename sex_type; 

---

so syntax error i'm overlooking, because going books syntax should fine. have insert these columns before modification can done (which admittedly, haven't tried yet)? or missing obvious alter/modify/change/drop command(s)?

sqlite's support alter table pretty limited, including rename to , add_column. you're getting syntax errors because you're issuing command that's not supported.

if don't have data lose, might better of dropping , recreating table.

if have data lose, might better off renaming table , creating replacement using insert select, seen in this example.