Dropping a Primary Key constraint [message #669340] |
Mon, 16 April 2018 11:13 |
buggleboy007
Messages: 282 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 #669342 is a reply to message #669341] |
Mon, 16 April 2018 11:36 |
buggleboy007
Messages: 282 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 |
John Watson
Messages: 8951 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 #669349 is a reply to message #669345] |
Mon, 16 April 2018 12:55 |
pablolee
Messages: 2882 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 |
buggleboy007
Messages: 282 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 |
John Watson
Messages: 8951 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 |
buggleboy007
Messages: 282 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 #669364 is a reply to message #669350] |
Tue, 17 April 2018 08:17 |
joy_division
Messages: 4963 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 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
EdStevens wrote on Tue, 17 April 2018 06:32buggleboy007 wrote on Mon, 16 April 2018 13:51Primary 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 |
buggleboy007
Messages: 282 Registered: November 2010 Location: Canada
|
Senior Member |
|
|
John Watson wrote on Tue, 17 April 2018 00:40For 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 |
buggleboy007
Messages: 282 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.
|
|
|