OraFAQ Forum: SQL & PL/SQL » Alter Table Command in relation to another table primary key

Home » SQL & PL/SQL » SQL & PL/SQL » Alter Table Command in relation to another table primary key (Oracle SQL Developer 4.0.0.13)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Alter Table Command in relation to another table primary key [message #608296]

Tue, 18 February 2014 08:12

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

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

cookiemonster
Messages: 13920
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.

Report message to a moderator

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

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

Report message to a moderator

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

cookiemonster
Messages: 13920
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

Report message to a moderator

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

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You should also consider naming your fk.

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

the books you should be using can be found at URL below

http://docs.oracle.com/cd/E16655_01/index.htm

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

does posted SQL do what you desire & not throw any error?

Report message to a moderator

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

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.

Report message to a moderator

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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/

Report message to a moderator

Previous Topic:	First Character in "numeric-Alpha"
Next Topic:	Query help

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 18:46:59 CDT 2024