Question Locked

Basic MySql Update

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

2 replies

by oldbaritone

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.

This discussion is closed to new replies.