Hi, I want to set a default value for a column based on the condition. The code works when the img datatype is set as VARCHAR. But I need INT. Any idea, please?
img INT DEFAULT (CASEE WHEN id=1 THEN NULL ELSE NOT NULL END)
That doesn’t make much sense. What is a default value of “NOT NULL” supposed to be? And why do you want the default value to be based on the id?
Since you’re asking quite a lot of weird questions here I’d suggest that you make yourself familiar with basic concepts of database design first. You may come to the conclusion that you either need something else than a RDBMS for persistence or rethink your concept of persistence in general.
Id is a column in the same table - it’s INT. IF id for a giver row is 1 then the entry for img is not required. Else is empty - null.
What you want is not a default value for the column but a conditional NOT NULL column constraint. That’s not possible in MySQL. You can achieve that with a table CHECK constraint. But seriously: Reconsider what you’re doing.
I’m not the expert but in my opinion a very good case would be: if id=1 is a post then you need to have a featured image, if id=2 is a page then I don’t care.
Since you have different names for the entities (“post” and “page”) they are probably different things that should probably have separate tables. Secondly it’s likely not a good idea to base such a business rule on the id. If you have a second post in the table that would be treated as a page…
There are about 40 defaults for field values and conditional default isnt one. It sounds like what you are trying to do belongs in code not in database design or your design needs to change perhaps to have another related table for image pointers where the contents cant be null.