7 Replies - 12621 Views - Last Post: 28 February 2011 - 01:30 AM Rate Topic: -----

#1 nirvana.in.me  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-January 11

primary key not unique

Posted 15 February 2011 - 08:52 AM

im using the oracle database 10g express edition.
can anybody help me make a primary key NOT unique.
in my schema, M:M, a i created a table with 2 primary keys and i need one of those attributes to be availably repeated.
i did not declare the attributes unique, only primary keys.
however, when i add rows, the error says "ORA-02291: integrity constraint (MAJORMAJOR.SYS_C004104) violated - parent key not found".
please help me make a primary key NOT unique. i just need an idea.PLEASE.

Is This A Good Question/Topic? 0
  • +

Replies To: primary key not unique

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6245
  • View blog
  • Posts: 24,013
  • Joined: 23-August 08

Re: primary key not unique

Posted 15 February 2011 - 08:58 AM

A primary key is by its very definition unique.
Was This Post Helpful? 0
  • +
  • -

#3 nirvana.in.me  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-January 11

Re: primary key not unique

Posted 15 February 2011 - 09:04 AM

so it's not possible?
:(
so i cannot repeat the values of these primary keys on the table?

i thought that a
primary key = not nullable, but may not be unique
unique = nullable but must have unique values,different from the rest
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon


Reputation: 6979
  • View blog
  • Posts: 14,600
  • Joined: 16-October 07

Re: primary key not unique

Posted 15 February 2011 - 09:41 AM

A primary key must be unique. Each table can only have one PK ( made up of one or more fields). If you want that combination of fields not to be unique, then it's inappropriate as a primary key.

The PK is used to retrieve one, and only one, record from a table. Many tables don't really have anything that meets that criteria and simply use an unique identifier for each row. Many databases have a special field that can do this; Oracle isn't one of them.

In Oracle, you can create a SEQUENCE and then always apply it to a new record. More here.
Was This Post Helpful? 1
  • +
  • -

#5 nirvana.in.me  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 22-January 11

Re: primary key not unique

Posted 16 February 2011 - 12:40 AM

my teacher said that when the relationship is many to many, i have to create a new table and
the primary keys will be the primary keys of the two entities it is connected to.
Was This Post Helpful? 0
  • +
  • -

#6 andy_pleasants  Icon User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 122
  • Joined: 08-July 10

Re: primary key not unique

Posted 16 February 2011 - 01:58 AM

View Postnirvana.in.me, on 16 February 2011 - 12:40 AM, said:

my teacher said that when the relationship is many to many, i have to create a new table and
the primary keys will be the primary keys of the two entities it is connected to.


This is a composite key where the uniqueness is based on both values in the key, not just one.
Was This Post Helpful? 1
  • +
  • -

#7 baavgai  Icon User is online

  • Dreaming Coder
  • member icon


Reputation: 6979
  • View blog
  • Posts: 14,600
  • Joined: 16-October 07

Re: primary key not unique

Posted 16 February 2011 - 05:39 AM

A many to many relationship still requires primary key uniqueness.

person(person_id, name, ..), 
   PK(person_id)
group(group_id, name, ..), 
   PK(group_id)
person_group(person_id,group_id), 
   PK(person_id,group_id), 
   FK(person_id-person.person_id), 
   FK(group_id-group.group_id)



In the scenario above, one person can belong to many groups and one group can have many people; many to many.
The person_group exists only to make that connection. It's primary key is the same as the two pieces of that connection. Here, it simply wouldn't make sense to have a person_id,group_id repeat.

Note, that if a person could only belong to one group, there's no need for the third table:
group(group_id, name, ..), 
   PK(group_id)
person(person_id, name, group_d, ..), 
   PK(person_id),
   FK(group_id-group.group_id)


Was This Post Helpful? 0
  • +
  • -

#8 c#think#:)  Icon User is offline

  • D.I.C Head

Reputation: 17
  • View blog
  • Posts: 114
  • Joined: 17-March 10

Re: primary key not unique

Posted 28 February 2011 - 01:30 AM

It's very simple.

What everybody are trying to say is:
every table must have PK. PK is always unique. In your case you have a table with 2 attributes that are PK in other tables.

1) All you have to do is set these two attributes as a Composite Key. In this case every attribute can contain duplicated values, but every row/entity must be unique. And there you go, you have a PK(Composite Key) in a table and at the same time attributes are not unique.

2) The second way you can solve your problem is to add in your table another attribute, let's say "LINE_NR" that would be the PK for your table. And there you go, you have a PK in a table and at the same time attributes are not unique(except for the PK attribute).

Good luck!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1