Home » SQL & PL/SQL » SQL & PL/SQL » Dropping a Primary Key constraint (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0, Windows 10 Professional)
Dropping a Primary Key constraint [message #669340] Mon, 16 April 2018 11:13 Go to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
Hi all,

I am trying to propose a solution for a table which has a column that is PRIMARY KEY(actually it has set of 3 columns that make up the PRIMARY KEY) and the same column is a FOREIGN KEY. This is causing an issue when this column has a NULL value (as a trigger is getting fired and this NULL value is becoming a show stopper)

My proposed solution is to drop the PRIMARY KEY and recreate the PRIMARY KEY with the other existing column. This will take care of the issue (IMO). But before I propose the solution, I would like to know the what could be the impact. One is :

a) DROPPING PRIMARY KEY does not drop the UNIQUE INDEX(https://community.oracle.com/thread/369159)

Any other possible impact experience that one can share?

Re: Dropping a Primary Key constraint [message #669341 is a reply to message #669340] Mon, 16 April 2018 11:30 Go to previous messageGo to next message
John Watson
Messages: 7702
Registered: January 2010
Location: Global Village
Senior Member
I do not understand what you are saying. Is the PK on one column, or three? What about the FK?

It would probably be best if you post the DDL to create both the table and the keys.



Re: Dropping a Primary Key constraint [message #669342 is a reply to message #669341] Mon, 16 April 2018 11:36 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
CREATE TABLE MARITAL_STATUS_HIST" 
   (	"MAR_STAT_NO" NUMBER(2,0), 
	"TAX_PAYER_NO" NUMBER(25,0), 
	"START_DATE" DATE, 
	"END_DATE" DATE, 
	 CONSTRAINT "MARSTATHIST_DATES_CHK" CHECK (end_date is null or trunc(end_date)>=trunc(start_date) ) ENABLE, 
	 CONSTRAINT "MARSTATHIST_ENDDT_CHK" CHECK (end_date is null or end_date between date'1900-01-01' and date'2100-01-01' ) ENABLE, 
	 CONSTRAINT "MARSTATHIST_STARTDT_CHK" CHECK (start_date between date'1900-01-01' and date'2100-01-01' ) ENABLE, 
	 CONSTRAINT "MARITAL_STATUS_HIST_PK" PRIMARY KEY ("TAX_PAYER_NO", "START_DATE", "MAR_STAT_NO")
);

Above statement is the DDL for the table in question.

If you DESC it, then it appears as follows:

DESC marital_status_hist
Name         Null     Type       
------------ -------- ---------- 
MAR_STAT_NO  NOT NULL NUMBER(2)  
TAX_PAYER_NO NOT NULL NUMBER(25) 
START_DATE   NOT NULL DATE       
END_DATE              DATE  
So, MAR_STAT_NO, TAX_PAYER_NO,START_DATE are all the PRIMARY KEYS on the table and at the same time

MAR_STAT_NO, END_DATE are Foreign Keys as well.

My solution was to remove MAR_STAT_NO as Primary Key and only have MAR_STAT_NO as FOREIGN Key. Before I can ensure that the solution flies, I would like to know if there would be any other impacts (other than what I thought of)

[Updated on: Mon, 16 April 2018 11:37]

Report message to a moderator

Re: Dropping a Primary Key constraint [message #669344 is a reply to message #669342] Mon, 16 April 2018 11:52 Go to previous messageGo to next message
John Watson
Messages: 7702
Registered: January 2010
Location: Global Village
Senior Member
You will cause less confusion if you remember that a table has one primary key, which may be composed of several columns.

You cannot have a nullable column as part of a primary key. However, you can have nullable columns as part of a unique key, so perhaps drop the PK and add UK instead. Whether you drop or keep the index when dropping the constraint is dependent on the syntax you use (look it up) but by default you will drop the index if it was created implicitly rather than being created first (which is better practice).



Re: Dropping a Primary Key constraint [message #669345 is a reply to message #669344] Mon, 16 April 2018 11:55 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
Thanks John for your reply. I will test mine and your solutions in development but are there any other impacts that you are aware of?

[Updated on: Mon, 16 April 2018 11:55]

Report message to a moderator

Re: Dropping a Primary Key constraint [message #669349 is a reply to message #669345] Mon, 16 April 2018 12:55 Go to previous messageGo to next message
pablolee
Messages: 2880
Registered: May 2007
Location: Scotland
Senior Member
Have you had the business look at this solution? Removing a column from a primary key is generally a pretty significant action and shouldn't be done without a proper impact assessment. Are you sure that the other two columns are all that is required to create a unique key?
Simply because the data in the table, at this point in time, allows for the two column pk, does not necessarily mean that the 2 column key will be enough over time, presumably those 3 columns were chosen to be the PK for a reason.

[Updated on: Mon, 16 April 2018 12:57]

Report message to a moderator

Re: Dropping a Primary Key constraint [message #669350 is a reply to message #669349] Mon, 16 April 2018 13:06 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
If you ask me, the way the table is created itself is incorrect. However since it has been working for such a long time, I do not want to touch anything else other than looking for a band-aid fix at this moment. Yes, I will have to know the impacts, and I am on my way with it. However what I want to know is:
a)what could be the impacts that can come up when proposing a solution like mine. In case this is not a good way of doing it, then
b)what is a better way of doing it

Yes, I will also have to check with the person who raised this issue, as to why the same column was created as a PRIMARY KEY, when a FOREIGN KEY already existed on it or vice versa.

[Updated on: Mon, 16 April 2018 13:07]

Report message to a moderator

Re: Dropping a Primary Key constraint [message #669351 is a reply to message #669350] Mon, 16 April 2018 13:17 Go to previous messageGo to next message
John Watson
Messages: 7702
Registered: January 2010
Location: Global Village
Senior Member
You have to explain yourself better. This,Quote:
the same column was created as a PRIMARY KEY, when a FOREIGN KEY already existed on it or vice versa.
is nonsense. Your PK is not one column, it is three. You have not said yet what the FK is.
Re: Dropping a Primary Key constraint [message #669352 is a reply to message #669351] Mon, 16 April 2018 13:51 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
Primary Keys on the table are: mar_stat_no, tax_payer_no,start_date
foreign keys on the table are: mar_stat_no, tax_payer_no

I have so far not come across the same key being primary key and foreign key. May be there is a reason why it has been put this way, but not sure.
Re: Dropping a Primary Key constraint [message #669358 is a reply to message #669352] Tue, 17 April 2018 00:40 Go to previous messageGo to next message
John Watson
Messages: 7702
Registered: January 2010
Location: Global Village
Senior Member
For the third time, please use the correct terminology. You do not have three primary keys, you have one. You have still not described your foreign constraint(s).
Re: Dropping a Primary Key constraint [message #669361 is a reply to message #669352] Tue, 17 April 2018 06:32 Go to previous messageGo to next message
EdStevens
Messages: 1021
Registered: September 2013
Senior Member
buggleboy007 wrote on Mon, 16 April 2018 13:51
Primary Keys on the table are: mar_stat_no, tax_payer_no,start_date
foreign keys on the table are: mar_stat_no, tax_payer_no

I have so far not come across the same key being primary key and foreign key. May be there is a reason why it has been put this way, but not sure.

The term "primary keyS" (plural) is a non-starter. A table can only have one, and only one, primary key. You do not have Primay KeyS. You have one single primary key, that is made up of three columns. The fact that you repeatedly address this as if each individual column were itself a primary key indicates that you do not have the concept clear in your thinking.
Re: Dropping a Primary Key constraint [message #669362 is a reply to message #669340] Tue, 17 April 2018 06:51 Go to previous messageGo to next message
EdStevens
Messages: 1021
Registered: September 2013
Senior Member
Also asked at https://community.oracle.com/thread/4137868
Re: Dropping a Primary Key constraint [message #669364 is a reply to message #669350] Tue, 17 April 2018 08:17 Go to previous messageGo to next message
joy_division
Messages: 4920
Registered: February 2005
Location: East Coast USA
Senior Member
buggleboy007 wrote on Mon, 16 April 2018 14:06

a)what could be the impacts that can come up when proposing a solution like mine. In case this is not a good way of doing it, then
For one, you might not be able to create a primary key if you drop one of the columns from the original PK.

p.s. And it is annoying that you keep saying primary keys after being told there is only ONE primary key.
Re: Dropping a Primary Key constraint [message #669376 is a reply to message #669361] Wed, 18 April 2018 08:57 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
EdStevens wrote on Tue, 17 April 2018 06:32
buggleboy007 wrote on Mon, 16 April 2018 13:51
Primary Keys on the table are: mar_stat_no, tax_payer_no,start_date
foreign keys on the table are: mar_stat_no, tax_payer_no

I have so far not come across the same key being primary key and foreign key. May be there is a reason why it has been put this way, but not sure.
The term "primary keyS" (plural) is a non-starter. A table can only have one, and only one, primary key. You do not have Primay KeyS. You have one single primary key, that is made up of three columns. The fact that you repeatedly address this as if each individual column were itself a primary key indicates that you do not have the concept clear in your thinking.

Thanks for pointing out Ed Stevens. Will keep this in mind. If my concepts were very clear and I was a master, then I would not have visited this site for help. But yes, good to know that there is Primary Key is always singular and not plural.
Re: Dropping a Primary Key constraint [message #669377 is a reply to message #669358] Wed, 18 April 2018 08:58 Go to previous messageGo to next message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
John Watson wrote on Tue, 17 April 2018 00:40
For the third time, please use the correct terminology. You do not have three primary keys, you have one. You have still not described your foreign constraint(s).
Thanks John for pointing out the mistake. Point noted.
Re: Dropping a Primary Key constraint [message #669378 is a reply to message #669377] Wed, 18 April 2018 10:22 Go to previous message
buggleboy007
Messages: 198
Registered: November 2010
Location: Canada
Senior Member
Anyways the solution that I have come up with are the following:

a) write a validation in front-end itself to prevent the user from navigating and committing the record if MAR_STAT_NO is NULL
or
b) In case the MAR_STAT_NO is NULL in the front-end, in the trigger change the algorithm to ensure that if the MAR_STAT_NO is NULL, then have a default value so that the application does not report any run time error.

Modifying/altering the table structure is a bit risky as the tremors can be hard to correct. This is my approach.


Previous Topic: modification date for package SP
Next Topic: SQL rollup count every day
Goto Forum:
  


Current Time: Sat Dec 15 22:30:27 CST 2018