Обсуждение: very basic question

If I have the following two tables, (very much shortened for your convenience):

CREATE TABLE Gens(
gen_id serial NOT NULL CONSTRAINT PK_Gens1 PRIMARY KEY,
gen varchar(16) DEFAULT 'none' NOT NULL, --
CONSTRAINT UC_Gens1 UNIQUE(gen));
COMMENT ON COLUMN Gens.gen_id IS 'integer surr primary key';
COMMENT ON COLUMN Gens.gen IS 'examples are JR, SR, I, III, etc';

CREATE TABLE Usrs(
usr_id serial NOT NULL CONSTRAINT PK_Usrs1 PRIMARY KEY,
login varchar(32) NOT NULL,
gen_id int4 NOT NULL,
CONSTRAINT FK_A_Usr_Has_A_Generation_2 FOREIGN KEY (gen_id) REFERENCES Gens (gen_id),
CONSTRAINT UC_Usrs1 UNIQUE(login));
COMMENT ON COLUMN Usrs.usr_id IS 'integer surr primary key';


How do I insert values into Usrs that use the values of Gens.gen_id for referential integrity,
but by referencing the Gen.gen field?

Say I wanted to insert (in one statement):
    'fancy_login_name' for Usrs.login
        *and*
    the Gen.gen_id for 'JR' in Usrs.gen_id
        *assuming*
    that 'JR' exists in Gen.gen.

Thank you very much. In MySQL I would have used PHP to find the Gen.gen_id and then inserted
that, (whether that was the right way, I don't know)

OK, I'm a newbie to USING databases, I'm fair at designing them, I think.

You could simply do something like this:

INSERT INTO Usrs (login, gen_id) VALUES ('foo', (SELECT gen_id FROM Gens
WHERE gen = '$gen'));

It will even fail with a nice error if there won't be an id to select :)

Regards,

Arjen

> -----Oorspronkelijk bericht-----
> Van: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] Namens Dennis Gearon
> Verzonden: vrijdag 31 januari 2003 19:47
> Aan: pgsql-general@postgresql.org
> Onderwerp: [GENERAL] very basic question
>
>
> If I have the following two tables, (very much shortened for
> your convenience):
>
> CREATE TABLE Gens(
> gen_id serial NOT NULL CONSTRAINT PK_Gens1 PRIMARY KEY,
> gen varchar(16) DEFAULT 'none' NOT NULL, --
> CONSTRAINT UC_Gens1 UNIQUE(gen));
> COMMENT ON COLUMN Gens.gen_id IS 'integer surr primary key';
> COMMENT ON COLUMN Gens.gen IS 'examples are JR, SR, I, III, etc';
>
> CREATE TABLE Usrs(
> usr_id serial NOT NULL CONSTRAINT PK_Usrs1 PRIMARY KEY,
> login varchar(32) NOT NULL,
> gen_id int4 NOT NULL,
> CONSTRAINT FK_A_Usr_Has_A_Generation_2 FOREIGN KEY (gen_id)
> REFERENCES Gens (gen_id), CONSTRAINT UC_Usrs1 UNIQUE(login));
> COMMENT ON COLUMN Usrs.usr_id IS 'integer surr primary key';
>
>
> How do I insert values into Usrs that use the values of
> Gens.gen_id for referential integrity,
> but by referencing the Gen.gen field?
>
> Say I wanted to insert (in one statement):
>     'fancy_login_name' for Usrs.login
>         *and*
>     the Gen.gen_id for 'JR' in Usrs.gen_id
>         *assuming*
>     that 'JR' exists in Gen.gen.
>
> Thank you very much. In MySQL I would have used PHP to find
> the Gen.gen_id and then inserted
> that, (whether that was the right way, I don't know)
>
> OK, I'm a newbie to USING databases, I'm fair at designing
> them, I think.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>

something like this should work, if I understand your question correctly:

insert into usrs (login, gen_id) select 'fancy_login_name', gen_id from gens where gen = 'JR';


I hope, it helps...

Dima

Dennis Gearon wrote:
> If I have the following two tables, (very much shortened for your convenience):
>
> CREATE TABLE Gens(
> gen_id serial NOT NULL CONSTRAINT PK_Gens1 PRIMARY KEY,
> gen varchar(16) DEFAULT 'none' NOT NULL, --
> CONSTRAINT UC_Gens1 UNIQUE(gen));
> COMMENT ON COLUMN Gens.gen_id IS 'integer surr primary key';
> COMMENT ON COLUMN Gens.gen IS 'examples are JR, SR, I, III, etc';
>
> CREATE TABLE Usrs(
> usr_id serial NOT NULL CONSTRAINT PK_Usrs1 PRIMARY KEY,
> login varchar(32) NOT NULL,
> gen_id int4 NOT NULL,
> CONSTRAINT FK_A_Usr_Has_A_Generation_2 FOREIGN KEY (gen_id) REFERENCES Gens (gen_id),
> CONSTRAINT UC_Usrs1 UNIQUE(login));
> COMMENT ON COLUMN Usrs.usr_id IS 'integer surr primary key';
>
>
> How do I insert values into Usrs that use the values of Gens.gen_id for referential integrity,
> but by referencing the Gen.gen field?
>
> Say I wanted to insert (in one statement):
>     'fancy_login_name' for Usrs.login
>         *and*
>     the Gen.gen_id for 'JR' in Usrs.gen_id
>         *assuming*
>     that 'JR' exists in Gen.gen.
>
> Thank you very much. In MySQL I would have used PHP to find the Gen.gen_id and then inserted
> that, (whether that was the right way, I don't know)
>
> OK, I'm a newbie to USING databases, I'm fair at designing them, I think.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>