Thread: how to create secondary key!!

good day,

i'm using pgAdmin II as the remote client, after importing the text file
to postgresql database; i'm start trying to find the way to create the
primary key and secondary key. unfortunely, under the pgAdmin II there
aren't any tools to do that. However i managed to fine the way to create
the primary key by using the index function but i still cannot create the
secondary key in my database. please advise me!



Best Regards,

       /'"`\
      ( - - )
--oooO--(_)--Oooo---------------------------------------------------------

Nelson Yong
nelsonyong@ipmuda.com.my
THE BUILDING MATERIALS PEOPLE

Nelson,

> i'm using pgAdmin II as the remote client, after importing the text
> file
> to postgresql database; i'm start trying to find the way to create
> the
> primary key and secondary key. unfortunely, under the pgAdmin II
> there
> aren't any tools to do that. However i managed to fine the way to
> create
> the primary key by using the index function but i still cannot create
> the
> secondary key in my database. please advise me!

As there is no such thing as a "secondary key" in SQL, you are probably
referring to something else.   Can you explain what you mean by
"secondary key"?   Perhaps a "Foriegn Key" or a "Unique Index"?

-Josh Berkus

Nelson,

> What i call the Secondary key is when i want two or more fields
> become
> key eg. Order Table can be more than one or more transaction, i
> assign
> order no. and record no. as key.  The Order no. call primary key and
> the
> Record no. as secondary key.

I'm afraid that's incorrect.   What you have is a "two-column primary
key."   There is no "secondary key".   Here's how you create one:

create table order_detail (
    order_no INT NOT NULL REFERENCES orders(order_no),
    record_no INT NOT NULL,
    item_no INT NOT NULL REFERENCES inventory(item_no),
    quantity NUMERIC NOT NULL,
    comment TEXT,
    CONSTRAINT order_detail_PK PRIMARY KEY (order_no, record_no)
);

Got it?   Read the "CREATE TABLE" documentation for more detail.

-Josh Berkus

P.S. To reiterate:  There are Primary Keys, Candidate Keys, Surrogate
Keys, and Foreign Keys, but no "Secondary Keys".

Sorry guys, I realise that this is probably a stupid question to be asking,
but I'm fast running out of options.

I've got a  Sybase dump file and I was wondering if there is any way to read
this into postgres?

Much Thanks

---
Rob

**************************
Rob Cherry
mailto:rob@jamwarehouse.com
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************

Nelson,

> If i migrate the database from Text file using pgadmin tools,the
> table
> and data record will be created automatically and no primary key
> assign
> so now i need to assign the key but unfortunely i can't change the
> field
> property. i.e. I can't assign "two-column primary key."

You'll need to use the command line for this.  Open an ad-hoc query
window from PGAdminII (the wierd-looking monster).   Run the following
command:

ALTER TABLE table_name ADD CONSTRAINT table_name_PK
PRIMARY KEY (column_1, column_2);

... where you replace table_name, column_1 and column_2 with the
appropriate objects from your database.

BTW, the above command will fail if it turns out that the combination
of column_1 and column_2 is not unique, or if either column has NULLs.

-Josh Berkus

Rob,

> Sorry guys, I realise that this is probably a stupid question to be
> asking,
> but I'm fast running out of options.
>
> I've got a  Sybase dump file and I was wondering if there is any way
> to read
> this into postgres?

What's a SyBase dump file look like?   It's been 5 years, I can't
really remember.

If it's text, we can probably bend COPY around to loading it.

It it's binary, you may be S.O.L.   TechDocs is down right now, but
look later at techdocs.postgresql.org to see if anyone has written a
Sybase-to-PostgreSQL converter.

Also, if you have a *running* Sybase database, conversion is a lot
easier ... you can use Perl::DBI to read directly from sybase to a COPY
file, and then load the COPY file into Postgres.

-Josh Berkus

Nelson,

> I appriciate u reply, thank u.
>
> I try the sql statement which u commented then how to solve not null
> problem.
>
> ALTER TABLE king ADD CONSTRAINT king_PK
> PRIMARY KEY (vb1, vb2);
>
> Error hit during execute this command
> Number: -2147467259
>
> Description: Error while executing the query;
> ERROR:  Existing attribute "vb1" cannot be a PRIMARY KEY because it
> is
> not marked NOT NULL
> ---------------------------
> OK
> ---------------------------
>
> i try to use command below but no function, kindly command.
>
> ALTER TABLE table_name
>    ALTER COLUMN column_name INT NOT NULL

Regrettably, this function of the ALTER TABLE command is currently not
supported in PostgreSQL.   In order to mark a column as NOT NULL, you
must:
1) make sure to remove all NULLs from the column, and
2) modify the system tables to set the column NOT NULL.

Hopefully someone will post more detailed instructions on step 2), as I
cannot remember them right now.

-Josh