Thread: primary key attribute

Hi there,

I am having trouble trying to alter a class and update an column
constraint to PRIMARY KEY. I decided to create a new table using the
following, still it does seem work:

CREATE TABLE my_table

    (my_column INT PRIMARY KEY, my_column2 TEXT);

Could any one help me please. Thanks in advance.

Joao Paulo

Joao Paulo Felix wrote:
>
> Hi there,
>
> I am having trouble trying to alter a class and update an column
> constraint to PRIMARY KEY. I decided to create a new table using the
> following, still it does seem work:
>
> CREATE TABLE my_table
>
>         (my_column INT PRIMARY KEY, my_column2 TEXT);
>
> Could any one help me please. Thanks in advance.
>
> Joao Paulo

I'm new to this myself, but I know there's no
PRIMARY KEY in the SQL subset supported by PostgreSQL.

You can get the same effect by
CREATE TABLE my_table (my_column INT, my_column2 TEXT);

CREATE UNIQUE INDEX my_table_my_column_key
    ON my_table(my_column);

Then you'll be guaranteed that all records will have
distinct my_column integers.  Hope this helps.

Billy Donahue wrote:
  >Joao Paulo Felix wrote:
  >>
  >> Hi there,
  >>
  >> I am having trouble trying to alter a class and update an column
  >> constraint to PRIMARY KEY. I decided to create a new table using the
  >> following, still it does seem work:
  >>
  >> CREATE TABLE my_table
  >>
  >>         (my_column INT PRIMARY KEY, my_column2 TEXT);
  >>
  >> Could any one help me please. Thanks in advance.
  >>
  >> Joao Paulo
  >
  >I'm new to this myself, but I know there's no
  >PRIMARY KEY in the SQL subset supported by PostgreSQL.

Your information is out of date.  6.3.2 does support the PRIMARY KEY
constraint. Here are two examples that work:

create table price
(
    product        char(8)        primary key
                        references product (id),
    unit        text        default 'each',
    cost        money        not null,
    home        money        not null,
    export        money        not null,
    next_home    money,
    next_export    money
)
;

create table batch_issues
(
    product        char(8)        not null
                        references product (id),
    batch        int        not null,
    stamp        datetime    not null
                    default datetime(now()),
    type        char(1)        not null,
    ref        char(5),
    qty        int        not null,

    primary key (product, batch, stamp),
    foreign key (product, batch) references batch
);

[the foreign key bits are parsed but have no effect, in 6.3.2]

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "Let no man despise thy youth; but be thou an example
      of the believers, in word, in conversation, in
      charity, in spirit, in faith, in purity."
                                       I Timothy 4:12