Solved

oracle 10G

Posted on 2016-09-08
5
48 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
  • 2
  • 2
5 Comments
 
LVL 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now