4 Replies Latest reply: Oct 13, 2012 3:33 AM by Ashu_Neo RSS

    oracle sql problem

    968105
      hello!

      PROBLEM 1: how do you drop foreign key constraint without constraint name?

      PROBLEM 2:i created two tables employee and department.

      CREATE TABLE EMPLOYEE
      (
      FNAME VARCHAR (25) NOT NULL,
      MINIT VARCHAR(10),
      LNAME VARCHAR(25) NOT NULL,
      SSN CHAR(9),
      BDATE DATE,
      ADDRESS VARCHAR(30),
      SEX CHAR,
      SALARY DECIMAL(10,2),
      SUPERSSN CHAR(9),
      DNO INT,
      PRIMARY KEY (SSN),
      FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN)
      );

      CREATE TABLE DEPARTMENT
      (
      DNAME VARCHAR(25),
      DNUMBER INT NOT NULL,
      MGRSSN CHAR(9),
      MGRSTARTDATE DATE,
      PRIMARY KEY(DNUMBER),
      FOREIGN KEY(MGRSSN) REFERENCES EMPLOYEE(SSN)
      );

      ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DNO) REFERENCES DEPARTMENT(DNUMBER);

      i then made DNO foreign key.
      when i was trying to enter values

      INSERT INTO EMPLOYEE
      VALUES ('john','b','smith',123456789,'1965-01-09','731 fondren, houston,tx','m',30000,'333445555',5);

      it shows this error
      ORA-01722: invalid number

      why it is showing me this error and how do i fix it?

      thanks.
        • 1. Re: oracle sql problem
          ShankarViji
          Hi User,

          Solution 1 :
          SELECT * FROM USER_CONSTRAINTS WHERE table_name ='EMPLOYEE';
          
          ALTER TABLE employee DROP CONSTRAINT SYS_C0010744;
          Solution 2 :
          INSERT INTO employee
                      (fname, minit, lname, ssn,
                       bdate, address, salary,
                       superssn, dno
                      )
               VALUES ('john', 'b', 'smith', 123456789,
                       TO_DATE ('1965-01-09', 'YYYY-MM-DD'), '731 fondren', 30000,
                       123456789, 30000
                      );
          Thanks,
          Shankar
          • 2. Re: oracle sql problem
            560009
            1. query USER_CONSTRAINTS or ALL_CONSTRAINTS tables to get FOREIGN KEY details for a table
            For ex: select * from all_constraints where table_name = 'EMP' AND CONSTRAINT_TYPE = 'R';
            OR select * from USER_CONSTRAINTS where table_name = 'EMP' AND CONSTRAINT_TYPE = 'R';

            AND then use DROP foreing keys

            2. SSN data type is CHAR(9) use single quotes and for date to_date function llike below

            INSERT INTO EMPLOYEE VALUES ('john','b','smith','123456789',to_date('1965-01-09', 'yyyy-mm-dd'),'731 fondren, houston,tx','m',30000,'333445555',5);
            • 3. Re: oracle sql problem
              HuaMin Chen
              Hi,
              I don't understand why you put superssn on the same table as the FK that references the PK of the same table. It is fine by these

              CREATE TABLE EMPLOYEE
              (
              FNAME VARCHAR (25) NOT NULL,
              MINIT VARCHAR(10),
              LNAME VARCHAR(25) NOT NULL,
              SSN CHAR(9),
              BDATE DATE,
              ADDRESS VARCHAR(30),
              temp CHAR,
              SALARY DECIMAL(10,2),
              SUPERSSN CHAR(9),
              DNO INT,
              PRIMARY KEY (SSN)
              );

              INSERT INTO EMPLOYEE
              VALUES ('john','b','smith',123456789,to_date('1965-01-09','yyyy-mm-dd'),'731 fondren, houston,tx','m',30000,'333445555',5);
              • 4. Re: oracle sql problem
                Ashu_Neo
                Check comments below.
                INSERT INTO EMPLOYEE
                VALUES (
                'john',
                'b',
                'smith',
                '123456789',
                TO_DATE('1965-01-09','YYYY-DD-MM'), /* use to_date to convert string to date value */
                '731 fondren, houston,tx',
                'm',
                30000,
                '123456789',/*--'333445555', commented because superssn should be equal to ssn number as fk created*/
                5);
                
                Add on :- later you created a FK on Employee.DNO. Make sure, that you have a entry of dno = 5 in Department table.
                Thanks!