referential integrity 2

[ksbrace]

Hello,
I am just getting my feet wet w/MYSQL. I am going through a MYSQL tutorial in the book 'Internet and

http://WWW how

to program' by Deitel & Deitel. I am trying to an insert and have my data, such as the foreign keys also get automatically updated. But I am not having any success as of yet. I have this simple db called books. In books:
Publishers
PublisherID (Primary Key) that is a foreign key in Titles and PublisherName
--
Titles
ISBN (Primary Key) that is a foreign key in AuthorISBN, Title, EditionNumber,YearPublished,Description,PublisherID
--
AuthorISBN just a look-up table that links Titles and Authors together. It has ISBN and AuthorID.
--
Authors
AuthorID (Primary Key) that is foreign key to AuthorISBN,
FirstName, LastName,YearBorn.
----
I am trying to add a new title for an author, but also want the AuthorISBN table to get updated as well and the Publisher. I thought that this would happen automatically because I linked the tables togther when I created the DB.

Here's my sql statement:

insert into Titles(ISBN, Title, EditionNumber, YearPublished, Description,
 PublisherID) values ('0-88-010671-2','Brace: How to Program', 1, 2003, 'damn good book',3 );

any help would be greatly appreciated. Thanks in advance,
Kelly

 

[swampBoogie]

You need to insert the publisher and author information with separate insert statement(s). There is no automaticy about this.

 

[ksbrace]

Thanks for your response.

 

[JustKIDn]

swampboogie,

Can you clarify that?

I'm just figuring this stuff out myself and I've seen references that said what you said.

Can you give an example how to do this the right way?

Since you're having to do each table seperatly. How do you know what the FK is to enter?

Maybe a good link to show us the way?

tgus

____________________________
Families can be together forever...

 

[swampBoogie]

Let's say you have two tables

publisher (pubid int not null auto_increment primary key,
other columns)

title(isbn char(12) not null primary key,
pubid int references publisher)

Normally, when adding titles you would have a drop-down list of known publisher but also some way of adding a new publisher. It would probably be easiest to have it as a separate function and if a new publisher is inserted you just refresh the drop down with the publisher id as the value.

If you want to insert into both publisher and title table in the same page you must first insert into the publisher table.
After an insert statement you can get the newly generated auto_increment value by using the PHP function mysql_insert_id or the SQL function last_insert_id.


e.g.

<php pseudo code>
// connect
$q = &quot;insert into publisher(pubid,other column) values('',other values)&quot;;
mysql_query($q) or die(mysql_error());
$newid = mysql_insert_id();
$q = &quot;insert into title(ISBN,pubid,other column) values('&quot; .
$_POST('ISBN') .&quot;',&quot; . $pubid . &quot;,other columns)&quot;;
mysql_query($q) or die(mysql_error());
</php pseudo code>

 

[JustKIDn]

swampboogie,

You get a S T A R !

Even though I've never done any PHP code. I can kind of pick through it and get what you're doing.

Is it possible to wrap both query's together into one?
tgus

____________________________
Families can be together forever...

 

[swampBoogie]

The Mysql server does not support multiple queries but there are some interfaces that can handle this, e.g. PEAR I think.
As this is handled by the clients there is no performance gain.