Обсуждение: Foreign Keys and OIDs
Is the foreign key mechanism supposed to work with OIDs? Is it known to be broken in 7.0.3? I declare two tables: CREATE TABLE address ( street text, city text, state char(2) check ( state ~'[A-Z]{2}') ); CREATE TABLE personaddress ( person_id integer, address_id oid references address(oid), notes text, primary key (person_id, address_id) ); Then I insert data into both address and person. When I try to insert into personaddress I get the following reply: =>INSERT INTO personaddress VALUES ('1', '29321', 'My home'); ERROR: constraint <unnamed>: table address does not have an attribute oid Should I create a new primary key column or is there some workaround or a fix in the upcoming release or am I just plain doing something wrong? Thanks, Toshio Kuratomi -- ______S______U______B______L______I______M______I______N______A______L______ b a d g e r @ p r t r - 1 3 . u c s c . e d u GA->ME 1999
Foreign keys to oids are not currently supported (and won't be 7.1 either I believe). Right now you'll have to use a new key column. On Sat, 16 Dec 2000, Toshio Kuratomi wrote: > Is the foreign key mechanism supposed to work with OIDs? > Is it known to be broken in 7.0.3? > > I declare two tables: > > CREATE TABLE address ( > street text, > city text, > state char(2) check ( state ~'[A-Z]{2}') > ); > > CREATE TABLE personaddress ( > person_id integer, > address_id oid references address(oid), > notes text, > primary key (person_id, address_id) > ); > > Then I insert data into both address and person. > When I try to insert into personaddress I get the following reply: > > =>INSERT INTO personaddress VALUES ('1', '29321', 'My home'); > ERROR: constraint <unnamed>: table address does not have an attribute oid > > Should I create a new primary key column or is there some workaround or a > fix in the upcoming release or am I just plain doing something wrong?
Toshio Kuratomi wrote: > > Is the foreign key mechanism supposed to work with OIDs? > Is it known to be broken in 7.0.3? > > I declare two tables: > > CREATE TABLE address ( > street text, > city text, > state char(2) check ( state ~'[A-Z]{2}') > ); > > CREATE TABLE personaddress ( > person_id integer, > address_id oid references address(oid), > notes text, > primary key (person_id, address_id) > ); > > Then I insert data into both address and person. > When I try to insert into personaddress I get the following reply: > > =>INSERT INTO personaddress VALUES ('1', '29321', 'My home'); > ERROR: constraint <unnamed>: table address does not have an attribute oid > > Should I create a new primary key column or is there some workaround or a > fix in the upcoming release or am I just plain doing something wrong? > > Thanks, > Toshio Kuratomi > -- > > ______S______U______B______L______I______M______I______N______A______L______ > b a d g e r @ p r t r - 1 3 . u c s c . e d u > GA->ME 1999 The correct is: CREATE TABLE address ( address_id SERIAL PRIMARY KEY, street text, city text, state char(2) check ( state ~'[A-Z]{2}') ); CREATE TABLE personaddress ( person_id integer, address_id int4 references address, notes text, primary key (person_id, address_id) ); -- ====================================================== AKACIA TECNOLOGIA Desenvolvimento de sistemas para Internet www.akacia.com.br