帮助Mysql数据库设计
我有以下实体：住宅列表、商业列表、租赁列表和特色列表，其中包含每个用户的特色属性。最合乎逻辑的似乎是利用超类型/子类型方法并创建以下表：
CREATE TABLE listings (
id ...,
listPrice ...,
...
PRIMARY KEY (id)
)
CREATE TABLE residential (
id ...
...
PRIMARY KEY (id),
FOREIGN KEY id REFERENCES listings(id)
)
CREATE TABLE featuredListings (
id ..,
userId ...,
featuredListingId ...
PRIMARY KEY (id),
FOREIGN KEY featuredListingId REFERENCES listings(id)
)
但是当我尝试创建住宅表时，Mysql 返回错误“MySQL 错误号 1005。无法创建表‘住宅’（errno：150）”
也许是我的 DDL是不正确的。或者MySQL中是否有可能同时存在PK和FK？如果不是，这种情况下的最佳设计是什么？
I have following entities: residential listings, commercial listings, lease listing and featured listings which holds featured properties for each user. The most logical seems to leverage supertype/subtype approach and create following tables:
CREATE TABLE listings (
id ...,
listPrice ...,
...
PRIMARY KEY (id)
)
CREATE TABLE residential (
id ...
...
PRIMARY KEY (id),
FOREIGN KEY id REFERENCES listings(id)
)
CREATE TABLE featuredListings (
id ..,
userId ...,
featuredListingId ...
PRIMARY KEY (id),
FOREIGN KEY featuredListingId REFERENCES listings(id)
)
But when i try to create residential table, Mysql returns error "MySQL Error Number 1005. Can't create table 'residential' (errno: 150)"
Maybe my DDL is incorrect. Or is it even possible to have PK which in the same time is FK in MySQL? If not, what is the best design for such scenario?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
根据您在 MySQL 中使用的引擎，不支持外键。如果你使用 INNODB，它们是受支持的，尽管我不确定 id 是否可以是 FK。我不明白为什么不。
如果您使用的是 MYISAM FK，则不支持。
还有一种可能会失败，因为外键的列名不在括号内。
变成
两者都有这个问题。
雅各布
Depending on the engine you are using in MySQL, foreign keys are not supported. If you are using INNODB they are supported although I am not sure if id can be an FK. I cannot see why not.
If you are using MYISAM FKs are not supported.
There is also a chance that it is failing because the column names of the foreign key are not inside parenthesis.
becomes
Both have this issue.
Jacob