Composite primary key

[SaintJames]

How do I add a composite primary key to a contraints. in Oracle 10

This isthe SQL that I have but it returns an error,

SQL=ALTER TABLE [Table_Name] ADD CONSTRAINT [Constraint_Name] PRIMARY KEY (field1, field2);

Then I get this error:ORA-00955: name is already used by an existing object

But the contraint is non-existant. If I add only one field the constraint get added.

Help!
Oracle Newby

 

[oradba101]

Hi, Saint James

It may mean that you have an index that uses both those columns.

Regards,

William Chadbourne
Oracle DBA

 

[sem]

I think that existing index can not raise such error as it would be picked up by constraint without the need in new one. I suppose that you should check your existing constraints more carefully.


Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

It seems to me that an index already exists by the same name as "[Constraint_Name]". Whatever "[Constraint_Name]" is, look for an index by that name in the result set of the query:

select index_name from user_indexes;

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SaintJames]

I've removed the selected constraints for the indexes now I get

ORA-01536: space quota exceeded for tablespace

 

[SantaMufasa]

This means that instead of "quota unlimited" for the target tablespace, your DBA has assigned you some explicit, finite value for space consumption on the target tablespace. To move beyond this problem, either your DBA must alter your quota for that tablespace to some higher value (or "quota unlimited") for the target tablespace, or you must DROP some obsolete/extraneous table or index or TRUNCATE some table to the extent that you have enough free quota to accommodate the creation of the new index.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.