need help from vincent

Anon

This continues from yesterdays talk about innodb tables: yes they do exist(see below). thats what is confusing me! I could have put the whole of my code in but i thought u experts would think i was trying to get you to debug my code! anyway this is what i have and the last table does not work! ps vincent r u from the US? thanks if you can assist me! I'm about to start a career in the IT industry and was wondering what good jobs are available/ pay well! I currently live in london. cheers!

CREATE TABLE CLIENTS
( CID INT UNISGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CMPNYNAME VARCHAR(20),
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20),
POSTCODE VARCHAR(10) NOT NULL,
EMAIL VARCHAR(20),
CONTACT_NO INT ZEROFILL NOT NULL
)
TYPE=INNODB;

CREATE TABLE ARTISTS
( MEMBERID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20),
POSTCODE VARCHAR(10) NOT NULL,
DOB DATE NOT NULL,
EMAIL VARCHAR(20),
CONTACT_NO INT ZEROFILL NOT NULL,
STAGENAME VARCHAR(15),
EXPRNCE_IN_YEARS INT UNSIGNED NOT NULL,
SEX CHAR(1) NOT NULL,
NI_NO VARCHAR(12) NOT NULL
)
TYPE=INNODB;

CREATE TABLE SPECIALIST_AREA
( MEMBERID INT UNSIGNED NOT NULL
SPECIAL_AREA VARCHAR(20) NOT NULL //EG DJ, SINGER, DANCER, MC ETC.
GENRE VARCHAR(20),
FOREIGN KEY (MEMBERID)REFERENCES ARTISTS (MEMBERID)
)
TYPE=INNODB;

CREATE TABLE ARTIST_BOOKING
( MEMBERID INT UNSIGNED NOT NULL,
CID INT UNSIGNED NOT NULL,
DATE_NOW TIMESTAMP(6) NOT NULL,
DATE_FOR DATE,
HOURS TINYINT(2),
PRIMARY KEY (MEMBERID, CID, DATE_NOW),
FOREIGN KEY (MEMBERID) REFERENCES ARTISTS (MEMBERID),
FOREIGN KEY (CID) REFERENCES CLIENTS (CID)
)
TYPE=INNODB;

ps I copyright the code here so no one can use it or ill have there hides!

[deleted]

Anyway, I saw your post earlier (even though I nolonger get emails about new posts.... feckerdefeck)

I don't have access to an InnoDB-capable mysql right now, I'll look at it later.
It definately has something to do with the syntax/setup of the PK/FK, but without InnoDB, mysql doesn't process that bit, so I can't debug it.

I'll get back to you later.

A forum, a FAQ, email notification, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

The problems with your tables are down to your indexing.

From the innodb manual:
Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the first columns.

Causes:
1. There's no index on Memberid in Specialist_area.
2. The index on CID in the bookings table does not use CID as the first field in the index.

Resolutions:
1. Create an index on Memberid in specialist_area, either as a primary or secondary index.
2. Create a seperate secondary index on CID in bookings.

Copyrighting SQL code ?!? That'll be a first :o)

Justin

Anon

ok i sort of understand, but could u show me justin? wow actually i thinl u mite be right! where the hell is my composite primary key in specialist area!!!!!! if i do that will it then work? thanks for your input!

Anon

copyright was my little joke but what i meant was i copyright my schema! I put a lot of time and effort into creating my schema for my database! I have ER diagrams all over the place! ;-)

Anon

Have created your tables as a test.

Creation scripts are (sorry bit of a long posting):

DROP TABLE /!32200 IF EXISTS/ clients;
CREATE TABLE CLIENTS
(
CID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CMPNYNAME VARCHAR(20),
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20),
POSTCODE VARCHAR(10) NOT NULL,
EMAIL VARCHAR(20),
CONTACT_NO INT ZEROFILL NOT NULL
)
TYPE=INNODB;

DROP TABLE /!32200 IF EXISTS/ artists;
CREATE TABLE ARTISTS
( MEMBERID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
FNAME VARCHAR(20) NOT NULL,
LNAME VARCHAR(20) NOT NULL,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20),
POSTCODE VARCHAR(10) NOT NULL,
DOB DATE NOT NULL,
EMAIL VARCHAR(20),
CONTACT_NO INT ZEROFILL NOT NULL,
STAGENAME VARCHAR(15),
EXPRNCE_IN_YEARS INT UNSIGNED NOT NULL,
SEX CHAR(1) NOT NULL,
NI_NO VARCHAR(12) NOT NULL
)
TYPE=INNODB;

DROP TABLE /!32200 IF EXISTS/ specialist_area;
CREATE TABLE SPECIALIST_AREA
( MEMBERID INT UNSIGNED NOT NULL PRIMARY KEY,
SPECIAL_AREA VARCHAR(20) NOT NULL,
GENRE VARCHAR(20),
FOREIGN KEY (MEMBERID) REFERENCES ARTISTS (MEMBERID)
)
TYPE=INNODB;

DROP TABLE /!32200 IF EXISTS/ artist_booking;
CREATE TABLE ARTIST_BOOKING
( MEMBERID INT UNSIGNED NOT NULL,
CID INT UNSIGNED NOT NULL,
DATE_NOW TIMESTAMP(6) NOT NULL,
DATE_FOR DATE,
HOURS TINYINT(2),
PRIMARY KEY (MEMBERID, CID, DATE_NOW),
KEY CID_KEY (CID),
FOREIGN KEY (MEMBERID) REFERENCES ARTISTS (MEMBERID),
FOREIGN KEY (CID) REFERENCES CLIENTS (CID)
)
TYPE=INNODB;

This creates all the tables with the two changes:
1.Created primary key on Memberid in specialist_area
2. Created a seperate index on CID in artist booking.

Cheers
Justin

Anon

thanks a lot justin i'll see if this works! at the moment i'm having to create my tables inside the console, i cant seem to execute an sql file created elsewhere to run! whats the command line for running an existing sql file in mysql? once again thanks for your time!

Anon

cheers justin! just ran the script with source . and bingo it worked! whats the key KEY CID_KEY (CID) actually do then? is it basically creating a name to reference a foreign key? it would be cool if you could explain the use of this. thanks again! worked like a treat!! :o)

simon

Anon

Simon,

KEY CID_KEY(CID) actually creates a new index called CID_KEY for the table artist_booking that is an index on the field CID.

The reason that you have to do this is caused by the Innodb foreign key rules.

i.e. The rule is that for a foreign key relationship, the table that you want to foreign key against must have the foreign key field in the first column of an index.

For example:
Table a has three fields a1,a2,a3 and table b has 4 fields b1,b2,b3,b4.
The primary key on table a (i.e. the most important index) is based on fields a1 and a2 in that order.
There is a second index (not the primary key, equivalent to CID_KEY) based on the field a2 in table a.

The primary key on table b is based on field b1.

We want to create 2 foreign key relationships. One between b1 and a1 and one between a2 and b2.

Relationship 1 (b1 to a1).
Criteria 1. Is there an index on table b in which b1 is the first field - yes, the primary index on table b.
Criteria 2. Is there an index on table a in which a1 is the first field - yes, the primary index on table a.
Result : foreign key creation will work first time for b1 to a1.

Relationship 2 (b2 to a2).
Criteria 1. Is there an index on table b in which field b2 is the first field - no, not yet.
Criteria 2. Is there an index on table a in which a2 is the first field - yes, the secondary index on table a. We can't use the primary index as field a2 is the 2nd field.
Result: foreign key creation fails.
Solution: create a secondary index on table b with field b2 as the first field.

These are the steps that I used to check your table definitions with, i.e.

Relationship Artist_booking.CID referencing Client.CID
Criteria 1: Is there an index on table Artist_booking in which field CID is the first field - no, not yet.
Criteria 2: Is there an index on table Client in which field CID is the first field - yes, the primary.
reasult: foreign key defn fails.
Solution: create a new index on Artist_booking.CID with CID as the first field.

Hope this helps

Justin

Anon

I have just looked at the specialist_area table and I think that the memberid, specialist_area and genre attributes should combine to form a composite primary key as you cannot identify genre from just knowing the memberid or the specialist_area as you can have a DJ that plays House music or say Cheez music! :o) what do you think? thanks again for the explanation, very efficient!

Anon

Yep, no problem with that. It's a little unusual in database design to have a table that has no off-key data, but it's perfectly fine if it's a look-up type of table.

Your foreign key links should still work as memberid would still be the first field in the key.

Justin

Anon

ok Justin cheers! i am now churning out my insert scripts and was wondering if there are any special sorts of ways that i have to insert data into my tables? I mean I want to insert data into the artists table and have the corresponding memberid link to their specialist area in the specialist_area table. is there any way to check that referential integrity has been kept? Ive seen how it is done in oracle but not mysql. any tips? ;-) cheers! trust me in two months i'll buy u a few buds to say thanks! (finishing dissertation!)

Anon

Simon,

sorry for delay, have just got back from an Easter break.

AS far as I'm aware, as long as your inserts are done in the right order, i.e. non-foreign key table first, followed by table using foreign key, there should be no problem. So in this case, you would need to insert into arists first, followed by the insert into specialist_area.

The foreign key links will prevent any other order of insertion.

Bar that, there's no special requirements.

Cheers

Anon

I have an sql insert file made up and i tried to execute it, the first insert into artists is fine where I have used Null for the memberid auto_increment field but when it tried to insert into specialist_area it says: cannot insert into child. foreign key constraint fails. Is this because I have to to the table specialist_area which index I am refering to from the artists table.
i.e insert into specialist_area values
(1, "DJ", "UKGARAGE");

the number 1 being the index refering to the auto increment value in artists yeh?

thanks bud, hope your easter was good!

simon

ps i think we have the longest going tree in the whole of this forum!

Anon

Simon,

Yep good Easter. Tragically over now. Trust yours was as good.

Ok, problem you are having relates to the database commit phase. If you're happy with commits, then jump the next paragraph to the solution.

Commits:
The flow in a transactional database is:
1. Start the transaction (usually implicit, but can be done with a BEGIN statement)
2. User issues a series of data changing instructions (e.g. insert, update etc).
2. A database buffer is then filled with the work instruction. This buffer varies from database system to database system, but generally has a unique identifier for the change and a list of changes associated with that change number.
3a. If there is a disaster of some nature, a ROLLBACK can be called which returns the database to the state it was in, without applying the work in the buffer.
3b If all goes well, the transaction is finally written to the database and the buffer values are written out to the log file. This is most usually caused by the transaction ending(the COMMIT statement). The transaction can be committed by the database engine if the buffers are filling up etc.

The problem:
The problem that you have run into is that the SQL script that you are running hasn't commited all the changes that you have made to the artists table. Because this data hasn't been written from the buffers down to disk, the foreign key lookup from specialist_area doesn't think that you have put any entries in artists yet and so the foriegn key is failing.

The solution:
A little way into your insert script, put a Commit statement in. The number of lines that you put in between commits is upto you. Given that your record size isn't huge, a commit every hundred records or so should be fine (assuming that you don't have 10,000 records to put in).
For safe measure, put a commit after the last insert into artists.

After each commit, the records in the block above the commit will be available to the foreign key lookup in specialist_area.

NB: autoincrement fields DON'T return to 1 if you delete all the records in a table. The only way to return it all the autoincrements to 1 is to drop and recreate the tables.

If you want to by-pass the forum, my mail address is Justin@calax.com

Cheers

Justin