Page 1 of 1

PK field not null is being null

Posted: Mon Nov 26, 2007 4:36 pm
by 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

Posted: Mon Nov 26, 2007 5:02 pm
by John Cartwright
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.

Code: Select all

Insert Tablename Values ('','','','','','');
'' is not the same as NULL.. NULL is NULL.

Code: Select all

Insert Tablename Values (NULL,'','','','','');
This would fail.

Also I might suggest you use the auto_increment attribute on the PK and set it to int since you generally don't want to be messing with the PK in INSERT queries anyway.
Do I have to/should use Integer type in PK field and can Varchar be incremented.
Yes, you should be using integer or similar data types that can be numerically incremented.
3.Can I set user privilege to a particular row (on a particular PK); HOW?
No.
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.
Yes you simply need to reference the right column in the join syntax.
5. How can I make a one-to-one relationship in MySQL;
This is done by using an INNER JOIN.. for example lets say we have 2 tables, people and profession.

people has the following columns

Code: Select all

id - INT(32), AUTO INCREMENT, NOT NULL
profession_id - INT(32)
name - VARCHAR(150)
profession has the following columns

Code: Select all

id - INT(32), AUTO INCREMENT, NOT NULL
title - VARCHAR(150)
We would create a 1-1 relationship by refering the profession table from the profession_id stored in the people table.

Code: Select all

SELECT people.name, profession.title 
FROM people
INNER JOIN profession ON people.profession_id = profession.id

Posted: Tue Nov 27, 2007 12:58 am
by tanvirtonu
JCART Brother very many thanx to you. Brother as u said that the following query will join the table and act as a one to one relationship-

Code: Select all

SQL:
SELECT people.name, profession.title
FROM people
INNER JOIN profession ON people.profession_id = profession.id
But isnt the query above is a Query.I mean not a DML/table definition language like CREATE TABLE...
Now I think I dont need to make a relationship in the FK table. Rather I can get the result by querying it the above way without making any reference during table creation. Am I right?

2.I want to make my PK VARCHAR bcos my PK 'client_id' is like'2007CL02' and unique. Now if I make my PK VARCHAR without INTEGER- Auto Increment , Can MySql find my PK quikly enough to retrieve the row I need or I have to index that PK-VARCHAR field for quick execution.

3. CAn I give the same Index name to more than one field? will there be any problem?

Thnx again .