Help with Mysql database design

Question

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?

Answer 1

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.

FOREIGN KEY id REFERENCES listings(id)

becomes

FOREIGN KEY (id) REFERENCES listings(id)

Both have this issue.

Jacob

Answer 2

• Change the name of the Foreign key in the "residential" table.
• Ensure that the parent table exists.