0

I have a question about InnoDB and MyISAM storage engine of database.

Suppose we have 5 column in a table all column are set to "Not Null". When I run a query to insert values in only two column of MyISAM database, it wont generate any error, insert the record into table and left the other columns empty by default. But when I do the same with InnoDB database, it generate error that "somecolumn could not be null".

My all tables are set to MyISAM type but my database is InnoDB and I am having this issue.

Is there any way to resolve? I have already couple of solutions that either change the type of database to MyISAM, or set every column of all table to except "Null" value. I want to know the better solution than above mentioned.

Hope it does make any sense. :)

Haroon
  • 155
  • 2
  • 10
  • 2
    How to resolve it? Stop inserting NULL into columns that you've defined as NOT NULL. If you don't know the value when you do the insert, then the columns shouldn't be NOT NULL. – Alain Collins Oct 12 '12 at 18:37
  • Actually, application is already built and I need to go through each file to check the code and change according to your both suggestions. Please reconsider my question. Thanks, though. :) – Haroon Oct 12 '12 at 19:18
  • 1
    You might have to accept that fact that you will have to actually do some work to solve your problem. – bobwienholt Oct 13 '12 at 23:14

1 Answers1

2

Set a DEFAULT value for all of your NOT NULL columns and if you don't specify a value on INSERT, the default value will be used.

bobwienholt
  • 17,420
  • 3
  • 40
  • 48
  • Thank you for the response @bobwienholt. Sorry if you feel that but I am going to start arguing. :) The solution you suggest, again I need to go through every column of each table and this is what I don't want to do. Also, I can't set the default value of TEXT type column. Please reconsider my question. Thank you. – Haroon Oct 12 '12 at 19:17