Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 :)

share|improve this question
if you need the results that doesn't contain nulls you just need to specify a condition in where clause – Biju jose Nov 15 '12 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:) – Hasitha Shan Nov 15 '12 at 7:26
up vote 2 down vote accepted

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.

share|improve this answer
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 – Hasitha Shan Nov 15 '12 at 6:24

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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