定义外键的（列类型）

I'm Designing a database using MySQL , Right now am in the phase of documenting in which I have to Define every Column's data type . when it comes to each table on its own , there's no problem , but I have problem with 1-m Relationships , because I don't know how to Define the Foreign key's Data type ( because in its original table its type is "SERIAL" - coz its Primary Key ) But I can't Define it as SERIAL when it's FK . Can You Help Me ?

我正在使用MySQL设计数据库,现在正处于文档化阶段,我必须定义每个列的数据类型。当谈到每个表本身时,没有问题,但我有1米关系的问题,因为我不知道如何定义外键的数据类型(因为在其原始表中它的类型是“SERIAL” - 因为它是主键)但是当它是FK时我不能将它定义为SERIAL。你能帮助我吗 ?

EX : PUBLISHERS [ Publisher_ID ( SERIAL ) | Publisher_Name ( Varchar(50) ) | .. etc]

EX:PUBLISHERS [Publisher_ID(SERIAL)| Publisher_Name(Varchar(50))| ..等]

Books [ Book_ID (SERIAL) | Publisher_ID (?) | Book_Price ( Decimal (5,2) ) | .. etc ]

书籍[Book_ID(SERIAL)| Publisher_ID(?)| Book_Price(十进制(5,2))| ..等]

thanks

1 个解决方案

#1

SERIAL in MySql is just an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. Therefore use BIGINT UNSIGNED when defining FK column, like this

MySql中的SERIAL只是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名。因此在定义FK列时使用BIGINT UNSIGNED,就像这样

CREATE TABLE publishers(
publisher_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
publisher_name VARCHAR(50),
PRIMARY KEY(publisher_id)
);

CREATE TABLE books(
book_id INT NOT NULL AUTO_INCREMENT,
publisher_id BIGINT UNSIGNED,
title VARCHAR(100),
book_price DECIMAL(5,2),
PRIMARY KEY (book_id),
CONSTRAINT FOREIGN KEY (publisher_id) REFERENCES publishers (publisher_id)
);

SQLFiddle example