referential issue

[tyson.crouch]

G'day,

I've got a small problem. As all who post here usually do.

I've got my table

CREATE TABLE IF NOT EXISTS `attractions` (
  `attID` int(11) NOT NULL AUTO_INCREMENT,
  `attName` varchar(255) NOT NULL,
  `adultPrice` double NOT NULL,
  `childPrice` double NOT NULL,
  `referenceID` int(11) NOT NULL,
  PRIMARY KEY (`attID`),
  UNIQUE KEY `attName` (`attName`)
) ENGINE=InnoDB;

Within my table, referenceID is a key field which references attID.
When I insert a record into my table, how can i make sure that if no other attID field is specified in the referenceID that the new attID is inserted??

Thx in advance,
TC

[tesuji]

It's a great thing: INNODB supports FOREIGN KEYS. You may simply add foreign-key clause,
http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html. One should carfully consider insert/update/delete policies.

Recomendation: never ever omit foreign key clauses if attributs refer to other tables. Foreign keys are the only way to guarantee referential integrity. Without that you have to spend enormous additional programming efforts (if you be a serious programmer).

-- tesu

[tesuji]

Ah, i just see referenceID refers to attID within same table. This is kind of recursive reference one usually need to building hierarchical structures like trees, e.g. for bill of materials.

Referencing the same table is a common task (therefore my statement "if attributs refer to other tables" should be extended to " and same table".

-- tesu

[tyson.crouch]

@tesu - Thx, however my db although not shown in the create statement has an internal relation from referenceID to attID, like i said in the same table.

What I would like to know, is how to i, when inserting a new record have the referenceID the point to the attID which was just created?

Thx
TC

(Also, i am a serious programmer and would like to avoid running multiple queries to insert the newly created attID into the table)

[tesuji]

Maybe you want to get the last inserted auto incremental key? This can be done by function LAST_INSERT_ID(). For example:

begin transaction;
...
insert into mytable (referenceID, ... ) values (null, ...);
update mytable set referenceID = attID where attID = last_insert_id();
...
commit;

-- tesu

[tesuji]

I've got another idea solving this problem by using a trigger:

create trigger insert_fk after insert on mytable
  for each row set new.referenceID = new.attID;

(Code neither tested)

-- tesu

[tyson.crouch]

@tesu - I like the trigger idea, i think it might work easier than the transaction.

Just a question (given that i know very little about triggers) how can i run the trigger if the new.referenceID is NULL??

Thx

[tesuji]

Trigger will be automatically fired (=run) depending on the action performed on the table the trigger is assigned to.

I wrote "after insert" which means that the trigger will be fired automatically every time a new record has been inserted in mytable. Besides after insert there are before insert, before/after update/update of, before/after delete. Also you can combine insert, update, delete actions.

-- tesu

[tyson.crouch]

I've got the following trigger,

Delimiter //
CREATE TRIGGER nullCHeck AFTER INSERT ON gstravel.attractions
FOR EACH ROW
BEGIN
UPDATE referenceID SET new.referenceID = new.attID WHERE referenceID = NULL
END;
//

While testing this, i was given an error:
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 5)

I've looked over the syntax, but there must be something i've missed.
IF anyone could help, it would be much appreciated.

Thx,
TC

[tyson.crouch]

Okay,

well i've solved the issue of the syntax error. turns out i should have used the // delimiter instead of the ; after the END statement.

But, i now get another error message saying this
SQL query:

INSERT INTO `gstravel`.`attractions` (
`attID` ,
`attName` ,
`adultPrice` ,
`childPrice` ,
`referenceID` )
VALUES ( NULL , 'test', '2250', '1000', NULL )

My Trigger

DELIMITER $$
CREATE TRIGGER refNullCheck
AFTER INSERT ON attractions
FOR EACH ROW
  BEGIN
    UPDATE attractions SET new.referenceID = new.attID WHERE new.referenceID = null;
  END$$
DELIMITER ;

MySQL said:
#1442 - Can't update table 'attractions' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

[tesuji]

hi,

please do these changes:

1. in create table atractions:
`referenceID` INT(11) NOT NULL, --> `referenceID` INT(11),
(possibly you already has done)

2. in create trigger
UPDATE referenceID SET new.referenceID = new.attID WHERE referenceID = NULL -->
UPDATE attractions SET referenceID = last_insert_id();

This works fine on MS SQL Server 2008 and Sybase SQL Anywhere V11. I am just sitting in front of them and tested it. (Remark: On both databases the meaning of last_insert_id() is @@identity.) Unfortunately no mysql right now.

-- tesu

[tyson.crouch]

tesu, this appears to be a limitation of MySQL with all current 5.x versions.
I still get the same error message. You'd think that MySQL would have solved this not long after releasing triggers/procedures and the like..

Also, i'd already taken into consideration your initial point of changing from NOT NULL. THX!!!

You've been a gr8 help. It just appears that this is not going to happen.

[tesuji]

Well, actually the trigger solution works fine on my other databases (I will also test my first advice using commit etc). Btw, if you insert or update one or more tables you have to use begin transaction and commit/rollback too. Never rely on auto-commit configuration!

I should also note that i had had several serious problems with older mysql databases because msyql has its very own (kind of inconsiderate) notion on relational databases, sql, and relational design in the sense of Edgar Codd. In the past several times we discontinued very poorly designed applications based on MySQL (where poor design significantly correlates with the underlying DBMS in question), did redesigns and ported them to other, more reliable DBMS e.g. Oracle, Sybase ASE, DB2.

As of mid-week I will have mysql access at my company. There I will check both solution on a running mysql v5.1.

-- tesu

[tyson.crouch]

Thank-you tesu!

You've been a great help. I look forward to hearing from you regarding the transaction process!

IF all else fails, i'll have to use multiple queries to insert the attID =S

[drjohn]

Why not just do it in the programming language BEFORE the query is submitted?

eg

if (referenceID is null) referenceID = attID

because your problem is NOT a database query one, it's a data entry one.

[tyson.crouch]

It cannot be done BEFORE the the query is run. This is because the query itself inserts into an AI column.

this problem is not a data entry problem at all. Its more of a limitation had by MySQL to fully support triggers. The only way to achieve this through a given programming language is to return the last row entered, and check if referenceID is null.

I was hoping to have a more elegant solution (like using the triggers) but alas was not to be !

Thx for your effort drjohn