• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 71
  • Last Modified:

oracle 10G

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
anumoses
Asked:
anumoses
  • 2
  • 2
3 Solutions
 
sdstuberCommented:
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
 
DavidSenior Oracle Database AdministratorCommented:
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
 
anumosesAuthor Commented:
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
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now