Implementing primary and foreign key constraints in SQLite for Python projects

Data and Databases
1

I’m working on a Python project using SQLite and I’m struggling to set up primary and foreign keys correctly. I want to create two tables: vehicle_base and vehicle_details.

The vehicle_base table should have a primary key auto_id and a foreign key type_id. The vehicle_details table needs two foreign keys: auto_id and type_id.

Here’s what I’ve tried so far:

import sqlite3

conn = sqlite3.connect('AutoDatabase.db')
cursor = conn.cursor()

cursor.execute('''
CREATE TABLE vehicle_base (
    auto_id INTEGER PRIMARY KEY,
    type_id INTEGER,
    FOREIGN KEY (type_id) REFERENCES vehicle_base(auto_id)
)
''')

cursor.execute('''
CREATE TABLE vehicle_details (
    auto_id INTEGER,
    type_id INTEGER,
    manufacture_year INTEGER,
    cost INTEGER,
    pre_owned BOOLEAN,
    FOREIGN KEY (auto_id) REFERENCES vehicle_base(auto_id),
    FOREIGN KEY (type_id) REFERENCES vehicle_base(type_id)
)
''')

conn.close()

Can someone help me understand if this is the right way to set up these relationships? I’m not sure if I’m using the FOREIGN KEY constraints correctly.

2

ooh, interesting project! have u considered using an ORM like SQLAlchemy? it can make handling relationships way easier. also, what kinda vehicles are u working with? cars, boats, planes? im curious how complex ur data model needs to be. maybe we could brainstorm some ideas for optimizing the schema?

3

hey, ur code looks pretty close! just a couple tweaks:

  1. vehicle_base: type_id should reference a diff table (like vehicle_types), not itself
  2. vehicle_details: add PRIMARY KEY (auto_id, type_id)
  3. enable foreign key support: conn.execute(‘PRAGMA foreign_keys = ON;’)

hope that helps! lmk if u need anything else