Re: very basic question
От | Arjen van der Meijden |
---|---|
Тема | Re: very basic question |
Дата | |
Msg-id | 004001c2c95a$4df3fb30$3ac15e91@acm обсуждение исходный текст |
Ответ на | very basic question (Dennis Gearon <gearond@cvc.net>) |
Список | pgsql-general |
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) >
В списке pgsql-general по дате отправления: