REFERENCE problem with parent_table

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.