11 Replies - 2296 Views - Last Post: 13 April 2008 - 11:57 PM Rate Topic: -----

#1 BoneXXX  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 26-September 07

Drop Primary Key

Post icon  Posted 12 April 2008 - 10:13 PM

Hi I need a help for removing a PK from a table which has multiple primary keys. I think the code at the below just removes the constraint. I don't want remove the constraint, as I said there multiple primary keys and I want to remove just one of them to make it foreign key. Could you help me please? Thanks

ALTER TABLE tablename
drop CONSTRAINT pk_columnname;

This post has been edited by BoneXXX: 12 April 2008 - 10:15 PM

Is This A Good Question/Topic? 0
  • +

Replies To: Drop Primary Key

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


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

Re: Drop Primary Key

Posted 13 April 2008 - 03:55 AM

View PostBoneXXX, on 13 Apr, 2008 - 01:13 AM, said:

a table which has multiple primary keys


Not possible. A table, by definition, can have one, and only one, primary key. It can reasonably have multiple candidates for a primary key, but only one of them can be the primary key.

Dropping the primary key constraint from a table should drop the key. However, some versions of SQL also generate a unique index for PKs that the constraint my not take off. It should remove it, but I've seen key indexes linger on in some places,
Was This Post Helpful? 0
  • +
  • -

#3 BoneXXX  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 26-September 07

Re: Drop Primary Key

Posted 13 April 2008 - 04:11 AM

Thank you for the reply. The table was like this.
CREATE TABLE tablename
(
col1	  INTEGER,
col2	  INTEGER,
col3	  NUMBER(1),
	 CONSTRAINT pk_tablename PRIMARY KEY (col1,col2)
);



I have dropped the constraint then I wanted to alter pk and fk but I couln't create a primary key. These are my code's structure that I used

PROMPT Drop Primary Key
ALTER TABLE TableName 
DROP CONSTRAINT pk_ConstraintName;

PROMPT Create a primary key
ALTER TABLE TableName
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (ColumnnnName);


-------------------------------------------------------------------------------------------------------------
As output I get
Drop Primary Key

Table altered.

Create a primary key
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (ColumnnnName);
							 *
ERROR at line 2:
ORA-02437: cannot validate (Owner.PK_ConstraintName) - primary key violated



So what is the problem and how can I solve it, and please be specific as much as possible thanks.
Was This Post Helpful? 0
  • +
  • -

#4 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 187
  • View blog
  • Posts: 4,673
  • Joined: 26-November 04

Re: Drop Primary Key

Posted 13 April 2008 - 04:35 AM

Quote

Not possible. A table, by definition, can have one, and only one, primary key. It can reasonably have multiple candidates for a primary key, but only one of them can be the primary key.

Of course you can.
You can use two or more attributes collectively as a primary key.
Was This Post Helpful? 0
  • +
  • -

#5 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 187
  • View blog
  • Posts: 4,673
  • Joined: 26-November 04

Re: Drop Primary Key

Posted 13 April 2008 - 04:41 AM

As for your question, you can use this to set a primary key constraint after a table has been created:
ALTER TABLE <table> ADD CONSTRAINT <constraint_name> PRIMARY KEY (<attribute>);

Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


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

Re: Drop Primary Key

Posted 13 April 2008 - 05:28 AM

View Postborn2c0de, on 13 Apr, 2008 - 07:35 AM, said:

You can use two or more attributes collectively as a primary key.


And that would be more than one primary key, how? :P

View PostBoneXXX, on 13 Apr, 2008 - 07:11 AM, said:

ALTER TABLE TableName
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (ColumnnnName);
...
ORA-02437: cannot validate (Owner.PK_ConstraintName) - primary key violated



So what is the problem and how can I solve it, and please be specific as much as possible thanks.


The problem is that ColumnnnName contains data that makes it an invalid primary key. The following two queries should return no data:
select * from TableName where ColumnnnName is null;

select ColumnnnName from TableName group by ColumnnnName having count(*)>1;



You fix it by only having no duplicate values in ColumnnnName and no nulls. If the field ( or fields ) must contain nulls or duplicate values, it is not suitable for a primary key.

EDIT: Please read more on this, google is your friend: http://www.google.co...rch?q=ORA-02437

This post has been edited by baavgai: 13 April 2008 - 05:31 AM

Was This Post Helpful? 0
  • +
  • -

#7 BoneXXX  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 26-September 07

Re: Drop Primary Key

Posted 13 April 2008 - 05:38 AM

There are duplicated data but it was primary key before. I dropped the constraint and tried to make the column as primary key again but I can't do it. So is there any other solution for this case? Thanks
Was This Post Helpful? 0
  • +
  • -

#8 BoneXXX  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 26-September 07

Re: Drop Primary Key

Posted 13 April 2008 - 06:03 AM

OR how can I just make the col1 as a primary key and col2 as a foreign key on the created table. I can not create the table from beginning. That is what I am trying to do.

CREATE TABLE tablename
(
col1	  INTEGER,
col2	  INTEGER,
col3	  NUMBER(1),
	 CONSTRAINT pk_tablename PRIMARY KEY (col1,col2)
);

Was This Post Helpful? 0
  • +
  • -

#9 BoneXXX  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 26-September 07

Re: Drop Primary Key

Posted 13 April 2008 - 06:42 AM

I guess, I shouldn't remove any of the primary key consists col1 or col2, or if I do; I have to remove the duplicate data or the null data, right?
Was This Post Helpful? 0
  • +
  • -

#10 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Drop Primary Key

Posted 13 April 2008 - 08:13 AM

You can, at least with Microsoft SQL Server, have multiple column as your primary key. When you go this route it is called a composite key
Was This Post Helpful? 0
  • +
  • -

#11 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


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

Re: Drop Primary Key

Posted 13 April 2008 - 10:38 AM

View PostBoneXXX, on 13 Apr, 2008 - 09:42 AM, said:

I have to remove the duplicate data or the null data, right?


Yep, that would be the way to go if you want to apply a primary key there.

You can make a copy of the table to play with it. Maybe test out difference scenarios. You appear to be using Oracle, you can use this syntax to copy a table:

create table new_tablename as
   select * from tablename


Was This Post Helpful? 0
  • +
  • -

#12 born2c0de  Icon User is offline

  • printf("I'm a %XR",195936478);
  • member icon

Reputation: 187
  • View blog
  • Posts: 4,673
  • Joined: 26-November 04

Re: Drop Primary Key

Posted 13 April 2008 - 11:57 PM

Quote

A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

Note:

In Oracle, a primary key can not contain more than 32 columns.

A primary key can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

Source : TechOntheNet

Quote

Every table in Oracle has a field or a combination of fields that uniquely identifies each record in the table.

Source : IslandNet

Quote

A unique key or primary key comprises a single column or set of columns

Source: Wikipedia

Google is your friend. ;)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1