Page 1 of 2
[solved]help with testing
Posted: Sat Nov 27, 2010 11:12 pm
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?
Re: help with testing
Posted: Sun Nov 28, 2010 2:14 am
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.
Re: help with testing
Posted: Sun Nov 28, 2010 9:00 pm
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
Re: help with testing
Posted: Sun Nov 28, 2010 10:32 pm
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
Re: help with testing
Posted: Sun Nov 28, 2010 10:44 pm
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.
Re: help with testing
Posted: Sun Nov 28, 2010 10:53 pm
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
Re: help with testing
Posted: Sun Nov 28, 2010 11:19 pm
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.
Re: help with testing
Posted: Sun Nov 28, 2010 11:29 pm
by cybershot
you mean because the namesID foreign key field is null? I think that would make sense. I will try that
Re: help with testing
Posted: Sun Nov 28, 2010 11:32 pm
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
Re: help with testing
Posted: Sun Nov 28, 2010 11:37 pm
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".
Re: help with testing
Posted: Sun Nov 28, 2010 11:39 pm
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
Re: [solved]help with testing
Posted: Sun Nov 28, 2010 11:54 pm
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.
Re: [solved]help with testing
Posted: Sun Nov 28, 2010 11:58 pm
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
Re: [solved]help with testing
Posted: Mon Nov 29, 2010 12:11 am
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.
Re: [solved]help with testing
Posted: Mon Nov 29, 2010 12:15 am
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?