55 外鍵及其四種約束講解 · Flask notebook

# 55 外鍵及其四種約束講解在MySQL中，外鍵可以讓表之間的關系更加緊密，而SQLAlchemy同樣支持外鍵。通過FpreignKey類來實現，并且可以指定表的外鍵約束。相關示例代碼如下: ```text from sqlalchemy import create_engine, Column, Integer, Text, String, DateTime, String, Float, func,and_,or_,ForeignKey class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) username = Column(String(50),nullable=False) class Article(Base): __tablename__ = "article" id = Column(Integer,primary_key=True,autoincrement=True) title = Column(String(50),nullable=False) content = Column(Text,nullable=False) # 外鍵 uid = Column(Integer,ForeignKey("user.id")) Base.metadata.drop_all() Base.metadata.create_all() ``` 外鍵約束有以下幾項: 1. RESTRICT\(restrict\):父表數據被刪除，會阻止刪除 2. NO ACTION:在MySQL中，同RESTRICT 3. CASCADE:級聯刪除 4. SET NULL:父類數據被刪除，子表數據設置為NULL ```text from sqlalchemy import create_engine, Column, Integer, Text, String, DateTime, String, Float, func,and_,or_,ForeignKey from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker import random HOSTNAME = '127.0.0.1' PORT = "3306" USERNAME = "root" PASSWORD = "123456" DATABASE = "xt_flask" DB_URI = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(USERNAME,PASSWORD,HOSTNAME,PORT,DATABASE) from datetime import datetime engine = create_engine(DB_URI) Base = declarative_base(engine) session = sessionmaker(engine)() # 父表/從表 # user/article class User(Base): __tablename__ = 'user' id = Column(Integer,primary_key=True,autoincrement=True) username = Column(String(50)) class Article(Base): __tablename__ = "article" id = Column(Integer,primary_key=True,autoincrement=True) title = Column(String(50),nullable=False) content = Column(Text,nullable=False) # 外鍵，沒有指定，就默認為RESTRICT # RESTRICT:阻止刪除數據 # uid = Column(Integer,ForeignKey("user.id",ondelete="RESTRICT")) # 級聯刪除 # uid = Column(Integer, ForeignKey("user.id", ondelete="CASCADE")) # 只有父表被刪除，子表修改為NULL uid = Column(Integer, ForeignKey("user.id", ondelete="SET NULL")) Base.metadata.drop_all() Base.metadata.create_all() user = User(username = 'angle') session.add(user) session.commit() article = Article(content='abc',title='123',uid=1) session.add(article) session.commit() ```