Thread: UNIQUE constraint

Hi,

I have a question about the UNIQUE constraint.  The documentation
describes this example:

CREATE TABLE example (   a integer,   b integer,   c integer,   UNIQUE (a, c)
);

But it is not clean to me.  Does the above example mean that the list
of pairs must be unique or is it only a short version for this
constraint:

CREATE TABLE example (   a integer UNIQUE,   b integer,   c integer UNIQUE
);

Does the following table fullfill the UNIQUE clause of the example
from the Postgres documentation?
 a b c ----- 1 2 3 1 1 1

cu Sascha

-- 
secunet Security Networks AG, Im Teelbruch 116, 45219 Essen
Tel: +49-2054-123-408   Fax: +49-2054-123-123
PGP: FBE2 A49B 6526 C1B4 7F10  24E4 5004 7C27 6E9A 9698

On Aug 7, 2004, at 3:25 AM, Sascha Ziemann wrote:
> CREATE TABLE example (
>     a integer,
>     b integer,
>     c integer,
>     UNIQUE (a, c)
> );
>
> But it is not clean to me.  Does the above example mean that the list
> of pairs must be unique

Yes.

> Does the following table fullfill the UNIQUE clause of the example
> from the Postgres documentation?
>
>   a b c
>   -----
>   1 2 3
>   1 1 1

Yes.

For example,

test=# create table example (a integer, b integer, c integer, unique 
(a,c));
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 
"example_a_key" for table "example"
CREATE TABLE
test=# insert into example (a,b,c) values (1,2,3);
INSERT 5935749 1
test=# insert into example (a,b,c) values (1,1,1);
INSERT 5935750 1
test=# insert into example (a,b,c) values (1,3,3);
ERROR:  duplicate key violates unique constraint "example_a_key"
test=# select a,b,c from example; a | b | c
---+---+--- 1 | 2 | 3 1 | 1 | 1
(2 rows)

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann <grzm@myrealbox.com> writes:

> On Aug 7, 2004, at 3:25 AM, Sascha Ziemann wrote:
> > CREATE TABLE example (
> >     a integer,
> >     b integer,
> >     c integer,
> >     UNIQUE (a, c)
> > );
> >
> > But it is not clean to me.  Does the above example mean that the list
> > of pairs must be unique
> 
> Yes.

Do you know wheather this is part of the SQL standard?  I have to
write an application that works on Postgresql and Oracle.  Is this the
same on Oracle?

Sorry for asking but right now I have no access to the database to
test it.

cu Sascha

-- 
secunet Security Networks AG, Im Teelbruch 116, 45219 Essen
Tel: +49-2054-123-408   Fax: +49-2054-123-123
PGP: FBE2 A49B 6526 C1B4 7F10  24E4 5004 7C27 6E9A 9698

Hi,

I have a problem with the COMMENT command.  Here:

http://www.postgresql.org/docs/7.4/static/sql-comment.html

it is defined in this way:

COMMENT ON
{ TABLE object_name | COLUMN table_name.column_name | AGGREGATE agg_name (agg_type) | CONSTRAINT constraint_name ON
table_name| DATABASE object_name | DOMAIN object_name | FUNCTION func_name (arg1_type, arg2_type, ...) | INDEX
object_name| OPERATOR op (leftoperand_type, rightoperand_type) | RULE rule_name ON table_name | SCHEMA object_name |
SEQUENCEobject_name | TRIGGER trigger_name ON table_name | TYPE object_name | VIEW object_name
 
} IS 'text'

I would like to comment a constraint but it doesn't work.  I did the
following:

create table tab3 ( a integer, b integer, constraint uni unique (a, b)
);

comment on constraint uni on table tab3 is 'unique pair';

Did I do anything wrong or is this a bug?

cu Sascha

-- 
secunet Security Networks AG, Im Teelbruch 116, 45219 Essen
Tel: +49-2054-123-408   Fax: +49-2054-123-123
PGP: FBE2 A49B 6526 C1B4 7F10  24E4 5004 7C27 6E9A 9698

Sascha Ziemann writes:

> it is defined in this way:
[...]
>   CONSTRAINT constraint_name ON table_name |

Well then...

> comment on constraint uni on table tab3 is 'unique pair';                              ^^^^^
...why do you insert "table" when it is defined without? :-)

regards
Andreas

Hi,

I try to emulate with PostgreSQL an Oracle database.  My problem is
that PostgreSQL does not support any Oracle specific types.
PostgreSQL provides the TEXT and Oracle uses the CLOB or VARCHAR2
type.  I would like to use the CREATE TYPE statement to tell
PostgreSQL about the Oracle types, but the documentation does not
describe how to define a simple alias type.  I would like to define a
new type VARCHAR2 which should behave exactly like VARCHAR.  Is this
possible with PostgreSQL?  By this it would be possible to run the
Oracle create table scripts without modification.

cu Sascha

-- 
secunet Security Networks AG, Im Teelbruch 116, 45219 Essen
Tel: +49-2054-123-408   Fax: +49-2054-123-123
PGP: FBE2 A49B 6526 C1B4 7F10  24E4 5004 7C27 6E9A 9698

Besuchen Sie uns auf der DMS-Expo vom 07. - 09. September 2004 in
Essen, Halle 2, Stand 2435 c/d (Consulting Corner).

Sascha Ziemann <ziemann@secunet.de> writes:
> I would like to define a
> new type VARCHAR2 which should behave exactly like VARCHAR.

You could get about halfway there with CREATE DOMAIN varchar2 AS varchar;
But it's only halfway because the domain will not accept length
decorations; that is "varchar2(100)" will not work.
        regards, tom lane