Lab Content
Objective
Students use Python to interact with a relational database management system.
Procedure
Working with Check Constraints
We can add a check constraint using the SQL as follows:
cur.execute("""CREATE TABLE creek_data(
name varchar,
alky float CHECK (alky > 20),
ph float,
tot_p float CHECK (tot_p > 20);""")
Now insert the data from the Creek Data relation.
Check constraint can refer to several columns. If we only wanted data that had a lower alky than tot_p
we would do it as follows:
cur.execute("""CREATE TABLE creek_data(
name varchar,
alky float CHECK (alky > 20),
ph float CHECK (ph < 20),
tot_p float CHECK (tot_p < 1) ,
CONSTRAINT alt CHECK (alky < tot_p));""")
We can add a check constraint and give it a name using the SQL as follows:
cur.execute("""CREATE TABLE creek_data(
name varchar,
alky float CONSTRAINT positive_alky CHECK(alky > 0),
ph float,
tot_p float);""")
Not Null Constraints
We can can create columns that will except NULL for a value or that will
except not except NULL for a value. NULL and NOT NULL can be used in
conjunction with CONSTRAINTS as show in the SQL below:
cur.execute("""CREATE TABLE creek_data(
name varchar NOT NULL,
alky float NOT NULL CONSTRAINT positive_alky CHECK(alky > 0),
ph float NULL,
tot_p float);""")
Unique Constraints
A unique constraint that the data for the column with the constraint is unique.
We can do this using the following SQL:
cur.execute("""CREATE TABLE creek_data(
name varchar UNIQUE,
alky float,
ph float,
tot_p float);""")
Notice if you change the name of Balus Creek to Shallow Creek, the parameters have been met.
If you would like to put a unique constraint on more than one column you could use the following
SQL:
cur.execute("""CREATE TABLE creek_data(
name varchar,
alky float,
ph float,
tot_p float,
UNIQUE(name, alky);""")
Like we did with constraint, you can give your UNIQUE constraint a name:
cur.execute("""CREATE TABLE creek_data(
name varchar CONSTRAINT must_be_unique UNIQUE,
alky float,
ph float,
tot_p float,
UNIQUE(name, alky);""")
Primary Keys Constraint
Primary Keys are the same as UNIQUE NOT NULL:
- cur.execute(“”“CREATE TABLE creek_data(
name varchar UNIQUE NOT NULL,
alky float,
ph float,
tot_p float);”“”)
- cur.execute(“”“CREATE TABLE creek_data(
name varchar PRIMARY KEY,
alky float,
ph float,
tot_p float);”“”)
Notice the error says Key(name)=(Balus Creek) already exists
If we change our SQL to say PRIMARY the results are the same.
We can also have more than one primary key:
cur.execute("""CREATE TABLE creek_data(
name varchar,
alky float,
ph float,
tot_p float,
PRIMARY KEY(name, alky));""")
Foreign Keys Constraint
A foreign key constraint allows us to link tables together based on a common columns:
cur.execute("""CREATE TABLE rider(
name varchar PRIMARY KEY,
age integer,
gender varchar);""")
cur.execute("""CREATE TABLE make(
name varchar REFERENCES rider(name),
make varchar);""")
#Or we can shorten it
cur.execute("""CREATE TABLE make(
name varchar REFERENCES rider,
make varchar);""")
We can also use a foreign key to constrain and reference more than one column.
The tables we created for this assignment do not have enough columns in common to be able to use
more than one reference, but if we were to use more than reference we would use Python as follows:
cur.execute("""CREATE TABLE example1(
a1 integer PRIMARY KEY,
b1 integer,
c1 integer,
FOREIGN KEY (b1, c1) REFERENCES other_table(b2, c2)
);""")
#Note: number of constrained columns must match number and type of referenced columns
A table may contain more than one foreign key constraint. An example would be creating a new table containing the
riders name and the names of the riders from the make table. We can do this using Python as follows:
cur.execute("""CREATE TABLE riders(
name_rider varchar PRIMARY KEY,
age integer,
gender varchar
);""")
cur.execute("""CREATE TABLE make(
name_make varchar PRIMARY KEY,
make varchar
);""")
cur.execute("""CREATE TABLE riderMake(
name_rider varchar REFERENCES riders,
name_make varchar REFERENCES make,
PRIMARY KEY(name_rider, name_make)
);""")
We can set up constraints that will not allow users to delete a table that references another table or that will
delete the data from both tables. We can do this using Python as follows:
cur.execute("""CREATE TABLE riders(
name_rider varchar PRIMARY KEY,
age integer,
gender varchar
);""")
cur.execute("""CREATE TABLE make(
name_make varchar PRIMARY KEY,
make varchar
);""")
cur.execute("""CREATE TABLE riderMake(
name_rider varchar REFERENCES riders ON DELETE RESTRICT,
name_make varchar REFERENCES make ON DELETE CASCADE,
PRIMARY KEY(name_rider, name_make)
);""")
References
Update soon.
#Adding this line to try and get changes to push.
Metadata
========
| Title:Relational Algebra
| CreationDate:12/16/2013
| SoftwareUsed:None
| SoftwareVersion: None
| Contributors: Zac Miller, Crystal Lyliston