Primary Key

# Primary Key 예제 - Python + SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.dialects.postgresql import UUID
from datetime import datetime
import uuid

Base = declarative_base()


# ============================================
# 1. 자동 증가 Primary Key
# ============================================
class User(Base):
    """자동 증가 PK 사용"""
    __tablename__ = 'users'

    # 자동 증가 PK (PostgreSQL SERIAL / MySQL AUTO_INCREMENT)
    id = Column(Integer, primary_key=True, autoincrement=True)
    email = Column(String(255), unique=True, nullable=False)
    name = Column(String(100), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    # 관계
    orders = relationship('Order', back_populates='user')

    def __repr__(self):
        return f""


# ============================================
# 2. UUID Primary Key
# ============================================
class Order(Base):
    """UUID PK 사용 - 분산 시스템에 적합"""
    __tablename__ = 'orders'

    # UUID PK
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    total_amount = Column(Integer, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship('User', back_populates='orders')


# ============================================
# 3. 복합 Primary Key
# ============================================
class StudentCourse(Base):
    """복합 PK - 다대다 중간 테이블"""
    __tablename__ = 'student_courses'

    # 복합 PK
    student_id = Column(Integer, ForeignKey('students.id'),
                       primary_key=True)
    course_id = Column(Integer, ForeignKey('courses.id'),
                      primary_key=True)

    enrolled_at = Column(DateTime, default=datetime.utcnow)
    grade = Column(String(2))

    # 관계
    student = relationship('Student', back_populates='enrollments')
    course = relationship('Course', back_populates='enrollments')


class Student(Base):
    __tablename__ = 'students'
    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    enrollments = relationship('StudentCourse', back_populates='student')


class Course(Base):
    __tablename__ = 'courses'
    id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    enrollments = relationship('StudentCourse', back_populates='course')


# ============================================
# 4. 자연 키(Natural Key)
# ============================================
class Country(Base):
    """자연 키 사용 - ISO 국가 코드"""
    __tablename__ = 'countries'

    # 자연 키 PK
    code = Column(String(2), primary_key=True)  # 'KR', 'US'
    name = Column(String(100), nullable=False)
    continent = Column(String(50))


# ============================================
# 5. PK 사용 예제
# ============================================
def main():
    engine = create_engine('postgresql://localhost/mydb', echo=True)
    Base.metadata.create_all(engine)

    Session = sessionmaker(bind=engine)
    session = Session()

    try:
        # 사용자 생성 (PK 자동 할당)
        user = User(email='test@example.com', name='홍길동')
        session.add(user)
        session.flush()  # PK 값 확정

        print(f"생성된 사용자 ID: {user.id}")  # 자동 생성된 ID

        # UUID PK 주문 생성
        order = Order(user_id=user.id, total_amount=50000)
        session.add(order)
        session.flush()

        print(f"생성된 주문 UUID: {order.id}")

        # PK로 조회 (가장 빠름)
        found_user = session.query(User).get(user.id)  # deprecated in 2.0
        # SQLAlchemy 2.0 스타일
        found_user = session.get(User, user.id)

        # 복합 PK로 조회
        enrollment = session.get(StudentCourse, (1, 1))  # (student_id, course_id)

        session.commit()

    except Exception as e:
        session.rollback()
        print(f"에러: {e}")
    finally:
        session.close()


if __name__ == '__main__':
    main()

// Primary Key 예제 - Node.js + Prisma

// ============================================
// 1. Prisma 스키마 (schema.prisma)
// ============================================
/*
// 자동 증가 PK
model User {
  id        Int       @id @default(autoincrement())
  email     String    @unique
  name      String
  createdAt DateTime  @default(now())

  orders    Order[]
}

// UUID PK
model Order {
  id          String    @id @default(uuid())
  userId      Int
  totalAmount Decimal   @db.Decimal(10, 2)
  createdAt   DateTime  @default(now())

  user        User      @relation(fields: [userId], references: [id])
}

// 복합 PK
model StudentCourse {
  studentId  Int
  courseId   Int
  enrolledAt DateTime @default(now())
  grade      String?

  student    Student  @relation(fields: [studentId], references: [id])
  course     Course   @relation(fields: [courseId], references: [id])

  @@id([studentId, courseId])  // 복합 PK
}

// 자연 키
model Country {
  code      String  @id @db.Char(2)  // 'KR', 'US'
  name      String
  continent String?
}

// CUID (Collision-resistant Unique ID)
model Post {
  id        String   @id @default(cuid())
  title     String
  content   String?
  createdAt DateTime @default(now())
}
*/


// ============================================
// 2. Prisma Client 사용
// ============================================
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
    // 자동 증가 PK - 생성 시 ID 자동 할당
    const user = await prisma.user.create({
        data: {
            email: 'test@example.com',
            name: '홍길동'
        }
    });
    console.log('생성된 사용자 ID:', user.id);  // 1, 2, 3...


    // UUID PK - 생성 시 UUID 자동 할당
    const order = await prisma.order.create({
        data: {
            userId: user.id,
            totalAmount: 50000
        }
    });
    console.log('생성된 주문 UUID:', order.id);  // 550e8400-e29b-41d4...


    // PK로 단일 레코드 조회 (가장 빠름)
    const foundUser = await prisma.user.findUnique({
        where: { id: user.id }
    });


    // 복합 PK로 조회
    const enrollment = await prisma.studentCourse.findUnique({
        where: {
            studentId_courseId: {  // 복합 PK 이름
                studentId: 1,
                courseId: 1
            }
        }
    });


    // 자연 키 사용
    const country = await prisma.country.create({
        data: {
            code: 'KR',
            name: '대한민국',
            continent: 'Asia'
        }
    });

    // 자연 키로 조회
    const korea = await prisma.country.findUnique({
        where: { code: 'KR' }
    });


    // PK 기반 관계 조회
    const userWithOrders = await prisma.user.findUnique({
        where: { id: user.id },
        include: {
            orders: true  // FK가 참조하는 관계
        }
    });


    // 대량 삽입 시 PK 반환
    const users = await prisma.user.createMany({
        data: [
            { email: 'user1@example.com', name: '사용자1' },
            { email: 'user2@example.com', name: '사용자2' }
        ],
        // skipDuplicates: true  // 중복 PK 무시
    });
    console.log('생성된 레코드 수:', users.count);


    // PK 존재 여부 확인
    const exists = await prisma.user.findUnique({
        where: { id: 999 },
        select: { id: true }  // PK만 조회 (효율적)
    });

    if (!exists) {
        console.log('사용자가 존재하지 않습니다');
    }
}

main()
    .catch(console.error)
    .finally(() => prisma.$disconnect());