FastAPI Create Users Table / Create Foreign Key - 43

Users 테이블을 만들고 Todos 테이블과의 관계를 설정하는 것이 목표(Foreign key)

1. 라이브러리 import

from sqlalchemy.orm import relationship
from sqlalchemy import ForeignKey

 

2. Users 클래스 생성

class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    username = Column(String, unique=True, index=True)
    first_name = Column(String)
    last_name = Column(String)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)

 

3. 테이블 관계도 설정

class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True)
    username = Column(String, unique=True, index=True)
    first_name = Column(String)
    last_name = Column(String)
    hashed_password = Column(String)
    is_active = Column(Boolean, default=True)
	
    #Todos클래스와 관계형성
    todos = relationship("Todos", back_populates="owner")
    
   ---------------------------------------------------------
   
class Todos(Base):
    __tablename__ = "todos"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String)
    description = Column(String)
    priority = Column(Integer)
    complete = Column(Boolean, default=False)
    #users 테이블의 id의 외래키 생성)
    owner_id = Column(Integer, ForeignKey("users.id"))
	
    #Users 관계형성
    owner = relationship("Users", back_populates="todos")

 

4. DB 접속

터미널에서 sqlite3 todos.db 입력한 후. schema를 입력해서 테이블 확인

CREATE TABLE users (
        id INTEGER NOT NULL, 
        email VARCHAR, 
        username VARCHAR, 
        first_name VARCHAR, 
        last_name VARCHAR, 
        hashed_password VARCHAR, 
        is_active BOOLEAN, 
        PRIMARY KEY (id)
);
CREATE INDEX ix_users_id ON users (id);
CREATE UNIQUE INDEX ix_users_email ON users (email);
CREATE UNIQUE INDEX ix_users_username ON users (username);
CREATE TABLE todos (
        id INTEGER NOT NULL, 
        title VARCHAR, 
        description VARCHAR, 
        priority INTEGER, 
        complete BOOLEAN, 
        owner_id INTEGER, 
        PRIMARY KEY (id),
        #외래키 형성 확인
        FOREIGN KEY(owner_id) REFERENCES users (id)
);
CREATE INDEX ix_todos_id ON todos (id);