Sqlite Foreign Key總結(jié)

android Sqlite 外鍵默認(rèn)是關(guān)閉的,需要在SQLiteOpenHelper的子類里面打開,代碼如下

? @Override

public void onOpen(SQLiteDatabase db) {

? ? ? ? ? ?super.onOpen(db);

? ? ? ? ? ?if (!db.isReadOnly()) {

? ? ? ? ? ? // Enable foreign key constraints 開啟外鍵約束

? ? ? ? ? ?db.execSQL("PRAGMA foreign_keys=ON;");

? ?}

}


1介紹外鍵約束

SQL外鍵約束用于強(qiáng)制表之間的“存在”關(guān)系。例如呜象,考慮使用以下SQL命令創(chuàng)建的數(shù)據(jù)庫模式

CREATE TABLE artist( artistid? ? INTEGER PRIMARY KEY, ?artistname? TEXT);

CREATE TABLE track(trackid? ? INTEGER,trackname? TEXT,trackartist INTEGER? ? -- Must map to an artist.artistid!);

使用此數(shù)據(jù)庫的應(yīng)用程序有權(quán)假定跟蹤表中的每一行都有一個(gè)對(duì)應(yīng)的行厂画。畢竟睛约,聲明中的評(píng)論是這樣說的瑰步。不幸的是拐格,如果用戶使用外部工具編輯數(shù)據(jù)庫荐虐,或者應(yīng)用程序中存在bug藕夫,則行可能插入到不符合artist表中任何行的跟蹤表中嫡秕】视铮或者從artist表中刪除行,在track表中留下不符合artist中剩余行的孤立行淘菩。這可能導(dǎo)致應(yīng)用程序或應(yīng)用程序稍后出現(xiàn)故障遵班,或者至少使應(yīng)用程序編碼更加困難。

一種解決方案是在數(shù)據(jù)庫模式中添加一個(gè)SQL外鍵約束潮改,以強(qiáng)制artist和track之間的關(guān)系狭郑。為此,可以通過將track的聲明修改為以下來添加外鍵定義:

CREATE TABLE track(trackid? ? INTEGER,trackname? TEXT,trackartist INTEGER,

FOREIGN KEY(trackartist) REFERENCES artist(artistid));

通過這種方式,Sqlite被強(qiáng)制添加約束,當(dāng)嘗試向track表插入一條和artist表中沒有關(guān)聯(lián)的數(shù)據(jù)時(shí)候會(huì)失敗.當(dāng)從artist表中刪除一條和track表有關(guān)聯(lián)的數(shù)據(jù)會(huì)觸發(fā)異常(Foreign Key Constraint Exception).

sqlite> SELECT * FROM artist;

artistid ? ? ? artistname

--------? -----------------

1 ? ? ? ? ? ? ? ?Dean Martin

2 ? ? ? ? ? ? ? ?Frank Sinatra

sqlite> SELECT * FROM track;

trackid ? ? ? trackname? ? ? ? ? trackartist

-------? -----------------? -----------

11 ? ? ? ? ? ? ?That's Amore ? ? ? ? ? ? ?1

12 ? ? ? ? ? ? ?Christmas Blues ? ? ? ? 1

13 ? ? ? ? ? ? ? My Way ? ? ? ? ? ? ? ? ? ? ? 2


sqlite> INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);

SQL error: foreign key constraint failed

//track表中trackartist=3有問題,track(trackartist) 關(guān)聯(lián)artist(artistid),artistid只有倆條數(shù)據(jù)

//(artistid=1和artistid=2) 汇在,應(yīng)該首先給artist 插入一條artistid=3的數(shù)據(jù)就沒問題了.如下

INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');

INSERT INTO track VALUES(14, 'Mr. Bojangles', 3);


正如所愿,不能通過刪除/更新artist表中和artist表有關(guān)聯(lián)的數(shù)據(jù)


DELETE FROM artist WHERE artistname = 'Frank Sinatra';

SQL error: foreign key constraint failed

artist表artistname = 'Frank Sinatra'這個(gè)數(shù)據(jù)和track表trackname = 'My Way'存在關(guān)聯(lián),不可以刪除,如果想刪除這條數(shù)據(jù)首先刪除track表中關(guān)聯(lián)的數(shù)據(jù),在執(zhí)行這個(gè)刪除命令即可如下翰萨。

DELETE FROM track WHERE trackname = 'My Way';

DELETE FROM artist WHERE artistname = 'Frank Sinatra';


UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';

SQL error: foreign key constraint failed

artist表中artistname = 'Dean Martin'數(shù)據(jù)和track表有關(guān)聯(lián),如果想刪除,如下

DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues');

UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin';


2.ON DELETE and ON UPDATE操作

外建On DeleteOn Update 有五種配置類型,No Action/Restrict/Set Null/Set Default/Cascade默認(rèn)是No Action

no action. 父表刪除或者更新(外建所關(guān)聯(lián)的數(shù)據(jù)庫字段)時(shí)候,會(huì)報(bào)foreign key constrain錯(cuò)誤.

restrict.和no action類似

set null.父表刪除或者更新(外建所關(guān)聯(lián)的數(shù)據(jù)庫字段)時(shí)候,子表foreign key關(guān)聯(lián)的列數(shù)據(jù)重置為null.

set default.與set null類似,父表刪除或者更新(外建所關(guān)聯(lián)的數(shù)據(jù)庫字段)時(shí)候,子表foreign key關(guān)聯(lián)的列數(shù)據(jù)重置為創(chuàng)建表時(shí)該列的default value.

cascade.父表與子表關(guān)聯(lián),刪除夫表會(huì)把子表里面與父表外建關(guān)聯(lián)的數(shù)據(jù)都刪除,更新父表里數(shù)據(jù)(被子表關(guān)聯(lián)的foreign key列)會(huì)同步更新子表外建列的值糕殉。

如下

CREATE TABLE artist(artistid? ? INTEGER PRIMARY KEY,artistname? TEXT);

CREATE TABLE track(trackid? ? INTEGER,trackname? TEXT,trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE);


sqlite> SELECT * FROM artist;

artistid? ? ? artistname

--------? ? -----------------

1? ? ? ? ? ? ? ? Dean Martin

2? ? ? ? ? ? ? ? Frank Sinatra


sqlite> SELECT * FROM track;

trackid? ? ? ? trackname? ? ? ? ? ? trackartist

-------? ? ? --------------? ? ? ? -----------

11? ? ? ? ? ? ? ? That's Amore? ? ? ? ? ? ? 1

12? ? ? ? ? ? ? Christmas Blues? ? ? ? ? 1?

13? ? ? ? ? ? ? ? My Way? ? ? ? ? ? ? ? ? ? ? ? 2


//track表和artist表關(guān)聯(lián)外建使用ON UPDATE CASCADE,修改夫表,會(huì)同步修改子表所有與父表關(guān)聯(lián)列的值
sqlite> UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';

sqlite> SELECT * FROM artist;

artistid? artistname

--------? -----------------

2? ? ? ? Frank Sinatra

100? ? ? Dean Martin

sqlite> SELECT * FROM track;

trackid ? ? ? ?trackname? ? ? ? ? trackartist

------- ? ?-----------------? -----------

?11 ? ? ? ? ? ? ?That's Amore ? ? ? ? ?100

?12 ? ? ? ? ? ? ?Christmas Blues? ? 100

?13 ? ? ? ? ? ? ? ? ? ?My Way? ? ? ? ? ? 2


使用On Update或On Delete,如果配置ON DELETE SET DEFAULT,刪除夫表數(shù)據(jù)(與子表有關(guān)聯(lián)的數(shù)據(jù)),子表foreign key數(shù)據(jù)會(huì)重置為創(chuàng)建表時(shí)候default value.如果default value在夫表沒有關(guān)聯(lián),報(bào)foreign key constraint failed錯(cuò)誤亩鬼。如下

CREATE TABLE artist(artistid? ? INTEGER PRIMARY KEY,artistname? TEXT);

CREATE TABLE track(trackid? ? INTEGER,trackname? TEXT,trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT);

sqlite> SELECT * FROM artist;

artistid ? ? ? artistname

-------- ? ? -----------------

?3 ? ? ? ? ? ? ? ?Sammy Davis Jr.

sqlite> SELECT * FROM track;

trackid ? trackname? ? ? ? ? trackartist

------- ? --------------- ?-----------

?14 ? ? ? ? ?Mr. Bojangles ? ? ? ?3

//刪除父表數(shù)據(jù),子表trackartist=0,沒有與父表匹配報(bào)SQL error: foreign key constraint failed

sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';

如下修改,在夫表插入一條default的數(shù)據(jù)即可

sqlite> INSERT INTO artist VALUES(0, 'Unknown Artist');

sqlite> DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.';
































最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市捕仔,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌盈罐,老刑警劉巖榜跌,帶你破解...
    沈念sama閱讀 211,639評(píng)論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場(chǎng)離奇詭異盅粪,居然都是意外死亡钓葫,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,277評(píng)論 3 385
  • 文/潘曉璐 我一進(jìn)店門票顾,熙熙樓的掌柜王于貴愁眉苦臉地迎上來础浮,“玉大人,你說我怎么就攤上這事奠骄《雇” “怎么了?”我有些...
    開封第一講書人閱讀 157,221評(píng)論 0 348
  • 文/不壞的土叔 我叫張陵含鳞,是天一觀的道長影锈。 經(jīng)常有香客問我,道長蝉绷,這世上最難降的妖魔是什么鸭廷? 我笑而不...
    開封第一講書人閱讀 56,474評(píng)論 1 283
  • 正文 為了忘掉前任,我火速辦了婚禮熔吗,結(jié)果婚禮上辆床,老公的妹妹穿的比我還像新娘。我一直安慰自己桅狠,他們只是感情好讼载,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,570評(píng)論 6 386
  • 文/花漫 我一把揭開白布宵晚。 她就那樣靜靜地躺著,像睡著了一般维雇。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上晒他,一...
    開封第一講書人閱讀 49,816評(píng)論 1 290
  • 那天吱型,我揣著相機(jī)與錄音,去河邊找鬼陨仅。 笑死津滞,一個(gè)胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的灼伤。 我是一名探鬼主播触徐,決...
    沈念sama閱讀 38,957評(píng)論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼狐赡!你這毒婦竟也來了撞鹉?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 37,718評(píng)論 0 266
  • 序言:老撾萬榮一對(duì)情侶失蹤颖侄,失蹤者是張志新(化名)和其女友劉穎鸟雏,沒想到半個(gè)月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體览祖,經(jīng)...
    沈念sama閱讀 44,176評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡孝鹊,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,511評(píng)論 2 327
  • 正文 我和宋清朗相戀三年,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了展蒂。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片又活。...
    茶點(diǎn)故事閱讀 38,646評(píng)論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡,死狀恐怖锰悼,靈堂內(nèi)的尸體忽然破棺而出柳骄,到底是詐尸還是另有隱情,我是刑警寧澤松捉,帶...
    沈念sama閱讀 34,322評(píng)論 4 330
  • 正文 年R本政府宣布夹界,位于F島的核電站,受9級(jí)特大地震影響隘世,放射性物質(zhì)發(fā)生泄漏可柿。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,934評(píng)論 3 313
  • 文/蒙蒙 一丙者、第九天 我趴在偏房一處隱蔽的房頂上張望复斥。 院中可真熱鬧,春花似錦械媒、人聲如沸目锭。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,755評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽痢虹。三九已至被去,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間奖唯,已是汗流浹背惨缆。 一陣腳步聲響...
    開封第一講書人閱讀 31,987評(píng)論 1 266
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留丰捷,地道東北人坯墨。 一個(gè)月前我還...
    沈念sama閱讀 46,358評(píng)論 2 360
  • 正文 我出身青樓,卻偏偏與公主長得像病往,于是被迫代替她去往敵國和親捣染。 傳聞我的和親對(duì)象是個(gè)殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,514評(píng)論 2 348

推薦閱讀更多精彩內(nèi)容