Change column properties 1

[EagleM]

1. I have a table "students" with id, fname, mname, and lname columns. The 'mname' column can accept NULL.

The Submit button on the form was clicked a number of times, and because there was no input validation, I have records with nothing for fname and lname, and NULL for mname. I want to prune the table of these empty records.

I can delete records with no lname, but how would I test for empty? It's not NULL -- just empty. I was expecting the INSERT to return an error if a column doesn't accept NULL...

2. Also, can column properties be changed? For example, can I make a column that doesn't accept NULL to start accepting NULL? Can I add or remove other properties for an existing column? I am not sure how to plan a database, so I want to be able to alter it as the need arises. Will it always require making a new column and deleting the old one?

3. Is there a short tutorial or guide that helps plan for a database. The are a ton that help you create a simple basic DB, but I'm looking for one that will help me plan one right the first time.

Thanks

 

[dagger2002]

How are you accessing the mySQL DB?

 

[vacunita]

Most GUI's will allow you to do it with minimum effort, what are you using to access the DB, the command line prompt?

Second To alter a table the ALTER command is pretty much self explanatory. If I were you I would familiarize myself with mysql's online manual.

http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

"ALTER TABLE mytable MODIFY mycolumn ...."

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[EagleM]

I have the command prompt and PHP (which basically runs the same sql queries as the command prompt).

I did look at the ALTER TABLE in the manual, but it wasn't light reading and didn't have examples of code.
So I guess it's:

 ALTER TABLE students MODIFY mname NOT NULL

and this will modify the field 'maname' to not accept NULL.

So how can I test for the empty value? Does empty value mean an empty string, and I can do the following:

DELETE FROM students WHERE lname = '' ?

 

[vacunita]

First I Suggest you get a GUI. something like SQLyog. That will make your life easier when changes need to be made to the DB.

Yes that is the correct way of checking for empty strings.






----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.