How do I make Student & Behaviour table link via Incident table?

Question

I believe I am part way there but am really quite unsure as to what to do to make Incident a table that links Student and Behaviour tables. I am writing in python in sqlite3.

c.execute('''CREATE TABLE Student (StudentID integer PRIMARY KEY, Forename text, Surname text, FormNumber text, YearGroup text, Date text, FormTutor text)''')

c.execute('''CREATE table Incident (Student ID integer, BehaviourID integer, Date text)''')

c.execute('''CREATE TABLE Behaviour (BehaviourID integer PRIMARY KEY, BehaviourType text, BehaviourDescription text)''')

Peter Peter · Accepted Answer · 2014-02-25T20:35:44

I think you mostly have it (if I understand your question correctly). Take a look at this:

c.execute('''
CREATE TABLE Student (
    StudentID integer PRIMARY KEY, 
    Forename text, 
    Surname text, 
    FormNumber text, 
    YearGroup text, 
    Date text, 
    FormTutor text)''')

c.execute('''
CREATE TABLE Behaviour (
    BehaviourID integer PRIMARY KEY, 
    BehaviourType text, 
    BehaviourDescription text)''')

c.execute('''
CREATE table Incident (
    StudentID integer, 
    BehaviourID integer, 
    Date text,
    FOREIGN KEY(StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY(BehaviourID) REFERENCES Behaviour(BehaviourID))''')

This creates the Student table, then the Behaviour table, and finally the Incident table with foreign keys referencing the primary keys in Student and Behaviour. The Incident table allows you to define a many-to-many relationship between Student and Behaviour.

How do I make Student & Behaviour table link via Incident table?

1 Answers