Solved

oracle 10G

Posted on 2016-09-08
5
52 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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 …
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…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

943 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

6 Experts available now in Live!

Get 1:1 Help Now