PyQt: Basic foreign key misbehaving?

judethedude

Hey everyone, I'm building some tables and encountered something weird. I'm building my two tables with this code (triple quotes isn't formatting correctly):

create_table_query = QSqlQuery()
create_table_query.exec(
        """
        CREATE TABLE IF NOT EXISTS doc (
            doc_id INTEGER PRIMARY KEY,
            doc_name TEXT,
            doc_number TEXT,
            doc_address TEXT,
            doc_notes TEXT
            )
        """
    )

create_table_query.exec(
        """
        CREATE TABLE IF NOT EXISTS patient_profile (
            patient_id INTEGER PRIMARY KEY,
            doc_id INTEGER NOT NULL,
            doc_secondary_id INTEGER NOT NULL,
            name_title TEXT,
            name_fname TEXT,
            FOREIGN KEY (doc_id) REFERENCES doc (doc_id)          
            )
        """
    )

However, in my patient_profile table, if I rename doc_id to anything that doesn't match the name of the primary id in the doc table, the foreign key relationship ceases to exist. For example, I'd like patient_profile to look like this:

    create_table_query.exec(
        """
        CREATE TABLE IF NOT EXISTS patient_profile (
            patient_id INTEGER PRIMARY KEY,
            doc_primary_id INTEGER NOT NULL,
            doc_secondary_id INTEGER NOT NULL,
            name_title TEXT,
            name_fname TEXT,
            FOREIGN KEY (doc_primary_id) REFERENCES doc (doc_id)          
            )
        """
    )

But doc_primary_id doesn't show up as a foreign key in my sqlite browser.
Am I missing something obvious here?
Thanks for your time, Jude.

judethedude

Solved, sorry to bother everyone.
I needed to designate my patient_id as UNIQUE