3 Replies Latest reply: Jul 2, 2010 8:09 AM by 771626 RSS

    FOREIGN KEY CONSTRAINTS

    771626
      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE     11.2.0.1.0     Production
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production

      Hi all.
      I am having a problem adding a foreign key constraint to a table and was wondering if anyone had any ideas.
      I've made my example just about as simple as you can get.

      CREATE TABLE tparent(
      pid NVARCHAR2(10) NOT NULL,
      pid2 NVARCHAR2(10) NULL);
      CREATE UNIQUE INDEX ui_tparent ON tparent(pid);

      CREATE TABLE tchild(
      cid NVARCHAR2(10) NOT NULL,
      cid2 NVARCHAR2(10));
      CREATE UNIQUE INDEX ui_tchild ON tchild(cid);
      ALTER TABLE tchild ADD CONSTRAINT fk_tc_to_tp FOREIGN KEY (cid) REFERENCES tparent(pid);

      Error report:
      SQL Error: ORA-02270: no matching unique or primary key for this column-list
      02270. 00000 - "no matching unique or primary key for this column-list"
      *Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
      gives a column-list for which there is no matching unique or primary
      key constraint in the referenced table.
      *Action:   Find the correct column names using the ALL_CONS_COLUMNS
      catalog view

      ??? What - as you can see I have a unique index - ui_tparent.

      This works though if I use a PRIMARY KEY and not a UNIQUE INDEX?
      i.e
      ALTER TABLE tparent ADD CONSTRAINT pk_tparent PRIMARY KEY (pid);

      Ora docs state that in order to create a FK the parent needs either a UI or PK.

      Any ideas?

      regards,
        • 1. Re: FOREIGN KEY CONSTRAINTS
          Fahd.Mirza
          >
          ALTER TABLE tchild ADD CONSTRAINT fk_tc_to_tp FOREIGN KEY (cid) REFERENCES tparent(pid);
          you do not necessarily need to reference it as a foreign key, modify your above statement like the following example:

          ALTER TABLE childtable MODIFY ( EMP_ID VARCHAR2(20 CHAR) REFERENCES EMP (EMP_ID) ) ;

          regards
          • 2. Re: FOREIGN KEY CONSTRAINTS
            ZahKar
            as the error say:

            no matching unique or primary key for this column-list

            it requires unique or primary key, and a unique index is not a unique key.
            SQL> CREATE TABLE tparent(
            pid NVARCHAR2(10) NOT NULL,
            pid2 NVARCHAR2(10) NULL);  2    3
            
            Table created.
            
            SQL> alter table tparent add constraint tparent_uk unique (pid);
            
            Table altered.
            
            SQL> CREATE TABLE tchild(
            cid NVARCHAR2(10) NOT NULL,
            cid2 NVARCHAR2(10));  2    3
            
            Table created.
            
            SQL> ALTER TABLE tchild ADD CONSTRAINT fk_tc_to_tp FOREIGN KEY (cid) REFERENCES tparent(pid);
            
            Table altered.
            
            SQL>
            • 3. Re: FOREIGN KEY CONSTRAINTS
              771626
              Ahh, I see...

              In SQL SERVER a UNIQUE INDEX is enough but in Oracle you need to create a UNIQUE CONSTRAINT against your UNIQUE INDEX.

              i.e.

              ALTER TABLE tparent ADD CONSTRAINT uc_tparent UNIQUE (pid) USING INDEX ui_tparent;

              C'est la vie...