[solved]help with testing

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

[solved]help with testing

Post by cybershot »

I took a database class not to long ago that used access and relational databases to do the work. Most of it made sense. I am use to setting primary keys and foreign keys like so

nameID - primary key
firstName
lastName

phoneID - primary key
homePhone
CellPhone
nameID - foreign key

I am trying a test database in phpMyAdmin by setting up the database just like the above. I am having trouble setting the foreign key. It won't work. can anyone offer any advice on this issue? I would think to test, it would work like this. My sql is not that good

SELECT * FROM names, phone
WHERE nameID = phoneID

my thought is that it will give everything in the names table and phone table bases on the primary keys in both tables?
Last edited by cybershot on Sun Nov 28, 2010 11:40 pm, edited 1 time in total.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: help with testing

Post by McInfo »

First, make sure your tables use the InnoDB storage engine (not the default MyISAM).

Fields used for foreign keys must be indexed; so, create indexes on fields that are not already indexed before defining foreign key constraints on them.

In phpMyAdmin, when a table is selected, on the "Structure" page, there should be a "Relation view" link. Click that and you should be able to define the foreign keys for the table.
cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

Re: help with testing

Post by cybershot »

I found the relational view but I am having trouble getting it to work. I started over and made a new table with the same information as above.

names - table
id - primary key
firstName
lastName

phone - table
id - primary key
homePhone
cellPhone
namesID - foreign key.

Here my thought is that the namesID foreign key in the phone table links to the id primary key in the names table. I had to go back to the structure tab for the phone table and click the button with the lightning bolt that says index. I did this on the namesID field to give the field an index, then I went into the relational view and selected testing.names.ID as the key to the phone table. so it looks like this

namesID - id ON DELETE ON UPDATE

I left the on delete and on update empty

anyway, I get an error that says

Warning: #1366 Incorrect integer value: '' for column 'namesID' at row 1
cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

Re: help with testing

Post by cybershot »

I get the error above when I try to add data to the table. I can add the names just fine but when I switch to the phone table and use the insert tab to put the phone data in, the error pops up after I click save
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: help with testing

Post by McInfo »

Please, show the INSERT query that causes the error. Also, export the structure of the two tables in SQL format. I would like to see the "CREATE TABLE" and "ALTER TABLE" queries.
cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

Re: help with testing

Post by cybershot »

Code: Select all


-- phpMyAdmin SQL Dump
-- version 3.1.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Nov 28, 2010 at 08:50 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `myData`
--

-- --------------------------------------------------------

--
-- Table structure for table `names`
--

CREATE TABLE IF NOT EXISTS `names` (
  `namesID` int(11) NOT NULL auto_increment,
  `firstName` text NOT NULL,
  `lastName` text NOT NULL,
  PRIMARY KEY  (`namesID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Dumping data for table `names`
--

INSERT INTO `names` (`namesID`, `firstName`, `lastName`) VALUES
(1, 'test', 'name');

-- --------------------------------------------------------

--
-- Table structure for table `phone`
--

CREATE TABLE IF NOT EXISTS `phone` (
  `phoneID` int(11) NOT NULL auto_increment,
  `homePhone` text NOT NULL,
  `cellPhone` text NOT NULL,
  `namesID` int(11) NOT NULL,
  PRIMARY KEY  (`phoneID`),
  KEY `namesID` (`namesID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Dumping data for table `phone`
--


--
-- Constraints for dumped tables
--

--
-- Constraints for table `phone`
--
ALTER TABLE `phone`
  ADD CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`namesID`) REFERENCES `names` (`namesID`) ON DELETE CASCADE;


//this is the sql insert query
INSERT INTO `myData`.`phone` (
`phoneID` ,
`homePhone` ,
`cellPhone` ,
`namesID`
)
VALUES (
NULL , '555-555-5555', '444-555-5555', ''
)


MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`mydata/phone`, CONSTRAINT `phone_ibfk_1` FOREIGN KEY (`namesID`) REFERENCES `names` (`namesID`) ON DELETE CASCADE) 
after I create the database, I make the foreign key relationship then I use the insert tab to insert the names which works fine then I go to the phone table click the insert tab and type in the phone data and that is when I get the error
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: help with testing

Post by McInfo »

Because namesID is defined as NOT NULL, something must be stored in it. Either edit the field and check the "Null" box to allow a NULL value or always specify a numeric value when inserting new rows.
cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

Re: help with testing

Post by cybershot »

you mean because the namesID foreign key field is null? I think that would make sense. I will try that
cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

Re: help with testing

Post by cybershot »

that worked, the error is gone. what sql query can I use to test this? I was thinking

SELECT * FROM names, phone
WHERE
namesID = phoneID

but this does not work. My thought was to select everything where the primary key matches the foreign key

Thanks for your help
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: help with testing

Post by McInfo »

Both tables have a field called namesID. You need to disambiguate by specifying which namesID field you mean. Instead of "namesID", use "names.namesID".
cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

Re: help with testing

Post by cybershot »

yep, I discovered that just now
I used this query

SELECT *
FROM NAMES , phone
WHERE names.namesID = phone.phoneID

and that got me back the results I was expecting but in this situation, the foreign key becomes useless. I selected everything by the primary keys. So now I am wondering if the foreign key is needed since the value of the field now is null
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: [solved]help with testing

Post by McInfo »

Foreign keys are good for data integrity. If you update or delete a row from one table, the database can automatically delete or update related rows in another table.
cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

Re: [solved]help with testing

Post by cybershot »

right, but i take that to mean that the primary key would have a value of 1 and so would the foreign key in the child table
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: [solved]help with testing

Post by McInfo »

Are you talking about the fact that a NULL is not related to a value in the other table? If that's a problem, just always specify a value other than NULL when inserting or updating.
cybershot
Forum Commoner
Posts: 29
Joined: Thu Jul 24, 2008 12:06 pm

Re: [solved]help with testing

Post by cybershot »

I am thinking from the program microsoft access where the foreign key actually holds information. Where the foreign key links data to the parent table as well as forces referential integrity for things like deleting data. So right now the issue becomes this. The way my table is set if I add five names without adding phone numbers, then the keys do not match up. So when I do this

SELECT *
FROM NAMES , phone
WHERE names.namesID = phone.phoneID

the data it returns doesn't match the names. So I thought the foreign key would auto increment as well so that you could select data based on the keys so that it would return the information based on those numbers. Does php not work this way? do you have to select the data you want manually?
Post Reply