FK test

main.py

import sqlite3

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

cursor.execute('''
	CREATE TABLE IF NOT EXISTS departments (
		department_id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL
	)
''')

cursor.execute('''
	CREATE TABLE IF NOT EXISTS employees (
		employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		department_id INTEGER,
		FOREIGN KEY (department_id) REFERENCES departments(department_id)
	)
''')

cursor.execute('''
	CREATE TABLE IF NOT EXISTS projects (
		project_id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		department_id INTEGER,
		FOREIGN KEY (department_id) REFERENCES departments(department_id)
	)
''')

cursor.execute('''
	CREATE TABLE IF NOT EXISTS tasks (
		task_id INTEGER PRIMARY KEY AUTOINCREMENT,
		description TEXT NOT NULL,
		employee_id INTEGER,
		project_id INTEGER,
		FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
		FOREIGN KEY (project_id) REFERENCES projects(project_id)
	)
''')

# 5 Departments
departments = [(f"Department {i+1}",) for i in range(5)]
cursor.executemany('INSERT INTO departments (name) VALUES (?)', departments)

# 100 Employees
employees = [(f"Employee {i+1}", i % 5 + 1) for i in range(100)]
cursor.executemany('INSERT INTO employees (name, department_id) VALUES (?, ?)', employees)

# 20 Projects
projects = [(f"Project {i+1}", i % 5 + 1) for i in range(20)]
cursor.executemany('INSERT INTO projects (name, department_id) VALUES (?, ?)', projects)

# 200 Tasks
tasks = [(f"Task {i+1}", i % 100 + 1, i % 20 + 1) for i in range(200)]
cursor.executemany('INSERT INTO tasks (description, employee_id, project_id) VALUES (?, ?, ?)', tasks)

conn.commit()
conn.close()