Thread: CHECK constraint names

CHECK constraint names

From
"Christopher Kings-Lynne"
Date:
Hi,

Is it correct behaviour that unnamed table-level check constraints get the
names '$1', '$2', '$3', etc. in Postgres 7.0.3???

Eg, using table constraints:
----------------------------

test=# create table test (temp char(1) NOT NULL, CHECK (temp IN ('M',
'F')));
CREATE
test=# select rcname from pg_relcheck;
rcname
$1
(1 row)

And, even worse - I think this has got to be a bug:
---------------------------------------------------

test=# create table test (temp char(1) NOT NULL, CHECK (temp IN ('M',
'F')));
CREATE
test=# create table test2 (temp char(1) NOT NULL, CHECK (temp IN ('M',
'F')));
CREATE
test=# select rcname from pg_relcheck;rcname
--------$1$1
(2 rows)

Two constraints with the same name!!!!

And if you use column constraints:
----------------------------------

test=# create table test (temp char(1) NOT NULL CHECK (temp IN ('M', 'F')));
CREATE
test=# select rcname from pg_relcheck; rcname
-----------test_temp
(1 row)

--
Christopher Kings-Lynne
Family Health Network (ACN 089 639 243)



Re: CHECK constraint names

From
Stephan Szabo
Date:
The constraint naming isn't really terribly sensible right now.  The
names generated should be unique within I think schema according to
the spec and I think that should be true even if users name a constraint
such that it would cause a collision (so, if i name a constraint what
an automatic constraint would normally be named, it should be picking
a different automatic name rather than erroring:
create table test( a int constraint test_b check (a>3), b int check
(b<3));
)

Until there's a a good way to look at the defined constraints (a catalog
or something) this probably isn't a big deal, since these should   also
be unique against the other constraints too (pk, unique, fk).


Stephan Szabo
sszabo@bigpanda.com

On Wed, 20 Dec 2000, Christopher Kings-Lynne wrote:

> Hi,
> 
> Is it correct behaviour that unnamed table-level check constraints get the
> names '$1', '$2', '$3', etc. in Postgres 7.0.3???
> 
> Eg, using table constraints:
> ----------------------------
> 
> test=# create table test (temp char(1) NOT NULL, CHECK (temp IN ('M',
> 'F')));
> CREATE
> test=# select rcname from pg_relcheck;
> rcname
> $1
> (1 row)
> 
> And, even worse - I think this has got to be a bug:
> ---------------------------------------------------
> 
> test=# create table test (temp char(1) NOT NULL, CHECK (temp IN ('M',
> 'F')));
> CREATE
> test=# create table test2 (temp char(1) NOT NULL, CHECK (temp IN ('M',
> 'F')));
> CREATE
> test=# select rcname from pg_relcheck;
>  rcname
> --------
>  $1
>  $1
> (2 rows)
> 
> Two constraints with the same name!!!!
> 
> And if you use column constraints:
> ----------------------------------
> 
> test=# create table test (temp char(1) NOT NULL CHECK (temp IN ('M', 'F')));
> CREATE
> test=# select rcname from pg_relcheck;
>   rcname
> -----------
>  test_temp
> (1 row)
> 
> --
> Christopher Kings-Lynne
> Family Health Network (ACN 089 639 243)
>