Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Sunday, 16 July 2017

SQL Create Table Foreign Key


This is used to reference the parent table primary key column which allows duplicates.
Foreign key always attached to the child table.
We can add this constraint in table and alter levels only.
Example:

TABLE LEVEL FOREIGN KEY    


 SQL>CREATE TABLE emp
(
   empno    NUMBER (2),
   ename    VARCHAR (10),
   deptno   NUMBER (2),
   PRIMARY KEY (empno),
   FOREIGN KEY (deptno) REFERENCES dept (deptno)
);
SQL>CREATE TABLE emp
(
   empno    NUMBER (2),
   ename    VARCHAR (10),
   deptno   NUMBER (2),
   CONSTRAINT pk PRIMARY KEY (empno),
   CONSTRAINT fk FOREIGN KEY (deptno) REFERENCES dept (deptno)
);

ALTER LEVEL FOREIGN KEY

   

SQL> ALTER TABLE emp ADD FOREIGN KEY(deptno) REFERENCES dept(deptno);
SQL> ALTER TABLE emp ADD CONSTRAINT fk FOREIGN KEY(deptno) REFERENCES dept(deptno);
Once the primary key and foreign key relationship has been created then you can not remove any parent record if the dependent childs exists.
Share this article :

2 comments:

  1. Hi Dayakar,

    Zoooooooom! That’s how speedy and easy this read was! Looking forward to more of such powerful content on SQL Create Table Foreign Key

    I need your help please, I am new at PL/SQL

    I need to run a select command with multiple values, if a break down this query into 3 pieces and run them,they works perfectly, but Ive got problems whenever i execute this query because there are multiple values into the where clause and i think its necessary to create lines break so it may work perfectly

    how can I get through this?

    SELECT job_name, command,description,days_of_week,start_mins,machine, J2.CREATE_STAMP, J2.MODIFY_STAMP
    FROM MDBADMIN.UJO_JOBROW JR JOIN MDBADMIN.ujo_job2 J2
    ON (JR.JOID = J2.JOID)
    WHERE job_name IN ('coscl_PM_zip_log','coscl_PM_clear_log','coscl_PM_alarma_fs','coscl_PM_pmapp1_PM_up','coscl_PM_control_recargas_cod_11','coscl_PM_control_recargas_ingreso','coscl_PM_control_rec_error_cc_agrup_v1'.'coscl_PM_control_procesos_diarios','coscl_PM_rep_recar_x_estado','coscl_PM_control_tiempos_recargas',
    'coscl_PM_control_porcentaje','coscl_PM_control_puertos_bloq','coscl_PM_control_errores', 'coscl_PM_control_java','coscl_PM_control_error_cache');

    Thank you very much and will look for more postings from you.

    ,Merci
    Irene Hynes

    ReplyDelete
  2. Hello There,

    Fully agree on SQL Create Table Foreign Key. We’re seeing a lot of projects tackle big complex problems but few seem to have taken into consideration and in particular reasons to adopt.

    My table column type is clob. Field value contain double quotes

    id,text_clob,date

    1,"\"data1\",0,1,1,1,\"data2\"","2018-03-03 09:08:45"

    can you kindly help loader query. i cant use replace in clob field.
    It was cool to see your article pop up in my google search for the process yesterday. Great Guide.
    Keep up the good work!

    Thank you,
    Preethi.

    ReplyDelete