Solved

oracle 10G

Posted on 2016-09-08
5
59 Views
Last Modified: 2016-09-12
Adding a column (org_id_mfg) to a table called sales_profile_items and
make this org_id_mfg a FK to Organizations Table and create FK Index.

Alter TABLE sales_profile_items
add org_id_mfg number(38) not null;

ALTER TABLE sales_profile_items
ADD CONSTRAINT fk_org_id_mfg
  FOREIGN KEY (org_id_mfg)
  REFERENCES organizations(id);
  




CREATE INDEX DSS.ORG_ORG_ID_MFG_FK_I ON DSS.ORGANIZATIONS
(ORG_ID_MFG)
LOGGING
TABLESPACE MISC_I
PCTFREE    5
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

Open in new window


Please let me know if this is valid?
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 total points
ID: 41789607
The parent table (dss.organizations) must have a unique or primary constraint on the FK column.
The constraint will have a supporting index (that may or may not be unique).  

And that parent table constraint must exist prior to creating the child table's FK constraint.

If there will be a lot of deletes or updates to the parent table's constrained column then you might want an index on the child table's constrained column as well.  Otherwise, the dml on the parent will require a table lock on the child while it checks if the constraint would be invalidated.
0
 
LVL 23

Assisted Solution

by:David
David earned 250 total points
ID: 41789611
Almost. Your primary or parent table should already have an index. As to the column name being ID, I'd have to check my memory if that is a reserved word.

You will need a unique index on your new column in the child table. Prior to the constraint, add an index.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41789737
IN THE ORGANIZATIONS TABLE WE HAVE ORG_ID THAT IS A PRIMARY KEY. SO I WANT THE ORG_ID_MFG FROM SALES_PROFILE_ITEMS TABLE TO BE A FOREIGN KEY TO ORG_ID. AND ALSO CREATE INDEX ON THE FOREIGN KEY.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 41789855
if you already have a PK on org_id for the organizations table  then your FK should reference that column, not the ID column

ALTER TABLE sales_profile_items
ADD CONSTRAINT fk_org_id_mfg
  FOREIGN KEY (org_id_mfg)
  REFERENCES organizations(org_id);  --- note I changed the column name to ORG_ID

Open in new window


also, if you want to create an index on the sales_profile_items, then you'll need to specify that table in your index creation, not the parent table


CREATE INDEX DSS.ORG_ORG_ID_MFG_FK_I ON DSS.sales_profile_items   -- note I changed the table name
(ORG_ID_MFG)
LOGGING
TABLESPACE MISC_I
PCTFREE    5
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

Open in new window



finally, your initial alter table to add the column will fail unless the table is empty.
The reason is, you can't create a NOT NULL column on a populated table, because the column needs to have a value.

So, you must either create the column without the NOT NULL condition, or create the column with a default value so it will be populated at the same time the column is created
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 41794527
thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
dbms_crypto.decrypt   errors out 6 61
make null the repeated levels 2 50
oracle numeric condition check 4 48
return value based on substr 10 73
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question