Re: Newbie table definition question - Mailing list pgsql-general

Stephen,

Thanks that does help. Syntax is a bit foreign still. I'm not sure why
you defined the "grocery_items_id_exists" and "grocery_types_id_exists
" constraints though. Is that something that's ever explicitly accessed
either through a query or an insert? Or is it one of those things that
enforces data integrity behind the scenes?

Thanks,

Ken

On Oct 16, 2004, at 8:42 PM, Steven Klassen wrote:

> * Ken Tozier <kentozier@comcast.net> [2004-10-16 13:50:37 -0400]:
>
>> I'm a C/Objective C programmer and am having a bit of difficulty
>> figuring out how to define SQL table approximations to things that
>> are very easy to do in C/Objective C
>
> I sympathize; no matter how many languages you know, there's always a
> learning curve involved trying to pick up the nuances of the next one.
>
>> Basically what I'm confused about is how to simulate arrays of
>> structs in Postgres. For example, if I define a C struct like so
>
> You can track whatever information you need about the particular trip,
> add rows to the cart associating the trip with the items being
> purchased, and finally the grocery types and items.
>
> CREATE TABLE trips (
>     id bigserial primary key NOT NULL,
>     created timestamp default now() NOT NULL
> );
>
> CREATE TABLE cart (
>     id bigserial primary key NOT NULL,
>     trips_id bigint NOT NULL,
>     grocery_items_id bigint NOT NULL
> );
>
> CREATE TABLE grocery_types (
>     id bigserial primary key NOT NULL,
>     name text NOT NULL
> );
>
> CREATE TABLE grocery_items (
>     id bigserial primary key NOT NULL,
>     grocery_types_id bigint NOT NULL,
>     name text NOT NULL,
>     price numeric(10,2) NOT NULL
> );
>
> ALTER TABLE cart ADD CONSTRAINT grocery_items_id_exists FOREIGN KEY
> (grocery_items_id) REFERENCES grocery_items(id);
> ALTER TABLE grocery_items ADD CONSTRAINT grocery_types_id_exists
> FOREIGN KEY (grocery_types_id) REFERENCES grocery_types(id);
>
> INSERT INTO grocery_types (name) VALUES ('fruit');
> INSERT INTO grocery_types (name) VALUES ('vegatable');
>
> INSERT INTO grocery_items (grocery_types_id, name, price) VALUES (1,
> 'Apple', '0.50');
> INSERT INTO grocery_items (grocery_types_id, name, price) VALUES (1,
> 'Orange', '0.75');
>
> INSERT INTO grocery_items (grocery_types_id, name, price) VALUES (1,
> 'Brocolli', '1.35');
> INSERT INTO grocery_items (grocery_types_id, name, price) VALUES (1,
> 'Lettuce', '2.55');
>
> HTH,
>
> --
> Steven Klassen - Lead Programmer
> Command Prompt, Inc. - http://www.commandprompt.com/
> PostgreSQL Replication & Support Services, (503) 667-4564
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>