List:General Discussion« Previous MessageNext Message »
From:Jules Bean Date:May 19 1999 9:12am
Subject:Re: Problem w/ self-referencing tables population
View as plain text  
Gianmarco Armellin wrote:
> 
> Hi everyone!
> We are  total  beginners, please  help :)
> 
> We  need  to  populate a table
> 
> CREATE TABLE Level (
>         id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>         descr VARCHAR(30) BINARY,
>         parent INT REFERENCES Level(id),
>         UNIQUE (descr)
> )
> 
> in which 'parent' denotes an ordering relation between levels.
> We try to populate it with:
> 
> 
> INSERT INTO Level VALUES (
>         NULL, 'Nation', NULL);
> 
> INSERT INTO Level
>         SELECT NULL, 'Region', ParentLevel.id FROM Level AS ParentLevel WHERE
> ParentLevel.descr='Nation';
> 
> ...trying to make mysql understand that 'Region' is under 'Nation', but I get
> 
> ERROR 1093 at line 12: INSERT TABLE 'Level' isn't allowed in FROM table list
> 
> Does  anyone know what's going on?
> Any workaround  is much appreciated. thanks in advance,

Two things wrong :-)

1) Is 'descr' supposed to be a primary key or not?  If it is, make it a
primary key, and do without the auto_increment one, you don't need it. 
Since you've declared it UNIQUE, I don't see why you don't use it as a
primary key.  Conversely, if 'descr' isn't supposed to be a primary key
(i.e. if it would be a valid data set for your application that two
'levels' exist with the same descr field) then you should use the
LAST_INSERT_ID() builtin function.  Search in the mysql manual for this,
but it would look something like this:

INSERT INTO Level VALUES (NULL, 'Nation', NULL);
INSERT INTO Level VALUES (NULL, 'Region', LAST_INSERT_ID());

(I'm actually not sure if you can use LAST_INSERT_ID() in another insert
- you might need to SELECT LAST_INSERT_ID() to get the value into a
local variable in your support language, perl, C, whatever, and then
insert it).

2) Your database structure isn't normalised.

You're using 'NULL' to denote 'level has no parent'.  That's not what
NULL means.  NULL means 'unknown'.  Since it is implicit in your
structure that some levels don't have parents, I would suggest you split
into two tables:

CREATE TABLE Level (
        descr VARCHAR(30) BINARY PRIMARY KEY,
)

CREATE TABLE Parents {
	child VARCHAR(30) BINARY REFERENCES Level(descr) PRIMARY KEY,
	parent VARCHAR(30) BINARY REFERENCES Level(descr)
}

Your example then becomes


INSERT INTO Level VALUES ('Nation');
INSERT INTO Level VALUES ('Region');
INSERT INTO Parents VALUES ('Region','Nation');

Hope that helps,

Jules

-- 
/----------------+-------------------------------+---------------------\
|  Jelibean aka  | jules@stripped         |  6 Evelyn Rd        |
|  Jules aka     |                               |  Richmond, Surrey   |
|  Julian Bean   | jmlb2@stripped        |  TW9 2TF *UK*       |
+----------------+-------------------------------+---------------------+
|  War doesn't demonstrate who's right... just who's left.             |
|  When privacy is outlawed... only the outlaws have privacy.          |
\----------------------------------------------------------------------/
Thread
Problem w/ self-referencing tables populationGianmarco Armellin18 May
  • Re: Problem w/ self-referencing tables populationThimble Smith19 May
  • Re: Problem w/ self-referencing tables populationJules Bean19 May