I tried searching on this topic but I couldn't find any help on it. I want to write a CREATE TABLE query with SELECT having NOT NULL fields. The query I am using to create the table is as follows:

SELECT timeStamp, localIp, localport, localgeo, isp, foreignip, foreignPort,
COUNT(*) AS `countSamefPort`
FROM union_of_outbound_threats
GROUP BY timeStamp, localIp, localport, localgeo, isp, foreignip, foreignPort;

I want the fields to be set as NOT NULL. What extra parameters should I add? If this was addressed before please direct me to it :)

  • if you need the results that doesn't contain nulls you just need to specify a condition in where clause
    – Biju jose
    Commented Nov 15, 2012 at 6:52
  • the thing is i want the empty results, BUT need to specify the column constraint to NOT NULL on the new table:) Commented Nov 15, 2012 at 7:26

1 Answer 1


I don't see how MySQL could know anything about the content of your columns, unless you were basing the CREATE on another table ("LIKE ...").

It couldn't know anything about primary keys, default values, foreign keys, etc, right?

Looks like you're stuck doing an ALTER TABLE afterwards.

  • ohh..i seee :) thank you for pointing that out :) i thought it was possible as in specifying the constraints on the select statement itself for example :) thanks again Commented Nov 15, 2012 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.