foreign key on table oracle
SELECT c.OWNER, a.TABLE_NAME, a.COLUMN_NAME, a.CONSTRAINT_NAME,
c.R_OWNER AS REF_OWNER, cpk.TABLE_NAME AS REF_TABLE,
cpk.CONSTRAINT_NAME AS REF_PK
FROM ALL_CONS_COLUMNS a
JOIN ALL_CONSTRAINTS c ON a.OWNER = c.OWNER
AND a.CONSTRAINT_NAME = c.CONSTRAINT_NAME
JOIN ALL_CONSTRAINTS cpk ON c.R_OWNER = cpk.OWNER
AND c.R_CONSTRAINT_NAME = cpk.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'R' AND c.TABLE_NAME= 'table_name';how to create table in oracle sql syntax example and foreign key
CREATE TABLE hr.admin_emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
ssn NUMBER(9) ENCRYPT,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
photo BLOB,
sal NUMBER(7,2),
hrly_rate NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
comm NUMBER(7,2),
deptno NUMBER(3) NOT NULL
CONSTRAINT admin_dept_fkey REFERENCES hr.departments
(department_id))oracle foreign key reference table
SELECT cons.OWNER AS CHILD_OWNER,
cons.TABLE_NAME AS CHILD_TABLE,
cons.CONSTRAINT_NAME AS CONSTAINT_NAME,
cons.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
col.OWNER AS PARENT_OWNER,
col.TABLE_NAME AS PARENT_TABLE,
col.COLUMN_NAME AS COLUMN_NAME
FROM DBA_CONS_COLUMNS col, DBA_CONSTRAINTS cons
WHERE cons.R_OWNER = col.OWNER
AND cons.R_CONSTRAINT_NAME = col.CONSTRAINT_NAME
AND cons.TABLE_NAME = 'table_name';Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
