Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

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 Nov 15 '12 at 7:26
add comment

1 Answer 1

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 Nov 15 '12 at 6:24
add comment

Your Answer

 
discard

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.