newby - AUTOINCREMENT problem.

Hi,

I'm enclosed a snippet of test code which highlights my problem. The Stored
procedure insertValue should insert text into the parent, then insert other
text into the child table but the 2 tables should auto increment in sync
(i.e. so that they both end up with the same id numbers). I've tried taking
the auto increment out of the child table but then I don't know how to get
the right parent id into the child table.

Any advice appreciated - this is my first database, so I'm just in the
learning process really. Code follows:

CREATE TABLE Parent
(id INTEGER DEFAULT AUTOINCREMENT,
parenttext VARCHAR(16),
PRIMARY KEY (id))!

CREATE TABLE Child
(childID INTEGER INTEGER DEFAULT AUTOINCREMENT,
childtext VARCHAR(16),
FOREIGN KEY (childid) REFERENCES Parent(id),
PRIMARY KEY (childID))!

CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))
BEGIN
insert into parent (parenttext) values (p);
insert into child (childtext) values (c);
END!

call insertValues('from parent', 'from child')!
select * from parent, child where parent.id = child.childid!In message <436cefc6$1_3@.mk-nntp-2.news.uk.tiscali.com>, Mary Walker
<123@.123.com> writes
>Hi,
>I'm enclosed a snippet of test code which highlights my problem. The Stored
>procedure insertValue should insert text into the parent, then insert other
>text into the child table but the 2 tables should auto increment in sync
>(i.e. so that they both end up with the same id numbers). I've tried taking
>the auto increment out of the child table but then I don't know how to get
>the right parent id into the child table.
>Any advice appreciated - this is my first database, so I'm just in the
>learning process really. Code follows:

OK. The first piece of advice is don't rely on the autonumber to return
any particular value. There are ways that the values in the two tables
could get out of sync and it would be a PITA to fix.

There are two options that I could suggest.

One is that you create the parent record and then take the autonumber
value from there and explicitly insert it into the child table. Don't
use an autonumber in child table, use a simple integer instead. This is
the way I recommend that you do it.

The second method has limited applicability but can make things easier.
First redesign your child table with an integer key. Then create a query
that links the two tables. You can now update the query instead of
updating the two tables separately.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.|||>> this is my first database, so I'm just in the learning process really. <<

Get a book on RDBMS and learn why an auto-increment can NEVER be a
relational key. Learn why a table name should be a collective or
plural name (unless there is only one row in the table). Learn way
"id" is too vague to be data element name. Learn why camelCase is a
bitch to read --hint: where does your eye jump when you see an
Uppercase letter?

I know you want to have a "Magical Universal Key" that will solve all
your design problems, without having to really think or learn anything.
What is your **real key** in your **real** problem?

Going to a Newsgroup to get what usually takes a few YEARS of college
and experience does not work.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1131243682.809956.301200@.g49g2000cwa.googlegr oups.com...
>>> this is my first database, so I'm just in the learning process really.
>>> <<
> Get a book on RDBMS and learn why an auto-increment can NEVER be a
> relational key. Learn why a table name should be a collective or
> plural name (unless there is only one row in the table). Learn way
> "id" is too vague to be data element name. Learn why camelCase is a
> bitch to read --hint: where does your eye jump when you see an
> Uppercase letter?
> I know you want to have a "Magical Universal Key" that will solve all
> your design problems, without having to really think or learn anything.
> What is your **real key** in your **real** problem?
> Going to a Newsgroup to get what usually takes a few YEARS of college
> and experience does not work.

LOL. Thanks very much you for reply. I have actually learnt a lot by
simply reading your reply - but I'll buy the book anyway :-)|||In your book 'sql for smarties' you have an example with a table named
'Warehouse' - if you where to follow your own naming standards then it
should be called 'Warehouses'.

'id' is fine within the context of the table, if the column belongs to the
table 'Child' then its obviously Child.id.

What key would you use for a message board? Would you still key it on
subject and posting date or like the rest of us and how NNTP works, create a
guid with the domain?

The auto-number (IDENTITY property) is usually used as a artificial (or
surrogate) key, there is no such thing as a relational key - I think you
mean 'natural key', see: http://en.wikipedia.org/wiki/Natural_key.

Quoting from that aritcle...

"
The main disadvantage of choosing a natural key is that it may need to
change if your business requirements change. For example, if you have chosen
CustomerNumber as the primary key for a customer, and, subsequently,
CustomerNumber becomes alphanumeric instead of numeric, then as well as
changing the type of the column, you will need to make changes to all other
tables where CustomerNumber is used as a foreign key.

Retrieved from "http://en.wikipedia.org/wiki/Natural_key"

"

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1131243682.809956.301200@.g49g2000cwa.googlegr oups.com...
>>> this is my first database, so I'm just in the learning process really.
>>> <<
> Get a book on RDBMS and learn why an auto-increment can NEVER be a
> relational key. Learn why a table name should be a collective or
> plural name (unless there is only one row in the table). Learn way
> "id" is too vague to be data element name. Learn why camelCase is a
> bitch to read --hint: where does your eye jump when you see an
> Uppercase letter?
> I know you want to have a "Magical Universal Key" that will solve all
> your design problems, without having to really think or learn anything.
> What is your **real key** in your **real** problem?
> Going to a Newsgroup to get what usually takes a few YEARS of college
> and experience does not work.|||Hi Mary,

The SCOPE_IDENTITY() will return the last inserted IDENTITY value for that
connection.

insert into parent (parenttext) values (p);
print scope_identity() -- gives the id from parent

insert into child (childtext) values (c);
print scope_identity() -- gives id from child

Does that help?

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

"Mary Walker" <123@.123.com> wrote in message
news:436cefc6$1_3@.mk-nntp-2.news.uk.tiscali.com...
> Hi,
> I'm enclosed a snippet of test code which highlights my problem. The
> Stored procedure insertValue should insert text into the parent, then
> insert other text into the child table but the 2 tables should auto
> increment in sync (i.e. so that they both end up with the same id
> numbers). I've tried taking the auto increment out of the child table but
> then I don't know how to get the right parent id into the child table.
> Any advice appreciated - this is my first database, so I'm just in the
> learning process really. Code follows:
> CREATE TABLE Parent
> (id INTEGER DEFAULT AUTOINCREMENT,
> parenttext VARCHAR(16),
> PRIMARY KEY (id))!
> CREATE TABLE Child
> (childID INTEGER INTEGER DEFAULT AUTOINCREMENT,
> childtext VARCHAR(16),
> FOREIGN KEY (childid) REFERENCES Parent(id),
> PRIMARY KEY (childID))!
> CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))
> BEGIN
> insert into parent (parenttext) values (p);
> insert into child (childtext) values (c);
> END!
> call insertValues('from parent', 'from child')!
> select * from parent, child where parent.id = child.childid!