sqlite修改表格约束

- 已有表格

CREATE TABLE sentence_word (
	sw_no	INTEGER NOT NULL UNIQUE,
	word_name	varchar(10) NOT NULL,
	sentence_no	INTEGER NOT NULL,
	sentence_name	TEXT NOT NULL,
	PRIMARY KEY(sw_no AUTOINCREMENT),
	FOREIGN KEY(sentence_name) REFERENCES sentence(sentence_name),
	FOREIGN KEY(sentence_no) REFERENCES sentence(sentence_no),
	FOREIGN KEY(word_name) REFERENCES words(word_name)
);

添加修改和删除的约束 ON UPDATE CASCADE ON DELETE CASCADE

PRAGMA foreign_keys = OFF;

SAVEPOINT temp;

CREATE TABLE sentence_word_temp (
	sw_no	INTEGER NOT NULL UNIQUE,
	word_name	varchar(10) NOT NULL,
	sentence_no	INTEGER NOT NULL,
	sentence_name	TEXT NOT NULL,
	PRIMARY KEY(sw_no AUTOINCREMENT),
	FOREIGN KEY(sentence_name) REFERENCES sentence(sentence_name) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY(sentence_no) REFERENCES sentence(sentence_no) ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY(word_name) REFERENCES words(word_name) ON UPDATE CASCADE ON DELETE CASCADE
);

INSERT INTO sentence_word_temp (sw_no, word_name, sentence_no,sentence_name) SELECT sw_no, word_name, sentence_no,sentence_name FROM sentence_word;

DROP TABLE sentence_word;

ALTER TABLE sentence_word_temp RENAME TO sentence_word;

PRAGMA foreign_keys = ON;

COMMIT;