Instabase Drive


ertosql.ipynb

Playing with Constraints

You can connect to the aws database using the following psql command, and type w4111student for the password:

psql -h w4111.cisxo09blonu.us-east-1.rds.amazonaws.com -U student w4111
In [ ]:
# replace with URL of your instabase postgres database
ib.connect_db('ib://ewu/w4111-public/databases/test')
In [1]:
%config SqlMagic.autocommit=False
ib.connect_db("postgresql://student:w4111student@w4111.cisxo09blonu.us-east-1.rds.amazonaws.com/w4111")
Connected to: postgresql://student:w4111student@w4111.cisxo09blonu.us-east-1.rds.amazonaws.com/w4111

Domain constraints

In [ ]:
%%sql 
DROP TABLE IF EXISTS A CASCADE;
CREATE TABLE A (
    a int,
    b text,
    c float
);
In [ ]:
%%sql
INSERT INTO A(a, b) VALUES (90, "\'40\');
In [ ]:
%%sql
SELECT * FROM A

Primary Keys

In [ ]:
%%sql
DROP TABLE IF EXISTS A, B CASCADE;

create table a (
    a int,
    primary key (a)
);
create table b ( 
    b int, 
    a int, 
    foreign key (a) references A(a) ON DELETE NO ACTION
);
In [ ]:
%%sql
INSERT INTO a values(1);
INSERT INTO b VALUES (10, 1), (11, 1);
In [ ]:
%%sql
DELETE FROM a WHERE a = 1;
In [ ]:
%%sql
SELECT * FROM b;

References

Let's start with simple references:

A <--- B
In [ ]:

Circular foreign key relationships

Unfortunately, Jupytr cell magic issues don't let this run. You need to use psql to run this example

In [ ]:
%%sql

ER to SQL examples

At-most-one Single Arrow

A -- <R> <-- B
In [11]:
%%sql
DROP TABLE IF EXISTS A, B, R CASCADE;

CREATE TABLE A(a int primary key);
CREATE TABLE B(b int primary key);
CREATE TABLE R(
    a int NOT NULL,
    b int,
    FOREIGN KEY (a) REFERENCES A(a),
    FOREIGN KEY (b) REFERENCES B(b),
    PRIMARY KEY(b)
)
Done.
Done.
Done.
Done.
Out[11]:
In [13]:
%%sql
INSERT INTO A VALUES(1), (2), (3);
INSERT INTO B VALUES(10), (11), (12);
3 rows affected.
3 rows affected.
Out[13]:
In [18]:
%%sql
INSERT INTO R(a, b) VALUES(1, 11);
1 rows affected.
Out[18]:
In [19]:
%%sql
SELECT * FROM R
2 rows affected.
Out[19]:
a b
0 1 10
1 1 11
In [23]:
%%sql
-- same as above
DROP TABLE IF EXISTS  A, B, R, B_R CASCADE;

CREATE TABLE A(a int PRIMARY KEY);
CREATE TABLE B_R(
    b int PRIMARY KEY,
    a int,
    FOREIGN KEY (a) REFERENCES A(a)
);
Done.
Done.
Done.
Out[23]:
In [24]:
%%sql
INSERT INTO B_R(b) VALUES (10);
1 rows affected.
Out[24]:
In [25]:
%%sql
INSERT INTO A VALUES(1), (2);
2 rows affected.
Out[25]:
In [31]:
%%sql
SELECT * FROM B_R;
2 rows affected.
Out[31]:
b a
0 10 1
1 11 1
In [30]:
%%sql
UPDATE B_R SET a = 1 WHERE b = 11;
1 rows affected.
Out[30]:

At-most-one Both Directions

A -->  <R>  <-- B
In [33]:
%%sql
DROP TABLE IF EXISTS A, B, R, B_R CASCADE;

-- three table translation
CREATE TABLE A(a int PRIMARY KEY);
CREATE TABLE B(b int PRIMARY KEY);
CREATE TABLE R(
    a int UNIQUE NOT NULL REFERENCES A(a),
    b int UNIQUE NOT NULL REFERENCES B(b),
    PRIMARY KEY (a, b)
)
Done.
Done.
Done.
Done.
Out[33]:
In [35]:
%%sql
INSERT INTO R(a, b) VALUES (1, 11);

IntegrityErrorTraceback (most recent call last)
<ipython-input-35-604120bbcb76> in <module>()
----> 1 get_ipython().run_cell_magic(u'sql', u'', u'INSERT INTO R(a, b) VALUES (1, 11);')

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/IPython/core/interactiveshell.pyc in run_cell_magic(self, magic_name, line, cell)
   2115             magic_arg_s = self.var_expand(line, stack_depth)
   2116             with self.builtin_trap:
-> 2117                 result = fn(magic_arg_s, cell)
   2118             return result
   2119 

<decorator-gen-131> in execute(self, line, cell, local_ns)

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/IPython/core/magic.pyc in <lambda>(f, *a, **k)
    186     # but it's overkill for just that one bit of state.
    187     def magic_deco(arg):
--> 188         call = lambda f, *a, **k: f(*a, **k)
    189 
    190         if callable(arg):

<decorator-gen-130> in execute(self, line, cell, local_ns)

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/IPython/core/magic.pyc in <lambda>(f, *a, **k)
    186     # but it's overkill for just that one bit of state.
    187     def magic_deco(arg):
--> 188         call = lambda f, *a, **k: f(*a, **k)
    189 
    190         if callable(arg):

/depot/build/py/instabase/notebook/ipython/sql.pyc in execute(self, line, cell, local_ns)
    184     else:
    185       return self.handle_sqlalchemy_url(
--> 186           self.db_url, line=line, cell=cell, local_ns=local_ns)
    187 
    188 

/depot/build/py/instabase/notebook/ipython/sql.pyc in handle_sqlalchemy_url(self, url, line, cell, local_ns)
     75     for statement in sqlparse.split(query):
     76       txt = sqlalchemy.sql.text(statement)
---> 77       result = self.session.execute(txt)
     78 
     79       try:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    946             raise exc.ObjectNotExecutableError(object)
    947         else:
--> 948             return meth(self, multiparams, params)
    949 
    950     def _execute_function(self, func, multiparams, params):

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc in _execute_on_connection(self, connection, multiparams, params)
    267     def _execute_on_connection(self, connection, multiparams, params):
    268         if self.supports_execution:
--> 269             return connection._execute_clauseelement(self, multiparams, params)
    270         else:
    271             raise exc.ObjectNotExecutableError(self)

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_clauseelement(self, elem, multiparams, params)
   1058             compiled_sql,
   1059             distilled_params,
-> 1060             compiled_sql, distilled_params
   1061         )
   1062         if self._has_events or self.engine._has_events:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1198                 parameters,
   1199                 cursor,
-> 1200                 context)
   1201 
   1202         if self._has_events or self.engine._has_events:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1411                 util.raise_from_cause(
   1412                     sqlalchemy_exception,
-> 1413                     exc_info
   1414                 )
   1415             else:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    201     exc_type, exc_value, exc_tb = exc_info
    202     cause = exc_value if exc_value is not exception else None
--> 203     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    204 
    205 if py3k:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1191                         statement,
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:
   1195             self._handle_dbapi_exception(

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    505 
    506     def do_execute(self, cursor, statement, parameters, context=None):
--> 507         cursor.execute(statement, parameters)
    508 
    509     def do_execute_no_params(self, cursor, statement, context=None):

IntegrityError: (psycopg2.IntegrityError) duplicate key value violates unique constraint "r_a_key"
DETAIL:  Key (a)=(1) already exists.
 [SQL: 'INSERT INTO R(a, b) VALUES (1, 11);'] (Background on this error at: http://sqlalche.me/e/gkpj)
In [36]:
%%sql 
-- clear the table
DELETE FROM R;
DELETE FROM A CASCADE;
DELETE FROM B CASCADE;
2 rows affected.
2 rows affected.
2 rows affected.
Out[36]:
In [51]:
%%sql
DROP TABLE IF EXISTS A_R_B CASCADE;

CREATE TABLE A_R_B(
    a int UNIQUE,
    -- all of a's attributes here
    b int UNIQUE,
    -- all of b's attributes here
    CHECK (a IS NOT NULL or b IS NOT NULL)
)
Done.
Done.
Out[51]:
In [52]:
%%sql
INSERT INTO A_R_B(a) VALUES (1), (2), (3);
INSERT INTO A_R_B(b) VALUES (10), (11), (12);
3 rows affected.
3 rows affected.
Out[52]:
In [53]:
%%sql
SELECT * FROM A_R_B;
6 rows affected.
Out[53]:
a b
0 1.0 NaN
1 2.0 NaN
2 3.0 NaN
3 NaN 10.0
4 NaN 11.0
5 NaN 12.0
In [44]:
%%sql
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE A_R_B SET b = 10 WHERE a = 1;
DELETE FROM A_R_B WHERE b = 10 and a is null;
COMMIT;
Done.
Done.
1 rows affected.
1 rows affected.
Done.
Out[44]:
In [ ]:
%%sql
BEGIN;

COMMIT;
In [55]:
from sqlalchemy import create_engine
db = create_engine("postgresql://student:w4111student@w4111.cisxo09blonu.us-east-1.rds.amazonaws.com/w4111")
cur = db.execute("""BEGIN;SET CONSTRAINTS ALL DEFERRED;UPDATE A_R_B SET b = 10 WHERE a = 1;DELETE FROM A_R_B WHERE b = 10 AND a is null;SELECT * FROM A_R_B;COMMIT;""")
for row in cur: print row

IntegrityErrorTraceback (most recent call last)
<ipython-input-55-1d460d438db0> in <module>()
      1 from sqlalchemy import create_engine
      2 db = create_engine("postgresql://student:w4111student@w4111.cisxo09blonu.us-east-1.rds.amazonaws.com/w4111")
----> 3 cur = db.execute("""BEGIN;SET CONSTRAINTS ALL DEFERRED;UPDATE A_R_B SET b = 10 WHERE a = 1;DELETE FROM A_R_B WHERE b = 10 AND a is null;SELECT * FROM A_R_B;COMMIT;""")
      4 for row in cur: print row

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, statement, *multiparams, **params)
   2073 
   2074         connection = self.contextual_connect(close_with_result=True)
-> 2075         return connection.execute(statement, *multiparams, **params)
   2076 
   2077     def scalar(self, statement, *multiparams, **params):

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    940         """
    941         if isinstance(object, util.string_types[0]):
--> 942             return self._execute_text(object, multiparams, params)
    943         try:
    944             meth = object._execute_on_connection

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params)
   1102             statement,
   1103             parameters,
-> 1104             statement, parameters
   1105         )
   1106         if self._has_events or self.engine._has_events:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1198                 parameters,
   1199                 cursor,
-> 1200                 context)
   1201 
   1202         if self._has_events or self.engine._has_events:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1411                 util.raise_from_cause(
   1412                     sqlalchemy_exception,
-> 1413                     exc_info
   1414                 )
   1415             else:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    201     exc_type, exc_value, exc_tb = exc_info
    202     cause = exc_value if exc_value is not exception else None
--> 203     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    204 
    205 if py3k:

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
   1191                         statement,
   1192                         parameters,
-> 1193                         context)
   1194         except BaseException as e:
   1195             self._handle_dbapi_exception(

/mnt/disks/notebook-disk/conda/current/envs/python2/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    505 
    506     def do_execute(self, cursor, statement, parameters, context=None):
--> 507         cursor.execute(statement, parameters)
    508 
    509     def do_execute_no_params(self, cursor, statement, context=None):

IntegrityError: (psycopg2.IntegrityError) duplicate key value violates unique constraint "a_r_b_b_key"
DETAIL:  Key (b)=(10) already exists.
 [SQL: 'BEGIN;SET CONSTRAINTS ALL DEFERRED;UPDATE A_R_B SET b = 10 WHERE a = 1;DELETE FROM A_R_B WHERE b = 10 AND a is null;SELECT * FROM A_R_B;COMMIT;'] (Background on this error at: http://sqlalche.me/e/gkpj)

Participation

Each B record should have at least one relationship with A.

A -- <R> == B
In [63]:
%%sql
-- Does this work?  Not really...
-- actually translates:
--  A -- <R> <== B
DROP TABLE IF EXISTS A, B_R CASCADE;

CREATE TABLE A(a int PRIMARY KEY);
CREATE TABLE B_R(
    b int NOT NULL,
    a int NOT NULL,
    PRIMARY KEY (b, a),
    FOREIGN KEY (a) REFERENCES A(a) ON DELETE NO ACTION
)
Done.
Done.
Done.
Out[63]:
In [64]:
%%sql
INSERT INTO A VALUES(1), (2);
INSERT INTO B_R VALUES (10, 1);
INSERT INTO B_R VALUES (10, 2);
2 rows affected.
1 rows affected.
1 rows affected.
Out[64]:
In [ ]:
%%sql
DROP TABLE IF EXISTS A, B, R;

At most + At Least One

A -- <R> <== B
In [ ]:
%%sql
DROP TABLE IF EXISTS A, B_R;

ISA Relationships

  • Users(uid, name)
  • Students(grades) isa Users
  • Staff(ratings) isa User

Questions

  • what if Employs table wants to reference Users?
  • what if Employs table wants to reference Students and Staff?

Constraints

  • Covering: must every user be an instructor or student?
  • Overlaps: can eugene be an instructor and a student?
In [ ]:
%%sql

CREATE TABLE users(
    uid int primary key,
    name text,
    CHECK ((SELECT COUNT(*) FROM staff WHERE staff.uid = uid) > 0)
);
CREATE TABLE staff(
    uid int REFERENCES users(uid), -- short hand for: FOREIGN KEY (uid) REFERENCES users(uid)
    rating int
);
CREATE TABLE student(
    uid int REFERENCES users(uid),
    grade int
)
-- covering: cant enforce
-- overlaps: can allow
In [70]:
%%sql 
DROP TABLE IF EXISTS staff, student CASCADE;
CREATE TABLE staff(
    uid int primary key,
    name text,
    rating int
);
CREATE TABLE student(
    uid int primary key,
    name text,
    grade int
);
Done.
Done.
Done.
Out[70]:
In [71]:
%%sql
INSERT INTO staff VALUES(1, 'eugene', 100);
INSERT INTO staff VALUES(2, 'billy', 5);
INSERT INTO student VALUES (1, 'billy', 5);
INSERT INTO student VALUES (2, 'eugene', 5);
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
Out[71]:
In [ ]:
%%sql
-- this is a hack that uses a single table to enforce covering and overlaps constraints
-- using a CHECK constraint
DROP TABLE IF EXISTS users;

CREATE TABLE users(
    uid int PRIMARY KEY,
    name text,
    rating int, -- staff
    grade int,   -- student
    check(
        rating is not null or grade is not null
    )
)