Re: REFERENCE problem with parent_table

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.