PK field not null is being null

Discussion in 'MySQL' started by tanvirtonu, Nov 26, 2007.

0
  1. #1
    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
     
    tanvirtonu, Nov 26, 2007 IP
  2. flippers.be

    flippers.be Peon

    Messages:
    432
    Likes Received:
    4
    Best Answers:
    0
    Trophy Points:
    0
    #2
    1.
    there is a difference between NULL and an empty string

    NULL means you do not enter a value at all
    an empty sting '' or an integer column set to 0 are not NULL so they are allowed even if the column is defined as not null

    only solution to prevent this from being entered is to check it in your code that does the insert..


    4. you can name FK like you want - it is however encouraged to include the names of the columns where it links to, so you have a good overview of what fk's exist and what they do
     
    flippers.be, Nov 27, 2007 IP
  3. mariush

    mariush Peon

    Messages:
    562
    Likes Received:
    44
    Best Answers:
    0
    Trophy Points:
    0
    #3
    If you set the column NOT NULL, you would be able to do Insert Tablename Values ('','','','','',''); because you're inserting an empty string, not the value NULL.
    As the previous person said, the only way is to make sure in the code that you're not inserting an empty string, if this must be enforced.

    My advice is to download and install phpMyAdmin on your computer and use it to create databases, make some queries and see how phpMyAdmin builds the queries for each operation you perform on tables. It will get you used to the particularities of mySQL.
    If you don't know, phpMyAdmin is a graphic mySQL client that allows you to manage databases from a web browser.
     
    mariush, Nov 27, 2007 IP