|
|
Posted by Andreas Edin on 11/06/05 10:12
> Jim Hernandez wrote:
>
> I'm having a little trouble setting up the tables so they'll
> communicate with each other. I'm using Navicat. I've got the two
> tables set up, on the Tracklist table I have album id, tracknumber
> and trackname.
>
> albumid on Table 2 is a foreignkey i have set linked to the albumid
> (primary key) on Table 1. The foreignkey is then linked to albumid
> on table 2 as a primary key as well. When I try to add another
> record for album id=1 I recieve an error saying that more than one
> entry is being put in for albumid 1 and it won't take the change.
>
> Did I set up the tracklist table incorrectly?
>
> Thanks so much for all your help by the way.
It seems to me that you have set the albumid as an uniqe field in
table2. If so you wouldn't be able to add more than one record i table2
with id number 1. Here is an example how you can create your tables
with primary keys, foreign keys and some index to speed up the query:
CREATE TABLE `table1` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
`album` VARCHAR(64) NOT NULL COMMENT 'Album',
`title` VARCHAR(128) NOT NULL COMMENT 'Title',
`artist` VARCHAR(64) NOT NULL COMMENT 'Artist',
`label` VARCHAR(64) NOT NULL COMMENT 'Label',
INDEX `Index_2`(`album`),
PRIMARY KEY(`id`)
)
ENGINE = MYISAM
AUTO_INCREMENT = 1
COMMENT = 'AlbumTable';
CREATE TABLE `table2` (
`id` INTEGER UNSIGNED NOT NULL COMMENT 'Id field correspondent to
table1',
`tracknumber` INTEGER UNSIGNED NOT NULL COMMENT 'Order of track
within the album',
`trackname` VARCHAR(255) NOT NULL COMMENT 'Name of the track',
INDEX `Index_1`(`id`, `tracknumber`),
CONSTRAINT `FK_table2_1` FOREIGN KEY `FK_table2_1` (`id`)
REFERENCES `table1` (`id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT
)
ENGINE = MYISAM
AUTO_INCREMENT = 1
COMMENT = 'MetaData for albums'
Feel free to ask more questions Jim. I'll do my best to answer them.
Best regards Andreas Edin, Sweden.
--
Navigation:
[Reply to this message]
|