sqlite에서 지원하지 않는! 것들!

mysql..oracle쓰다가 sqlite도 당연히 되겠지라는 생각으로 사용하다가 괜한 시간낭비를 해버렸다. sqlite에서 지원하지 않는것들을 보자

http://www.sqlite.org/omitted.html

RIGHT and FULL OUTER JOIN		LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
Complete ALTER TABLE support		Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
Complete trigger support		FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
Writing to VIEWs		VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
GRANT and REVOKE		Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.

으악!! alter table이 안되는것이였다 이것도 모르고 찾아다녔다니!!!!!!!!

그러다가 테이블을 복제하고 삭제하는 방식을 이용해 fk를 추가하라..라고 나와있다. https://www.sqlite.org/lang_altertable.html#otheralter

1. If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF.
2. Start a transaction.
3. Remember the format of all indexes and triggers associated with table X. This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.
4. Use CREATE TABLE to construct a new table "new_X" that is in the desired revised format of table X. Make sure that the name "new_X" does not collide with any existing table name, of course.
5. Transfer content from X into new_X using a statement like: INSERT INTO new_X SELECT ... FROM X.
6. Drop the old table X: DROP TABLE X.
7. Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.
8. Use CREATE INDEX and CREATE TRIGGER to reconstruct indexes and triggers associated with table X. Perhaps use the old format of the triggers and indexes saved from step 3 above as a guide, making changes as appropriate for the alteration.
9. If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.
10. If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints.
11. Commit the transaction started in step 2.
12. If foreign keys constraints were originally enabled, reenable them now.