MySQL主键和外键使用及说明
摘自网上一个经典的例子：大哥和小弟
一、外键约束
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。
外键的使用条件：
1.两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；
2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；
3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；
外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；
外键的定义语法：
[constraint symbol] foreign key [id] (index_col_name, ...)
references tbl_name (index_col_name, ...)
[on delete {restrict | cascade | set null | on action | set default}]
[on update {restrict | cascade | set null | on action | set default}]
该语法可以在 create table 和 alter table 时使用，如果不指定constraint symbol，MYSQL会自动生成一个名字。
on delete,on update表示事件触发限制，可设参数：
restrict（限制外表中的外键改动）
cascade（跟随外键改动）
set null（设空值）
set default（设默认值）
no action（无动作，默认的）
1. 先建立1个新的数据库
2.在pycharm中新建2张table（Dage,Xiaodi）
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey
engine=create_engine("mysql+pymysql://root:1234@localhost/chen")
Base=declarative_base()
class Dage(Base):
__tablename__='Dage'
id=Column(Integer,primary_key=True)
name=Column(String(32))
class Xiaodi(Base):
__tablename__='Xiaodi'
id=Column(Integer,primary_key=True)
name=Column(String(32))
Dage_id = Column(Integer,ForeignKey('Dage.id'))
Base.metadata.create_all(engine)
在客户端show table已经创建过程
show create table xiaodi;
----------------------------------------------------------------------+
| xiaodi | CREATE TABLE `xiaodi` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL,
`Dage_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Dage_id` (`Dage_id`),
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`Dage_id`) REFERENCES `dage` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
show create table dage;
3.在2张表中各插入1条数据。由于不知名的错误，需要把2个表中的数据分开创建。
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String,ForeignKey
from sqlalchemy.orm import sessionmaker
engine=create_engine("mysql+pymysql://root:1234@localhost/chen")
Base=declarative_base()
class Dage(Base):
__tablename__='Dage'
id=Column(Integer,primary_key=True)
name=Column(String(32))
class Xiaodi(Base):
__tablename__='Xiaodi'
id=Column(Integer,primary_key=True)
name=Column(String(32))
Dage_id = Column(Integer,ForeignKey('Dage.id'))
Base.metadata.create_all(engine)
Session_class=sessionmaker(bind=engine)
session=Session_class()
#dage1=Dage(name='I_am_dage')
xiaodi1=Xiaodi(Dage_id=1,name='I_am_xiaodi')
session.add_all([xiaodi1])
session.commit()
查看效果：发现这种创建方式有个问题，id会自增，2张表的ID依次为1,2
4.尝试删除大哥
删除不了，因为有外键约束
插入1个小弟，因为没有大哥，所以插入不成功
5.把外键约束增加事件触发限制：
alter table xiaodi drop foreign key xiaodi_ibfk_1;
alter table xiaodi add foreign key(Dage_id) references dage(id) on delete cascade on update cascade; #意思是从表会跟随主表的改变而改变。
理论上，现在就能正常删除了。
二，多外键关联
建立一个customer表和一个地址表
表结构：
from sqlalchemy import Integer, ForeignKey, String, Column
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
Base = declarative_base()
class Customer(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True)
name = Column(String(64))
# 账单地址和邮寄地址 都关联同一个地址表
billing_address_id = Column(Integer, ForeignKey("address.id"))
shipping_address_id = Column(Integer, ForeignKey("address.id"))
billing_address = relationship("Address", foreign_keys=[billing_address_id])
shipping_address = relationship("Address", foreign_keys=[shipping_address_id])
class Address(Base):
__tablename__ = 'address'
id = Column(Integer, primary_key=True)
city = Column(String(64))
def __repr__(self):
return self.street
engine = create_engine("mysql+pymysql://root:1234@localhost/chen",
encoding='utf-8')
Base.metadata.create_all(engine) # 创建表结构
生成表内容：
from Day12 import ex5 from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind=ex5.engine) # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 session = Session_class() # 生成session实例 #cursor addr1 = ex5.Address(city="BJ") addr2 = ex5.Address(city="Shanghai") addr3 = ex5.Address(city="Tianjin") session.add_all([addr1, addr2, addr3]) c1 = ex5.Customer(name="Alex", billing_address=addr1, shipping_address=addr2) c2 = ex5.Customer(name="Jack", billing_address=addr3, shipping_address=addr3) session.add_all([c1, c2]) session.commit()
效果：
查询：
obj=session.query(ex5.Customer).filter(ex5.Customer.name=='Alex').first() print(obj.name,obj.billing_address,obj.shipping_address)
返回结果：
C:\abccdxddd\Oldboy\python-3.5.2-embed-amd64\python.exe C:/abccdxddd/Oldboy/Py_Exercise/Day12/ex4.py
Alex BJ Shanghai
Process finished with exit code 0
三，多对多
1.创建表结构
import sqlalchemy
from sqlalchemy import Table, Column, Integer,String,DATE, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
Base=declarative_base()
book_m2m_author=Table('book_m2m_author',Base.metadata,
Column('book_id',Integer,ForeignKey('books.id')),
Column('author_id',Integer,ForeignKey('authors.id')),
)
class Book(Base):
__tablename__='books'
id=Column(Integer,primary_key=True)
name=Column(String(64))
authors=relationship('Author',secondary=book_m2m_author,backref='books')
def __repr__(self):
return self.name
class Author(Base):
__tablename__='authors'
id=Column(Integer,primary_key=True)
name=Column(String(32))
def __repr__(self):
return self.name
engine=create_engine('mysql+pymysql://root:1234@localhost/chen')
Base.metadata.create_all(engine)
2. 增加表内容
# 添加数据 from Day12 import ex4 from sqlalchemy.orm import sessionmaker Session_class = sessionmaker(bind=ex4.engine) # 创建与数据库的会话session class ,注意,这里返回给session的是个class,不是实例 session = Session_class() # 生成session实例 #cursor # 创建书 b1 = ex4.Book(name="learn python with Alex") b2 = ex4.Book(name="learn Zhangbility with Alex") b3 = ex4.Book(name="Learn hook up girls with Alex") # 创建作者 a1 = ex4.Author(name="Alex") a2 = ex4.Author(name="Jack") a3 = ex4.Author(name="Rain") # 关联关系 b1.authors = [a1, a3] b3.authors = [a1, a2, a3] session.add_all([b1, b2, b3, a1, a2, a3]) session.commit()
查看效果：
3. 通过作者查询书
4.通过书查作者
