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
PK field not null is being null
Moderator: General Moderators
-
tanvirtonu
- Forum Commoner
- Posts: 35
- Joined: Wed Oct 17, 2007 9:15 am
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
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 ('','','','','','');Code: Select all
Insert Tablename Values (NULL,'','','','','');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.
Yes, you should be using integer or similar data types that can be numerically incremented.Do I have to/should use Integer type in PK field and can Varchar be incremented.
No.3.Can I set user privilege to a particular row (on a particular PK); HOW?
Yes you simply need to reference the right column in the join syntax.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.
This is done by using an INNER JOIN.. for example lets say we have 2 tables, people and profession.5. How can I make a one-to-one relationship in MySQL;
people has the following columns
Code: Select all
id - INT(32), AUTO INCREMENT, NOT NULL
profession_id - INT(32)
name - VARCHAR(150)Code: Select all
id - INT(32), AUTO INCREMENT, NOT NULL
title - VARCHAR(150)Code: Select all
SELECT people.name, profession.title
FROM people
INNER JOIN profession ON people.profession_id = profession.id-
tanvirtonu
- Forum Commoner
- Posts: 35
- Joined: Wed Oct 17, 2007 9:15 am
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-
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 .
Code: Select all
SQL:
SELECT people.name, profession.title
FROM people
INNER JOIN profession ON people.profession_id = profession.idNow 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 .