Home » SQL & PL/SQL » SQL & PL/SQL » Alter Table Command in relation to another table primary key (Oracle SQL Developer 4.0.0.13)
Alter Table Command in relation to another table primary key [message #608296] Tue, 18 February 2014 08:12 Go to next message
Lahkra
Messages: 10
Registered: February 2014
Junior Member
I have a question about the Alter Table command in Oracle. When adding a column to a table, how does this relate to the key in another table? For example, if table TEAM is made and table DEPARTMENT is made, the Alter Table command is used to add the DEPT_ID attribute (primary key of DEPARTMENT)to table TEAM, is there a line that needs to be created in the ADD command showing a reference? Like if in TEAM there is already a reference to another attribute from another table as a Foreign Key, would this type of reference be needed in the Alter Table command?

I've looked at many sites and searched here, but maybe my search was too broad. My book just shows the Alter Table command can add a column to a table, but it doesn't talk about the relationship between tables once that is done concerning keys. Otherwise, how does the system know where to update the needed information from?

Thank you
Re: Alter Table Command in relation to another table primary key [message #608297 is a reply to message #608296] Tue, 18 February 2014 08:17 Go to previous messageGo to next message
BlackSwan
Messages: 22485
Registered: January 2009
Senior Member
yes
yes
you tell Oracle


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

[Updated on: Tue, 18 February 2014 08:18]

Report message to a moderator

Re: Alter Table Command in relation to another table primary key [message #608299 is a reply to message #608296] Tue, 18 February 2014 08:20 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Generally you would use a seperate alter table statement to create the foreign key. the documentation contains all the details with examples.
Re: Alter Table Command in relation to another table primary key [message #608302 is a reply to message #608299] Tue, 18 February 2014 08:40 Go to previous messageGo to next message
Lahkra
Messages: 10
Registered: February 2014
Junior Member
OK, looking at the documentation a reference would look like this?:

ALTER TABLE TEAM
     ADD FOREIGN KEY (DEPT_ID NUMBER (3,0)) REFERENCES DEPARTMENT(DEPT_ID);



Thank you
Re: Alter Table Command in relation to another table primary key [message #608303 is a reply to message #608302] Tue, 18 February 2014 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
you can't put the datatype in the foreign key. The new column needs to be added seperately
Re: Alter Table Command in relation to another table primary key [message #608304 is a reply to message #608303] Tue, 18 February 2014 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should also consider naming your fk.
Re: Alter Table Command in relation to another table primary key [message #608309 is a reply to message #608304] Tue, 18 February 2014 08:55 Go to previous messageGo to next message
Lahkra
Messages: 10
Registered: February 2014
Junior Member
I thought I did name the FK (DEPT_ID) in the line. So it should be like this (Going by what my book says, though it shows multiple developers)?

CREATE TABLE DEPARTMENT (
     DEPT_ID  NUMBER(3,0)  NOT NULL  PRIMARY KEY);


ALTER TABLE TEAM
     ADD (DEPT_ID NUMBER (3,0));
ALTER TABLE TEAM
     MODIFY (DEPT_ID) REFERENCES DEPARTMENT (DEPT_ID);



This would make it referenced as a FK automatically? Only dealt with Oracle for 3 weeks, and have no idea how things work properly. The book I have covers many developers, and the documentation wasn't clear to me since I am still new to this.
Re: Alter Table Command in relation to another table primary key [message #608310 is a reply to message #608309] Tue, 18 February 2014 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 22485
Registered: January 2009
Senior Member
the books you should be using can be found at URL below

http://docs.oracle.com/cd/E16655_01/index.htm
Re: Alter Table Command in relation to another table primary key [message #608313 is a reply to message #608310] Tue, 18 February 2014 09:27 Go to previous messageGo to next message
Lahkra
Messages: 10
Registered: February 2014
Junior Member
OK, so looking at one of the books I get this?:

ALTER TABLE TEAM
    ADD(DEPT_ID NUMBER(3,0),
    CONSTRAINT  DEPT_ID_FK
        FOREIGN KEY (DEPT_ID)
          REFERENCES DEPARTMENT (DEPT_ID)
);


http://docs.oracle.com/cd/E16655_01/server.121/e17633/tablecls.htm#CNCPT89156
Re: Alter Table Command in relation to another table primary key [message #608314 is a reply to message #608313] Tue, 18 February 2014 09:38 Go to previous messageGo to next message
BlackSwan
Messages: 22485
Registered: January 2009
Senior Member
does posted SQL do what you desire & not throw any error?
Re: Alter Table Command in relation to another table primary key [message #608318 is a reply to message #608314] Tue, 18 February 2014 10:55 Go to previous messageGo to next message
Lahkra
Messages: 10
Registered: February 2014
Junior Member
Well, I know that I've created things in the past, inserted something later and then got errors, so it is not always known when something should work properly. For example, my code that I had previously in a different part of my database suddenly comes up with errors now, though it worked before.
Re: Alter Table Command in relation to another table primary key [message #608320 is a reply to message #608318] Tue, 18 February 2014 11:07 Go to previous message
BlackSwan
Messages: 22485
Registered: January 2009
Senior Member
fixing any error depends upon which error is thrown & what exactly was done that generated the error.
any INSERT involving a Foreign Key might throw error because no parent record exists, duplicate child record exists or something else is wrong.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Previous Topic: First Character in "numeric-Alpha"
Next Topic: Query help
Goto Forum:
  


Current Time: Wed Jul 23 13:27:02 CDT 2014

Total time taken to generate the page: 0.14702 seconds