MYSQL :: Turning Off Foreign Key Checks

Group With Two Date Checks
please accept apologies - post edited 23:19.

Query below successfully groups for records having greater than 1 results while joining to a State table:
State.StateAbbr | State.StateTerm
posting table:
JPosts2.JID | JPosts2.JState | JPosts2.Class
runtime table:
JRun.JRID | JRun.FK_JobID | JRun.StartRun | JRun.EndRun
SELECT JPosts2.JID, JRun.StartRun, JRun.EndRun, JRun.JRID,StateTerm, COUNT(JState)
FROM JPosts2
INNER JOIN State
ON State.StateAbbr=JPosts2.JState
INNER JOIN JRun ON JRun.FK_JobID =JPosts2.JobID
AND `JClass` = 'Mechanics'
GROUP StateTerm
HAVING count(JState) > 1
ORDER BY JState
------------------------------------------
QUESTION: Can the query be coaxed to return results for the same JState where there are both:
at least >0 expired and at least > 0 current date checks grouped by StateTerm for the given JClass?

...JRun.JRID =(SELECT max(JRun.JRID) from JRun
WHERE JRun.FK_JobID = JID) AND (CURRENT_DATE () > DATE_ADD(JRun.EndRun,INTERVAL 18 DAY)) // expired

...JRun.JRID =(SELECT max(JRun.JRID) from JobRun
WHERE JRun.FK_JobID = JID) AND JRun.StartRun BETWEEN DATE_SUB(JRun.EndRun,INTERVAL 18 DAY) AND CURRENT_DATE //current

MYSQL Health Checks
does any one have a list of dba type checks that they use for mysql?

Testing The Database? Backing Up, General Checks
I want to know what else I can be doing to check the stability of our database. Currently what I do every evening is download the complete database through PHPMyAdmin on our dedicated server, once downloaded I start up the Wamp Server and import the database locally through PHPMyAdmin on my own machine.

The next thing I do is run the Check Tables command on all the tables to see if everything is where it is. It always comes back as OK on each table so I presume everything is ok?

Is there anything else I should be doing, or is what I am doing correct?

Every so often we delete auctions to free up space in the database. Once this is done we have overhead in the tables. Sometimes this can be 2-10mb, so what I do then is run the optimise command on the tables, again am I correct in doing this?

Finally our database is now over 40mb, to me this massive as the largest database I have worked on before has been 4mb, please dont laugh.

What sort of database sizes have you seen in MySql or are you running at present? What is MySql like in the long run stability wise. If looked after can we get many years of good service from it.

Turning Mysql Off On Mac?
My friend is running OSX and has mySQL running, she doesn't know why its running or how it got there, how do you turn it off? What are the steps to go through?

Turning A Column Into A Row
Is there a was to take tableA with this data:
Letter ID
A 1
B 2
C 3

And insert it into TableB like this:

ABC

Turning On Logging
I have a database that has various information submitted to it from the internet. I am having problems with people hacking/submitting info that shouldn't be submitted. I don't know how they are doing it but I though that if I turned on the Logging for that particular DB maybe it would give me some clues i.e. IP addresses, ISP, date/time info, SQL Statement executed etc.What form of logging would I need to turn on?

Apostrophes Turning Into Question Marks?
This may be easily resolved but I haven't ever encountered it before. I am entering text into my mysql database that contains apostrophes. This field is generally around 500-800 characters. When I add the information, there are apostrophes but when I go back to use the information, all of the apostrophes have been changed into question marks.
Any ideas how to keep the apostrophes? or why they are being changed?
Also, if I go through and change all the question marks back to apostrophes, they will stay apostrophes!

Turning On Slow Query Logging?
Background: I paid a young admin set me up on a database server. He installed the basic I needed for the server...at my request...No Cpanel...mysql and apache and some tight security w/o even a domain name to SSH into. Unfortunately, he's a busy kid, and teens sometimes don't realize that people depend on them...and well, I can't really seem to get him to do much so I gave up and figured it's a good way to force me to learn all this myself...
Well anyway, now I want to turn on Slow Query logging. But before I do that, I need to know how MySQL is running. Is SQLogging turned on already? Where is it logging to? So first thing I want to look up is, when the server is rebooted, what's the command to restart mysql? No clue. How do I change the setting? And of course, the server is production, so when I make the change, it needs to be quick, it needs to be smooth, and I need to be able to roll back to the previous config if necessary.
I'm running Redhat Enterprise.

Turning Localhost To A Network Server
I have a small home network with two computers. On my computer I am running MySQL 4.1.21-community as localhost. I have only be using this for development work.

My husband wants to learn SQL so I would like to be able to have Mysql Query Browser installed on his machine, with him accessing all the databases I have set up on my machine.

How do I go about giving him access to my databases?

My machine runs Windows XP and his runs Windows 2000.

Turning Off Allowing Multiple Non-unique Rows
I feel lazy and I am busy so i don't want to fix my perl code to only alllow insertion of unique rows. I know mysql is set to allow multiple rows of the same thing but I would like to turn that feature off. Is that possible?

How To Universally Act On All Cells In A Table, Turning Blanks To NULLS
i should be able to do this, but it's actually been a pain for a while. how can i turn all the blanks in a table to NULLS?

Why Use A Foreign Key?
I am going to have a database that will have around 7 tables. Each table will have a UserID column that will be used as the Foreign Key (kind of). I will setup a primary key comprised of the UserID and an Auto Incremented field in each of the tables, and I plan on setting up indexing on the UserID field, all tables but 1 table can and will have duplicates of the UserID, it is how I am implemented my single to many infrastructure. I will be updating and inserting into all the tables at the same time. Some of my queries however may or may not use more than 1 table.

Anyways my question is should I specifically setup a Foreign Key? As I stated above I plan on using the UserID field in each table as a Foreign Key....rather a linking identifier, but should I actualy create a Foreign Key?

I Want To Add FOREIGN KEY How?
What is the right command to add a FOREIGN KEY to a field in a table?

Foreign Key
I add foreign key to my tables, but mysql seem to don't consider them.

For exemple the table BIERE (beer in english) :
CREATE TABLE BIERE
(
NOM VARCHAR(20) NOT NULL,
TYPEB VARCHAR(20) NOT NULL,
ORIGINE VARCHAR(20),
COTE VARCHAR(10) NOT NULL,
PRIMARY KEY(NOM),
FOREIGN KEY (TYPEB) REFERENCES TYPEBIERE (NOM) on delete cascade,
FOREIGN KEY (ORIGINE) REFERENCES ORIGINE (NOM) on delete cascade,
FOREIGN KEY (COTE) REFERENCES COTE (NOM) on delete cascade
);
So the foreign key typeb come from the table TYPEBIERE (white...brown..etc)

But if I do an insert of a beer and I type "Love" for a type of beer (Love isn't in TYPEBIERE) MySql don't show any error and add this beer to the table?

Foreign Key
I think I've finally figured out how to make a foeign key - you just copy some code into the "SQL query" box near the bottom of a table's Structure view page, right?

I thought I also had the right code to paste in, but it isn't working. Here are the stats:

Foreign Key
I've got a strange foreign key constraint problem in that when I try to do an insert into a table with some foreign keys, even though those keys reference valid fields in valid tables, I get the "cannot add or update...." [1216] error message. Code:

Foreign Key
i am able to do good with single db or table with all the queries. I like to learn how to work with multiple tables/dbs, particularly i what to learn seting foreign key?

Foreign Key
I try to create tables with foreign keys so that not to delete rows when linked to another row of another tables i try for example:

DROP TABLE IF EXISTS LIVRE;
CREATE TABLE LIVRE (
id_livre int(11) NOT NULL auto_increment,
id_genre int(11) NULL ,INDEX (id_genre),
CONSTRAINT `FK_LIVRE_GENRE` FOREIGN KEY (id_genre) REFERENCES genre(id_genre) ON DELETE RESTRICT
) TYPE=INNODB;

but i can delete a row from the table genre when exist a row in LIVRE that use it

Can someone give me the right syntax please ?

Foreign Key?
What problems would be there if I don't use foreign keys in stars_in_movies table when I select data? I feel it doesn't really matter. Anyone would guess future problems if I get rid of foreign keys from the stars_in_movies table?

movies
id:integer (primary key)
title:varchar(100)
year:integer
director:varchar(100)
banner_url:varchar(200)
trailer_url:varchar(200)

stars
id:integer (primary key)
first_name:varchar(50)
last_name:varchar(50)
dob:date
photo_url:varchar(200)

stars_in_movies
star_id:integer, foriegn key referencing stars.id
movie_id:integer, foreign key referencing movies.id

Foreign Key
I have made 2 tables in sql, these are the table names/fields:

product: product_no,name,price,stock
user: username,password

I now have to create another table called basket, with these 3 fields: username, itemno, quantity.

username and itemno are foreign keys to the 'user' and 'product' table, respectively.

So, my problem is how to create the this table with this foreign key thing, would it be:

CREATE table basket(username VARCHAR(20), itemno INT(4), quantity INt(4))

Thats a simple create table line, but what do i change in it to take into account the foreign key thing?

Foreign Key
can i use two foreign key constraints in one table. i.e can one table refer to two or more tables

Foreign Key
I'd like to create a table with PRIMARY KEY k (a,b,c) where a, b, c are
foreign key. Is it possible? How can I do?

Foreign Key
problem:

I´ve a table "A" with primary key (a1, a2), and a table "B" where the PK of A is propaged to as follow:

(b1,b2) --> A(a1,a2)

With this I get an error in MySql front.
I also tried with a sql sentence and I get an error again.

Maybe Mysql does not allow foreign keys of more than one field?

Foreign Key
i want to link PRIMARY KEY in one table as a FOREIGN KEY in an other.
I have applied "InnoDB" to both, and applying the relationship via phpmyAdmin.

When i try to enter data into the data base via some php. The Data doesn't seem to be arrive. Even thought the PHP returns say that it has been sent. If I take out the foreign key relationship, the data is sent to the tables.

Add A Foreign Key
The SQL statements below works fine if the line for the foreign key is omitted.
With the line included, I get this error message:

#1005 - Can't create table './marywhar_VegDb/test_fk.frm' (errno: 150)

I've also tried removing the constraint name and ON UPDATE and ON DELETE parameters - no difference.

Would someone please tell me what I'm doing wrong?

CREATE TABLE `levels` (
`id` tinyint(4) NOT NULL auto_increment,
`level` char(16) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=InnoDB AUTO_INCREMENT=5;

INSERT INTO `levels` VALUES (1, 'not specified');
INSERT INTO `levels` VALUES (2, 'low');
INSERT INTO `levels` VALUES (3, 'medium');
INSERT INTO `levels` VALUES (4, 'high');

CREATE TABLE `test_fk` (
`id` smallint(6) NOT NULL auto_increment,
`level` tinyint(4) default 1,
PRIMARY KEY (`id`),
CONSTRAINT `k_level` FOREIGN KEY (`level`) REFERENCES `levels`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) TYPE=InnoDB AUTO_INCREMENT=1;

Foreign Key
If I have 3 tables (for example Tables A, B and C) and the Primary Key for table A is a Foreign Key in table B, Do I need to show the Primary Keys of tables A AND B as Foreign Keys in table C if I wish to use data from Tables A and B, OR does the fact that the Primary key of Table A is a Foreign key in Table B mean that I can obtain the data from Table A via Table B (because of the Foreign Key) through the use of a query.

Foreign Key
how we can create a foreign key in mysql?

Foreign Key
I tried to create a new table with a foreign key but It doesnt work.. this is the code>
CREATE TABLE Groupe
(
id_groupe VARCHAR(5) ,
nbre_member INT UNSIGNED ,
PRIMARY KEY (id_groupe)
); //the groupe table is created

CREATE TABLE Student
(
code_student VARCHAR(10),
Name VARCHAR(10),
id_groupe VARCHAR(5),
PRIMARY KEY (code_student)
FOREIGN KEY (id_groupe) REFERENCES Groupe(id_groupe)
ON DELETE CASCADE
ON UPDATE CASCADE
); // error in SQL syntax.

Foreign Key
I have a customer table and a products table. The clientID is a foreign key inside the products table. If i edit the clientId inside the product table i get:

1451 - cannot delete or update a parent row; a foreign key constraint fails ('database01/customers', CONSTRAINT 'client_ibfk_1 'FOREIGN KEY('clientId') REFERENCES 'Products' ('clientId'))

any ideas ? :|

From the customers table : CONSTRAINT `client_ibfk_1` FOREIGN KEY (`clientId`) REFERENCES 'Products`

Foreign Key
when you create your tables and a table contains a foreign key does that key automatically become part of the primary key for that table ? Say the table that is its being mapped to already has a unique primary key.

Foreign Key
I'd like to create a table with PRIMARY KEY k (a,b,c) where a, b, c are
foreign key. Is it possible? How can I do?

Foreign Key
right i have two tables:

members:

Field Type Null Default
customer_id int(5) Yes NULL
fobnumber int(5) Yes 00000
firstname varchar(32) Yes NULL
lastname varchar(32) Yes NULL
emailaddress varchar(32) Yes NULL
access_id int(5) Yes NULL

Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 1 customer_id
access_id INDEX 1 access_id

userAccessDetails:

Field Type Null Default
id int(10) Yes NULL
username varchar(32) Yes NULL
password var(32) Yes NULL

Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 0 id

I am wanting to link userAccessDetails->id (PRIMARY KEY as a foreign key for members->access_id.

I have applied "InnoDB" to both, and applying the relationship via phpmyAdmin.

When i try to enter data into the data base via some php. The Data does seem to be arrive. even thought the PHP return that it has been sent. If i take out the foreign key relationship the data is sent to the tables.

Foreign Key
I have two tables, one created using innoDB while the other uses MyISAM. Can I build a foreign key across these two tables?

A Foreign Key
In mysql, does foreign key work this way : instead of deleting the child entries when the parent entry is deleted, delete the parent entry when ALL child entries refering to it are deleted ?

Foreign Key
If I have a table ,A, that have been link to other table
is there any command that can show all the table link to the Table?

Foreign Key
I am using XAMPP and MySql. I want to asssign foreign key constraint using phpmyadmin. I am quite conversant with Oracle and Mysql.what i did is i typed sql query in phpmyadmin sql window. when i try to see structure of the table it displays Index in Key column rather than Foreign key. Is it Correct?? Does it mean that foreign key cinstraint is applied??Is there any way to define Foreign key using phpmyadmin??

Foreign Key
How would one implement a dual foreign key, kind of like an "and".
for example

table a( field01, field02)

table b(field01, field02

foreign key "field01 AND field 02"

)

Foreign Key
I have created a relational DB. But when i go to insert data into a table which has 2 F keys in it...... ie, 2, 1-many relationships comin in, it says that
Cannot add or update a child row: a foreign key constraint fails (1216)

Foreign Key
I have a table that has columns with foreign keys to other tables. Table 1 relates to table2-5. Tables 2-5 rely on an auto incremented id number generated when i insert data into table 1.
So if I create a foreign key for table 2-5 on table 1 then I can't insert data into table 1 because of the foreign key constraints. However I can't insert data into tables 2-5 without the auto incremented value from table 1.
If I create a foreign key for table 1 on tables 2-5 then when I delete table 1 the delete will not cascade to tables 2-5.
How should I structure my foreign key relationships so I'm able to do this?

Foreign Key
I have a table that has 2 columns with foreign key referencing on on different table. i've been trying to create the foreign key but always get error. Help.

Table
Owner (ownerid, etc.)
Lot (lotid, etc.)
Payment (ownerid, lotid) <- both column referce on the table mentioned respectively

Foreign Key Constraint
I have the following 2 tables with a many 2 many relationship between them
Category TABLE and Member TABLE.

CREATE TABLE Category
(
CategoryId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
CategoryName VARCHAR(25)
);

CREATE TABLE Member
(
MemberId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
MemberName VARCHAR(75)
);

A lookup table connecting the above 2 tables is defined as
CREATE TABLE favouriteCategory
(
CategoryId INT NOT NULL,
MemberId INT NOT NULL
);

I tried defining a FOREIGN KEY Constraint after creating the favouriteCategory table as

ALTER TABLE favouriteCategory
ADD CONSTRAINT categoryId_FK
FOREIGN KEY CategoryId
REFERENCES Category(CategoryId);

MySQL gives me this error

You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE favouriteCategory
ADD CONSTRAINT categoryId_FK
FOREIGN KEY CategoryId
REFERE' at line 1

Could someone tell me what I am doing wrong?

How To Distinguish The FOREIGN KEY?
How do i know if the table has FOREIGN KEY specially in PHPMYADMIN?

And can i call two tables at once even though they are both don't have FOREIGN KEY?

Foreign Key In Another Database
I am building a web application on a server that offers multiple mysql databases, but each database has a maximum capacity of 100 MB.

I'd like to balance the load of data over several databases, however, many of my tables use foreign key constraints. I would like to know if it's possible in mysql to have a table with foreign key that references another table in a different database ? And if so, how this can be done ?

I have tried using databasename.tablename (for example: FOREIGN KEY (id) REFERENCES databasename.tablename (id)), but I get errno: 150

Do I Really Need Foreign Keys
I am currently designing a database that is to be used for ordering products from a site.

member, order-states, order, order-lines, product, review

I am considering using foreign keys between the tables. I am not sure if this would be the best way to do it or not.

Q1: If I use fk which tables need to be innodb, the ones containing the fk, the ones the fk are from or all of them.

Q2: If I were to delete a member do all related table entries get removed or does that only happen with the cascade keyword. This is important as you would still need a record of the transactions in the orders table.

Q3: What benefits are there to fk using innodb over myisam and simply storing the value from one table as a field in the other.

Hope these questions aren't to trivial and feel free to point me to tutorials. Thanks.

Foreign Key Value In SQL Query...?
Currently toying with this cart script...
http://www.zend.com/zend/spotlight/p...ping-cart4.php

At one stage when I get the contents of the cart this query:

PHP

SELECT inventory.product, inventory.price,
  shopping.quantity, inventory.description,
  SUM(inventory.price*shopping.quantity) as subtotal
  SUM(subtotal) as total
FROM shopping, inventory
WHERE session='SESSID' AND
  shopping.product = inventory.product
GROUP BY shopping.product

is generated by this:

PHP

function get_contents() {
   $count = 0;

   $query = "SELECT ".$this->inv_table.".product," .
          $this->inv_table.".price," .
          $this->cart_table.".quantity,".
          $this->inv_table.".description,
          SUM(".$this->inv_table.".price*" .
          $this->cart_table.quantity .") as subtotal,
          SUM(subtotal) as total
          FROM ".$this->cart_table.",".$this->inv_table."
          WHERE session='".$this->cart_id."' AND
          ".$this->cart_table.".product = ".
          $this->inv_table.".product GROUP BY ".
          $this->cart_table.".product";

      $result = mysql_query($query, $this->dblink);
    for($count = 0;
        array_push($contents, mysql_fetch_array($result));
        $count++);

    $contents['items'] = $count;

    return $contents;
}

My setup is a little different in that "inventory.price" is infact a foreign key to a "pricebands" table with 11 different price bands.

How can i get the correct price from my "pricebands" table within the MYSQL query??

In essence something like this...

PHP

SELECT inventory.product,
(SELECT pricebands.bndPrice WHERE inventory.price = pricebands.bndID) AS inventory.price,
  shopping.quantity, inventory.description,
  SUM(inventory.price*shopping.quantity) as subtotal
  SUM(subtotal) as total
FROM shopping, inventory
WHERE session='SESSID' AND
  shopping.product = inventory.product
GROUP BY shopping.product

Foreign Key Concept
I am new to MySQL.
In my project i make use of multiple tables having relation with each other as one-to-many.
A1 table has one to many relation with z1 table.
A2 table also has one-to-many relation with z1Table.
i.e.z1 table has 2 foreign keys in it.

How to index z1 table?
How to store data in z1 table when there are multiple foreign keys in it?

Foreign Keys - To Use Or Not To Use?
I'm developing a large app with more than 100 tables and expecting very large traffic. Performance is an issue for me. According to the MySQL doc, using Foreign Keys in InnoDB (which I'm using) will affect performance.

I'm wondering how much does performance suffer and is it worth it to NOT use FKs and let the app code handle the table dependencies at the risk of getting orphaned records?