Question

Locked

Basic MySql Update

By kevinh ·
Hello,
I'm new to programing... the guy doing my application has left the planet...:0(
So, I'm trying to learn mysql database programing and finish the project myself. I've been reading and searching as much as possible but need a little help understanding some of the basics.


Question:
I'm trying to understand if entering data in a foreign key field of one table, will automatically update the matching primary key in another table, or vice versa? Or, do I have to write code to make that happen?

For example there are three tables (dbf_admin, dbf_admingrps, dbf_adminres).
The dbf_adminres table contains foreign keys from both the dbf_admin and dbf_admingrps tables, and has it's own primary key.

So, when I create an admin record (in the dbf_admin table) will the dbf_admin_id foreign key in the dbf_adminres table get updated automatically?

Can you give me any pointers?

I was thinking that when I entered a new admin record, that the dbf_adminres should know this, since the key/foreign fields are linked in the sql query statement.

Below is some sql code generated by phprunner and the sql database structure.


SELECT
dbf_admin_id,
dbf_admin_userid,
dbf_admin_fullname,
dbf_admin_lname,
dbf_admin_fname,
dbf_admin_passwd,
dbf_admin_status,
dbf_admin_active
FROM dbf_admin

SELECT
dbf_admingrp_id,
dbf_admingrp_name,
dbf_admingrp_desc
FROM
dbf_admingrps

SELECT
dbf_adminres.dbf_adminres_id,
dbf_adminres.dbf_admingrp_id,
dbf_adminres.dbf_admin_id
FROM dbf_adminres
INNER JOIN dbf_admin ON dbf_adminres.dbf_admin_id = dbf_admin.dbf_admin_id
INNER JOIN dbf_admingrps ON dbf_adminres.dbf_admingrp_id = dbf_admingrps.dbf_admingrp_id

==========================================

CREATE TABLE `rfx_admin` (
`rfx_admin_id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
`rfx_admin_userid` varchar(35) DEFAULT NULL,
`rfx_admin_fullname` varchar(100) DEFAULT NULL,
`rfx_admin_lname` varchar(50) DEFAULT NULL,
`rfx_admin_fname` varchar(50) DEFAULT NULL,
`rfx_admin_passwd` varchar(50) DEFAULT NULL,
`rfx_admin_status` int(11) DEFAULT NULL,
`rfx_admin_active` int(11) DEFAULT NULL,
PRIMARY KEY (`rfx_admin_id`),
UNIQUE KEY `rfx_admin_id` (`rfx_admin_id`),
UNIQUE KEY `rfx_admin_userid` (`rfx_admin_userid`),
KEY `AI_rfx_admin_id` (`rfx_admin_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `rfx_admingrps` (
`rfx_admingrp_id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT,
`rfx_admingrp_name` varchar(50) DEFAULT NULL,
`rfx_admingrp_desc` varchar(30) DEFAULT NULL,
PRIMARY KEY (`rfx_admingrp_id`),
UNIQUE KEY `rfx_admingrp_id` (`rfx_admingrp_id`),
KEY `AI_rfx_admingrp_id` (`rfx_admingrp_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `rfx_adminres` (
`rfx_adminres_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`rfx_admingrp_id` bigint(20) unsigned zerofill NOT NULL,
`rfx_admin_id` bigint(20) unsigned zerofill NOT NULL,
PRIMARY KEY (`rfx_adminres_id`),
UNIQUE KEY `rfx_adminres_id` (`rfx_adminres_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


--
Thanks,
-Kevin

This conversation is currently closed to new comments.

1 total post (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Referential Integrity

by oldbaritone In reply to Basic MySql Update

The concept you're describing is called "referential integrity", and you define if-and-when referential integrity should be enforced.

Here's an introduction
http://www.databasejournal.com/features/mysql/article.php/2248101/Referential-Integrity-in-MySQL.htm

In the POS example given, you'll see that you can create a customer without having sales, but you should have a customer in order to create a sale record. Unmatched records are "orphaned."

Read the entire article; it's very good. Hopefully it will explain what you're looking for.

Back to Web Development Forum
1 total post (Page 1 of 1)  

Related Discussions

Related Forums