CODEWITHSUNDEEP
oracle-databaseexists
Powerslave
Powerslave

Reputation: 457

Equivalent to IF NOT EXISTS in Oracle?

Following statement works in MSSQL:

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[FK_StationObjectsID]') AND parent_object_id = OBJECT_ID(N'[Attendance]'))
BEGIN
    ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid)
END

In Oracle I tried:

IF NOT EXISTS (SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_STATIONOBJECTSID' AND TABLE_NAME = 'ATTENDANCE') THEN
BEGIN
    ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid);
END;

but its giving me an error PLS-00103 "Encountered the symbol 'ALTER'..."

Upvotes: 2

Views: 26631

Answers (4)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Use execute immediate to fire ddl inside PLSQL block:

IF <condition> THEN 
    Execute immediate 'ALTER TABLE . . .';
END if;

Closest that come to not exists is a method given by Tom Kyte here

begin
  for i in (select count(*) cnt from dual 
            where not exists (
                SELECT *
                FROM USER_CONSTRAINTS 
                WHERE CONSTRAINT_NAME = 'FK_STATIONOBJECTSID' 
                AND TABLE_NAME = 'ATTENDANCE'
            )) loop
            if (i.cnt = 1) then
              execute immediate 'ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid)';
            end if;
  end loop;
end;
/

Simpler method would be:

declare
  n int := 0;
begin
  select count(*) into n
  from user_constraints
  where constraint_name = 'FK_STATIONOBJECTSID'
  and table_name = 'ATTENDANCE';
  if n = 0 then
    execute immediate 'ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid)';
  end if;
end;
/

See - https://dba.stackexchange.com/questions/37362/why-cant-we-write-ddl-statement-directly-into-the-pl-sql-block

Upvotes: 4

David Aldridge
David Aldridge

Reputation: 52336

How about executing the DDL, and ignoring any "foreign key already exists" error.

Not elegant, but not dependent on the key name not changing.

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You can use EXISTS in a SQL query, but not in a PLSQL condition the way you tried.

You may need the following:

declare
    vCheck number;
begin
    select count(1)
    into vCheck
    from user_constraints
    where constraint_name = 'FK_STATIONOBJECTSID'
      and table_name = 'ATTENDANCE';
    --
    if vCheck = 0 then
        execute immediate 'ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid)';
    end if;
end;

Upvotes: 1

Powerslave
Powerslave

Reputation: 457

Got it working, possibly not the best solution though:

DECLARE
  foreign_key_exists number := 0;  
BEGIN
  SELECT COUNT(*) INTO foreign_key_exists FROM USER_CONSTRAINTS WHERE upper(CONSTRAINT_NAME) = upper('FK_StationObjectsID') AND upper(TABLE_NAME) = upper('Attendance');
  IF (foreign_key_exists = 0) 
    THEN
      EXECUTE IMMEDIATE 'ALTER TABLE Attendance ADD CONSTRAINT FK_StationObjectsID FOREIGN KEY (StationObjectsID) REFERENCES stationobjects (stationobjectsid)';
  END IF;
END;
/

Upvotes: 0

Related Questions