Can anyone tell me how to create a

[cfFran]

Can anyone tell me how to create a primary key on two fields?
I have a table called "fmrDocs". It has two numeric fields called "fmrItemId" and "ptChgNumber". Both of those values are required to uniquely identify the record.

I have tried to list the two fields separated by a comma in the
ALTER TABLE fmrDocs
ADD CONSTRAINT fmrDocs_fmrItemId_pk PRIMARY KEY
(fmrItemId, ptChgNumber). That didn't work.

I tried ALTER TABLE fmrDocs
ADD CONSTRAINT fmrDocs_fmrItemId_pk PRIMARY KEY
(fmrItemId||ptChgNumber). That didn't work.

Our dba is out sick and I have to finish this very soon. Thanks in advance.
Fran

 

[SantaMufasa]

Fran,

When you say, "...That didn't work," what leads you to believe it didn't work. When I try it, it works:

SQL> ALTER TABLE fmrDocs
2 ADD CONSTRAINT fmrDocs_fmrItemId_pk PRIMARY KEY
3 (fmrItemId, ptChgNumber);

Table altered.

Please post the error message that implies failure.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:39 (18Dec03) GMT, 11:39 (18Dec03) Mountain Time)

 

[carp]

I too am curious. What error message did you get?

 

[cfFran]

Mufasa,

Let me supply a little background. I am submitting the Oracle commands through ColdFusion. We have a little utility that I wrote that passes the SQL statement directly through. I have successfully added 18 tables this morning and applied all of the single-field primary key constraints and the foreign key constraints using this technique. As a natter of fact I have used this ad hoc qry utility for close to five years now. However...

When I try the two-field PRIMARY KEY command, I get the following error:

ODBC Error Code = S1000 (General error)

[Oracle][ODBC][Ora]ORA-02260: table can have only one primary key

SQL = "ALTER TABLE fmrDocs ADD CONSTRAINT fmrDocs_fmrItemId_pk PRIMARY KEY (fmrItemId, ptChgNumber)"

Fran

 

[SantaMufasa]

Then it means that there is already a Primary Key on the table. To see the existing PK, issue this query:

select constraint_name from user_constraints
where constraint_type = 'P' and table_name = 'FMRDOCS';

You'll probably see something like my results:

CONSTRAINT_NAME
--------------------
FMRDOCS_FMRITEMID_PK

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:55 (18Dec03) GMT, 11:55 (18Dec03) Mountain Time)

 

[carp]

The error message tells you what the problem is.
This table already has a primary key - and it may already be defined the way you want it to be.

 

[carp]

You can get rid of the primary key with
ALTER TABLE my_table DROP PRIMARY KEY;
after which your primary key creation command will work. However, I would STRONGLY recommend you wait until your DBA gets back before doing this. They get real cranky when people dump things like primary keys and all of the sudden performance goes into the tank or the data becomes corrupted!

 

[cfFran]

I think you are correct. I created a new table called fmrDocsJunk. I put it up on the server and the command worked for that table.

Earlier today I created a PRIMARY KEY on just fmrItemId in the fmrDocs table.

I guess in order to drop the constraint from the fmrDocs table, I must first drop the foreign keys that reference it. Then I can drop it and recreate it.

Thanks a million, guys!

 

[cfFran]

Carp,

I think I am safe. The work I am doing is entirely in new tables being added to the server's database. Our DBA should have no concern over data lost. There isn't any yet.


Fran