SQL - 制約、ビュー、トランザクション: 料理人が多すぎると、データベースがダメになる(WITH CHECK OPTION, VIEW, INSERT, DELETE, UPDATE)

Head First SQL
頭とからだで覚えるSQLの基本
(オライリージャパン)
Lynn Beighley (著)
佐藤 直生 (監訳), 松永 多苗子 (翻訳)

開発環境

• OS X Mavericks - Apple、たまにFreeBSD 10(OS)
• Emacs (CUI)、BBEdit - Bare Bones Software, Inc. (GUI) (Text Editor)
• Python 3.4(プログラミング言語)
• SQLite (RDBMS(Relational Database Management System))

Head First SQL ―頭とからだで覚えるSQLの基本 (Lynn Beighley(著)、 佐藤 直生 (監訳)、 松永 多苗子 (翻訳)、オライリージャパン)の11章(制約、ビュー、トランザクション: 料理人が多すぎると、データベースがダメになる)、エクササイズ(p.473)を解いてみる。

エクササイズ(p.473)

最終的なpiggy_bankテーブルの予想図。

piggy_bank

id	coin	coin_year
1	Q	1950
2	P:	1972
3	N	2005
4	Q	1999
5	Q	1981
6	D	1940
7	Q	1980
8	P	2001
9	D	1926
10	P	1999
11	Q	1993
12	D	1942

コード(BBEdit, Emacs)

sample473.py

#!/usr/bin/env python3
#-*- coding: utf-8 -*-

import sqlite3

connection = sqlite3.connect('chapter11.sqlite')
cursor = connection.cursor()

def p_all(table):
    print(table)
    cursor.execute("""SELECT * FROM {0}""".format(table))
    print(tuple(map(lambda header: header[0], cursor.description)))
    for row in cursor.fetchall():
        print(row)


p_all('piggy_bank')

try:
    cursor.execute("""
    CREATE VIEW pb_quarters AS
    SELECT * FROM piggy_bank
    WHERE coin = 'Q'
    """)
    p_all('pb_quarters')
except Exception as err:
    print(type(err), err)

try:
    cursor.execute("""
    CREATE VIEW pb_dimes AS
    SELECT * FROM piggy_bank
    WHERE coin = 'D'
    CHECK OPTION
    """)
    p_all('pb_dimes')
except Exception as err:
    print(type(err), err)

try:
    cursor.execute("""
    INSERT INTO pb_quarters VALUES
    (NULL, 'Q', 1993)
    """)
    p_all('piggy_bank')
except Exception as err:
    print(type(err), err)

try:
    cursor.execute("""
    INSERT INTO pb_quarters VALUES
    (NULL, 'D', 1942)
    """)
    p_all('piggy_bank')
except Exception as err:
    print(type(err), err)

try:
    cursor.execute("""
    INSERT INTO pb_dimes VALUES (NULL, 'Q', 2005)
    """)
    p_all('piggy_bank')
except Exception as err:
    print(type(err), err)

try:
    cursor.execute("""
    DELETE FROM pb_quarters
    WHERE coin = 'N'
    OR coin ='P'
    OR coin = 'D'
    """)
    p_all('piggy_bank')
except Exception as err:
    print(type(err), err)

try:
    cursor.execute("""
    UPDATE pb_quarters
    SET coin = 'Q'
    WHERE coin = 'P'
    """)
    p_all('piggy_bank')
except Exception as err:
    print(type(err), err)

connection.commit()
connection.close()

入出力結果(Terminal, IPython)

$ ./sample473.py
piggy_bank
('id', 'coin', 'coin_year')
(1, 'Q', '1950')
(2, 'P', '1972')
(3, 'Q', '1950')
(4, 'P', '1972')
(5, 'N', '2005')
(6, 'Q', '1999')
(7, 'Q', '1981')
(8, 'D', '1940')
(9, 'Q', '1980')
(10, 'P', '1999')
pb_quarters
('id', 'coin', 'coin_year')
(1, 'Q', '1950')
(3, 'Q', '1950')
(6, 'Q', '1999')
(7, 'Q', '1981')
(9, 'Q', '1980')
<class 'sqlite3.OperationalError'> near "CHECK": syntax error
<class 'sqlite3.OperationalError'> cannot modify pb_quarters because it is a view
<class 'sqlite3.OperationalError'> cannot modify pb_quarters because it is a view
<class 'sqlite3.OperationalError'> no such table: pb_dimes
<class 'sqlite3.OperationalError'> cannot modify pb_quarters because it is a view
<class 'sqlite3.OperationalError'> cannot modify pb_quarters because it is a view
$

エラーメッセージから、SQLiteでは、VIEWを修正してテーブルを変更することはできないみたい。あと、WITH CHECK OPTIONもないみたい。