1

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:

CREATE TABLE tbl_tsLiLpLgIspFiFp
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 :)

Improve this question
2
  • if you need the results that doesn't contain nulls you just need to specify a condition in where clause
    – Biju jose
    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:)
    – Hasitha Shan
    Nov 15, 2012 at 7:26

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • 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, 2012 at 6:24

Your Answer

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

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