PK field not null is being null

[tanvirtonu]

I m newbie. I made a MySQL database table and in the primary key field I set the data type VAR CHAR.I also set it not null .BUT still I can insert blank values in this PRIMARY KEY field. If I just blank my primary key field by this query- Insert Tablename Values ('','','','','',''); (if my table has 6 fields).HOW IS IT POSSIBLE. How can PK field allow blank value when I set not null. HOW TO SOLVE IT.

2. I use a MySQL GUI Tools from http://dev.mysql.com/downloads/. In Table viewer of Query browser, each time I insert the first field, it sets the datatype to INTEGER,NOT NULL, AUTO INCREMENT. But I change it to VARCHAR as needed. Do I have to/should use Integer type in PK field and can Varchar be incremented.

3.Can I set user privilege to a particular row (on a particular PK); HOW?

4. Is it correct/incorrect that the name of the foreign key has to be the same as the name of the corresponding PK. I saw that in MySQL I can easily change the FK name to anything else and then just make a reference of FK to the PK.

5. How can I make a one-to-one relationship in MySQL;

Pls give me those answers. THANX

[Fumigator]

1. NULL is not the same thing as empty. To keep from having an empty value you'll have to validate before you insert into the table (via your application). It may not be an issue for you, keeping in mind your primary key must be unique so there can only be one row in your table with an empty key. (Is that a big deal? I think not)

2. You don't need an integer auto-increment primary key. It's just an easy way to guarantee a unique key is all. If your varchar primary key suits your needs, then it's fine to use it.

3. You can create a "view" of your table that includes only the rows you want to be accessable, then restrict access to just that view.

4. The name of your foreign key does not have to be the same as the key it relates to, but that is a pretty good way of keeping things easy to understand.

5. one-to-one? Put a foreign key in the 2nd table and force it to be unique.

[felgall]

NULL is what you get when you don't pass a value at all. Passing a zero length string is NOT NULL because it is a value that you passed and anything you pass to the field is not null by definition. Only by omitting the field from the insert statement completely can you set a field to NULL.

[Fumigator]

You can also use the keyword NULL to assign NULL to a column.