OraFAQ Forum: SQL & PL/SQL » Primary key ---- Urgent plse

Home » SQL & PL/SQL » SQL & PL/SQL » Primary key ---- Urgent plse

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

Primary key ---- Urgent plse [message #11192]

Thu, 11 March 2004 00:24

K.B.Shesha Saai
Messages: 14
Registered: February 2004

Junior Member

In oracle, I�have a table dept .. in which there are columns deptno, dname.� I have created primary key on both the columns.� Now i am creating emp table, in which i have columns like empno, ename and deptno.� For deptno of emp table, i want to refer to the deptno of the dept table.� Since there was a composite primary key on the dept table (on two cols), how can i refer deptno of emp column to deptno column of dept table ?

�

Please clarify

Thanks

Report message to a moderator

Re: Primary key ---- Urgent plse [message #11193 is a reply to message #11192]

Thu, 11 March 2004 01:07

Thato
Messages: 8
Registered: January 2004

Junior Member

try this:

Create table emp
(empno number,
ename varchar2(15),
deptno number constraint fk_dept references dept(deptno));

Report message to a moderator

Re: Primary key ---- Urgent plse [message #11205 is a reply to message #11192]

Sat, 13 March 2004 11:58

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

You need to add a unique key to the dept table on the deptno column. Please see the example below that demonstrates that it doesn't work without the unique key on the deptno column of the dept table and that it does work with the unique key on the deptno column of the dept table. This key provides something for the deptno column of the emp table to reference.

-- initial tables that you described:
scott@ORA92> CREATE TABLE dept_table
  2    (deptno NUMBER,
  3  	dname  VARCHAR2(14))
  4  /

Table created.

scott@ORA92> ALTER TABLE dept_table
  2  ADD CONSTRAINT dept_table_deptno_dname_pk
  3  PRIMARY KEY (deptno, dname)
  4  /

Table altered.

scott@ORA92> CREATE TABLE emp_table
  2    (empno  NUMBER,
  3  	ename  VARCHAR2(10),
  4  	deptno NUMBER)
  5  /

Table created.

scott@ORA92> -- doesn't work:
scott@ORA92> ALTER TABLE emp_table
  2  ADD CONSTRAINT emp_table_deptno_fk
  3  FOREIGN KEY (deptno) REFERENCES dept_table (deptno)
  4  /
FOREIGN KEY (deptno) REFERENCES dept_table (deptno)
                                            *
ERROR at line 3:
ORA-02270: no matching unique or primary key for this column-list

scott@ORA92> -- works:
scott@ORA92> ALTER TABLE dept_table
  2  ADD CONSTRAINT dept_table_deptno_uk
  3  UNIQUE (deptno)
  4  /

Table altered.

scott@ORA92> ALTER TABLE emp_table
  2  ADD CONSTRAINT emp_table_deptno_fk
  3  FOREIGN KEY (deptno) REFERENCES dept_table (deptno)
  4  /

Table altered.

Report message to a moderator

Re: Primary key ---- Urgent plse [message #12204 is a reply to message #11205]

Fri, 07 May 2004 00:05

Amit
Messages: 166
Registered: February 1999

Senior Member

But, After creating a unique contraint on deptno in dept_table, then it won't allow to insert duplicate values for deptno which defeates the reason of creating composite Pk on deptno,dname.If I am having duplicate values in deptno because of which I had to create composite Pk then after creating Uk on deptno, I won't be able to insert deplicate records for deptno because of UK violations.

Pl. advise, because I am facing this problem in one of my scenario.

Report message to a moderator

Re: Primary key ---- Urgent plse [message #12218 is a reply to message #12204]

Fri, 07 May 2004 22:15

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

You can create an additional table, such as the deptno_table in the example below.

scott@ORA92> create table deptno_table
  2    (deptno			       number,
  3  	constraint deptno_table_deptno_pk primary key (deptno))
  4  /

Table created.

scott@ORA92> create table dept_table
  2    (deptno			       number,
  3  	dname			       varchar2(14),
  4  	constraint dept_table_deptno_dname_pk primary key (deptno, dname),
  5  	constraint dept_table_deptno_fk foreign key (deptno) references deptno_table (deptno))
  6  /

Table created.

scott@ORA92> create table emp_table
  2    (empno			       number,
  3  	ename			       varchar2(10),
  4  	deptno			       number,
  5  	constraint emp_table_deptno_fk foreign key (deptno) references deptno_table (deptno))
  6  /

Table created.

scott@ORA92> insert into deptno_table (deptno) values (1)
  2  /

1 row created.

scott@ORA92> insert into dept_table (deptno, dname) values (1, 'deptno1a')
  2  /

1 row created.

scott@ORA92> insert into dept_table (deptno, dname) values (1, 'deptno1b')
  2  /

1 row created.

scott@ORA92> insert into emp_table (empno, ename, deptno) values (10, 'name1', 1)
  2  /

1 row created.

scott@ORA92> insert into emp_table (empno, ename, deptno) values (20, 'name2', 1)
  2  /

1 row created.

scott@ORA92> select * from deptno_table
  2  /

    DEPTNO
----------
         1

scott@ORA92> select * from dept_table
  2  /

    DEPTNO DNAME
---------- --------------
         1 deptno1a
         1 deptno1b

scott@ORA92> select * from emp_table
  2  /

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
        10 name1               1
        20 name2               1

Report message to a moderator

Previous Topic:	ora_00106
Next Topic:	Copying a table structure without copying the table itself.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 09:08:54 CDT 2024