-2

I thought foreign key wont matter for me but now i think i have to adopt. i have over 20 tables with many to many relationships which serves a blog website.

table: gateway (post_id, group_id, post_type, post_date ...)

table: post (post_id, group_id, title, body ...)

table: poll (post_id, group_id, title, ...)

and like wise table: video, photo, link tables; and then they have table: to track like/unlike records and user comment records -- which all shares a common field post_id var(14)

at this point i think it would be better to reference them with foreign keys. in this example i have 8 tables which can be linked with post_id, so i tried FK between post & gateway [on delete: cascade, on update: no action(InnoDB)] now when i try other table to ref. FK to gateway or post it throws an error and i dont know why? definitely i am wrong somewhere.

Kindly help me if my approach is right or is there a better way?

This is the error message I get:

Image displaying the error

Mr. Radical
  • 1,847
  • 1
  • 19
  • 29
Debraj Debbarma
  • 77
  • 1
  • 1
  • 6
  • Could you post the error you are getting? While you at it post the table structure and some sample as well. B.T.W. a common error with foreign keys is that you use a different field type in your tables. – Mr. Radical Feb 10 '13 at 13:02
  • I took a look at your previous posts. Before we move on; Will you give feedback / accept answer and /or up vote this time? – Mr. Radical Feb 10 '13 at 13:09
  • if i may post an external like on comment - [http://eborok.com/error.jpg] one more point i want to add is post_id in those tables are pre fixed with a letter and upderscore (e.g. p_post_id) would it be a problem? – Debraj Debbarma Feb 10 '13 at 13:25
  • Sorry Mr. Radical for no feedback / accept answer and /or vote. coz when i click it says you Vote Up requires 15 reputation -- what shell i do then :-( ?? – Debraj Debbarma Feb 10 '13 at 13:27
  • it is not just my answer. http://stackoverflow.com/questions/14682448/how-to-get-the-total-row-count-with-mysqli You clear state that your question is solved in a comment but you don't accept it. – Mr. Radical Feb 10 '13 at 13:29
  • Post your table structure and some sample data in http://www.sqlfiddle.com – Mr. Radical Feb 10 '13 at 13:30
  • Try setting the foreign key to index and make sure you are using the correct name and using the same column type. http://stackoverflow.com/questions/825362/mysql-error-150-foreign-keys – Mr. Radical Feb 10 '13 at 13:34
  • yes now i know how to accept answers. that right sign bellow the post numbers serves acceptance was unknown to me. – Debraj Debbarma Feb 10 '13 at 13:37
  • Are both tables using the same engine (InnoDB)? – Mr. Radical Feb 10 '13 at 13:41
  • ah!! so silly - my mistake datatype was set to int. took a cup of coffee then i spot the problem. Thanks for your time Mr. Radical. next time on wards i will take attention to accept/vote. take care. – Debraj Debbarma Feb 10 '13 at 13:47
  • Glad that I was able to help. – Mr. Radical Feb 10 '13 at 13:51

1 Answers1

0

Try setting the foreign key to index and make sure you are using the correct name and using the same column type.

Mr. Radical
  • 1,847
  • 1
  • 19
  • 29