Solved

oracle 10G

Posted on 2016-09-08
5
58 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

756 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