Altering a column on SQLite after definition

Question

I was following an SQLite tutorial where the goal is to create a simple student database from an UML diagram.

The first thing I did was copy your UML, then took a break. Upon return, I figured I'd get started from the UML while reloading your video, so the very first things I did was create the student and sex_type table as 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 then I realized, I forgot to indicated that I want sex_id to be NOT NULL as well. I also forgot under the to ensure that Foreign Key(sex) references sex_type(sex_id).

I reviewed my SQL books, and recalled something known as the ALTER command. However, no matter how I slice it, I get something 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 with the same supposed syntax error. About the only thing that has worked tonight:

sqlite> alter table sex_type RENAME TO gender;
sqlite> alter table gender_id RENAME TO sex_type;

So what is this syntax error that I'm overlooking, because going by my books my syntax should be fine. Do I have to insert something into these columns before modification can be done (which admittedly, I haven't tried yet)? Or am I missing something obvious about the ALTER/MODIFY/CHANGE/DROP command(s)?

possible duplicate of [Add not null DateTime column to SQLite without default value?](http://stackoverflow.com/questions/3027438/add-not-null-datetime-column-to-sqlite-without-default-value) — dan04, Feb 06 '14 at 00:30

score 3 · Answer 1 · edited May 23 '17 at 12:34

SQLite's support for ALTER TABLE is pretty limited, including only RENAME TO and ADD_COLUMN. You're getting syntax errors because you're issuing a command that's not supported.

If you don't have any data to lose, you might be better of dropping and recreating the table.

If you do have data to lose, you might be better off renaming the table and creating a replacement using INSERT with SELECT, as seen in this example.

Altering a column on SQLite after definition

1 Answers1