Обсуждение: REFERENCE problem with parent_table

 Hello

I need many tables of type "id" and "name", see below:
      CREATE TABLE id_names (
      id            smallint        CONSTRAINT the_id PRIMARY KEY NOT NULL,
      name        text            CONSTRAINT the_name UNIQUE
    ) WITH OIDS;
therefore I created these tables with the "LIKE" operator, see below:
    CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS;
    CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS;
    CREATE TABLE like_id_3 ....
 Next I can't create a table with some column reference to any of the last two tables, see below:
    database=# CREATE TABLE ref_1 ( id_1 smallint CONSTRAINT the_id_1 REFERENCES like_id_1 (id) );
    ERROR:  there is no unique constraint matching given keys for referenced table "like_id_1"
 Obviously if I use "id_names" instead of "like_id_1" every think is fine but my idea is not create thousands of almost same tables with the table name's like the only one difference. Then I thought to use the operator "LIKE", but you see, there are a problem. Any Idea about what must I do ??

  Thank you,
           Gustavo

> I need many tables of type "id" and "name", see below:
> /      CREATE TABLE id_names (
>       id            smallint        CONSTRAINT the_id PRIMARY KEY NOT NULL,
>       name        text            CONSTRAINT the_name UNIQUE
>     ) WITH OIDS;/
> therefore I created these tables with the "LIKE" operator, see below:
> /    CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS;
>     CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS;

it looks like the syntax here is a little off from what is defined by:
http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

CREATE TABLE like_id_1 ( id    smallint like id_names,
                         name  text     like id_name ) with oids ;

Regards,

Richard Broersma jr.

Richard Broersma Jr wrote:
>> I need many tables of type "id" and "name", see below:
>> /      CREATE TABLE id_names (
>>       id            smallint        CONSTRAINT the_id PRIMARY KEY NOT NULL,
>>       name        text            CONSTRAINT the_name UNIQUE
>>     ) WITH OIDS;/
>> therefore I created these tables with the "LIKE" operator, see below:
>> /    CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS;
>>     CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS;
>>
>
> it looks like the syntax here is a little off from what is defined by:
> http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
>
> CREATE TABLE like_id_1 ( id    smallint like id_names,
>                          name  text     like id_name ) with oids ;
>
Are you sure, I think that you are wrong.  Operator "LIKE" is like a
operator for a "table_constraint", I mean, is wrote in a new line, like
a new column, and isn't property or part of any column.  More over, in
the documentation is wrote: "/The LIKE clause specifies a table from
which the new table automatically copies all column names, their data
types, and their not-null constraints./", but is like  the operator
"REFERENCE" see the tables "like_id_1" empty (see below) or maybe is a
bug in posgresql.
/    database=# insert into like_id_1 values (1,'hello');
    INSERT 157581 1
    database=# select * from like_id_1;
     id | name
    ----+--------
      1 | hello
    (1 row)
    database=# CREATE TABLE ref_1 ( id smallint CONSTRAINT the_id
REFERENCES like_id_1 (id) );
    ERROR:  there is no unique constraint matching given keys for
referenced table "like_id_1"/

  Thank you any way,
        Gustavo

On Tue, 15 Aug 2006, gustavo halperin wrote:

>  Hello
>
> I need many tables of type "id" and "name", see below:
> /      CREATE TABLE id_names (
>       id            smallint        CONSTRAINT the_id PRIMARY KEY NOT NULL,
>       name        text            CONSTRAINT the_name UNIQUE
>     ) WITH OIDS;/
> therefore I created these tables with the "LIKE" operator, see below:
> /    CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS;
>     CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS;
>     CREATE TABLE like_id_3 ..../
>  Next I can't create a table with some column reference to any of the
> last two tables, see below:
> /    database=# CREATE TABLE ref_1 ( id_1 smallint CONSTRAINT the_id_1
> REFERENCES like_id_1 (id) );
>     ERROR:  there is no unique constraint matching given keys for
> referenced table "like_id_1"/
>  Obviously if I use "id_names" instead of "like_id_1" every think is
> fine but my idea is not create thousands of almost same tables with the
> table name's like the only one difference. Then I thought to use the
> operator "LIKE", but you see, there are a problem. Any Idea about what
> must I do ??


The LIKE clause doesn't copy the UNIQUE/PRIMARY KEY constraints from
id_names. You'll probably need to add the constraint information to the
other tables.

> > it looks like the syntax here is a little off from what is defined by:
> > http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
> >
> > CREATE TABLE like_id_1 ( id    smallint like id_names,
> >                          name  text     like id_name ) with oids ;
> >
> Are you sure, I think that you are wrong.  Operator "LIKE" is like a
> operator for a "table_constraint", I mean, is wrote in a new line, like
> a new column, and isn't property or part of any column.  More over, in
> the documentation is wrote: "/The LIKE clause specifies a table from
> which the new table automatically copies all column names, their data
> types, and their not-null constraints./", but is like  the operator
> "REFERENCE" see the tables "like_id_1" empty (see below) or maybe is a
> bug in posgresql.
> /    database=# insert into like_id_1 values (1,'hello');
>     INSERT 157581 1
>     database=# select * from like_id_1;
>      id | name
>     ----+--------
>       1 | hello
>     (1 row)
>     database=# CREATE TABLE ref_1 ( id smallint CONSTRAINT the_id
> REFERENCES like_id_1 (id) );
>     ERROR:  there is no unique constraint matching given keys for
> referenced table "like_id_1"/

You are correct, I miss-spoke.  :o)

Regards,

Richard Broersma Jr.