CODEWITHSUNDEEP
sqloracle-databaseplsql
Mohammad Alqurm
Mohammad Alqurm

Reputation: 587

Reference a foreign key to a different type

Is it possible to do this in PL/SQL:

CREATE TABLE ROOM_TYPE
(
   ROOM_TYPE_ID                     NUMBER NOT NULL PRIMARY KEY,
   REF_SERVICE_PROVIDER             NUMBER NOT NULL,
   ROOM_TYPE_NAME1                  VARCHAR2(100) NOT NULL,
   ROOM_TYPE_NAME2                  VARCHAR2(100),
   REF_SERVICE_ID                   NUMBER(15,3),
   ROOM_AC                          NUMBER(1),
   ROOM_BATH_ROOM                   NUMBER(1),
   ROOM_CHOICE_OF_MEAL              NUMBER(1),


   CONSTRAINT fk_ref_service_provider
   FOREIGN KEY (REF_SERVICE_PROVIDER)
   REFERENCES PROVIDER(PROVIDER_ID),

   CONSTRAINT fk_ref_service_id
   FOREIGN KEY (REF_SERVICE_ID)
   REFERENCES MEDICAL_SERVICE(SERVICE_ID)
);

While the REF_SERVICE_ID of type number(15,3) is a foreign key in this table which refers to a primary key SERVICE_ID of type number.

The question is, can I refer to a primary key of type number while the foreign key is number(15,3) type !?

Upvotes: 0

Views: 98

Answers (1)

ajmalmhd04
ajmalmhd04

Reputation: 2602

Yes you can, but there is a big chance getting following error:

ORA-01438: value larger than specified precision allowed for this column

For eg:

CREATE TABLE A ( ID NUMBER NOT NULL PRIMARY KEY);
CREATE TABLE b
     (
          id  NUMBER(15,3),
          val CHAR(1),
          CONSTRAINT b_pk FOREIGN KEY (ID) REFERENCES a(id)
     );

     INSERT INTO A VALUES(456);
     INSERT INTO B VALUES( 456, 'X');  --inserts successfully

 INSERT INTO A VALUES(12345678901234567890);
 INSERT INTO B VALUES( 12345678901234567890, 'X'); --errors

Upvotes: 1

Related Questions