Santhosh Reddy Podduturi, November 24, 2016 - 12:40 pm UTC
In any special cases, does foreiegn key exist without a primary key?
November 24, 2016 - 12:46 pm UTC
You can point an FK to a unique constraint:
create table t1 (
x int constraint uc unique
);
create table t2 (
x int constraint fk references t1(x)
);
Santhosh Reddy Podduturi, November 24, 2016 - 12:58 pm UTC
So a fk always pointer to either a pk or unique.am i correct?
November 24, 2016 - 1:12 pm UTC
Yes.
Santhosh Reddy Podduturi, November 24, 2016 - 1:25 pm UTC
How to know this letters P,F,C,U defination(user_constraint table constrint type contains this letters)?is there any table explains the letters w,e,p,f,c,u.
November 24, 2016 - 2:02 pm UTC
Santhosh Reddy Podduturi, November 24, 2016 - 2:03 pm UTC
Hi,
I executed below query,I noticed some some Foreign keys not pointing to any constraint either unique or primary
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Nov 24 19:25:52 2016
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT CONSTRAINT_TYPE,R_CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE rownum
<5 AND constraint_type ='F';
C R_CONSTRAINT_NAME
- ------------------------------
F
F
F
F
SQL>
November 24, 2016 - 5:02 pm UTC
What does the DDL for those tables show?
santhoshreddy podduturi, November 24, 2016 - 11:43 pm UTC
Actulaay those tables are predefined tables like dba_constraints by Oracle apps(EBS).
November 25, 2016 - 8:00 am UTC
constraint type F is not a foreign key. "R" is a foreign key
From the docs:
C - Check constraint on a table
P - Primary key
U - Unique key
R - Referential integrity
V - With check option, on a view
O - With read only, on a view
H - Hash expression
F - Constraint that involves a REF column
S - Supplemental logging
santhoshreddy podduturi, November 25, 2016 - 12:28 am UTC
SQL> SELECT table_name,r_constraint_name FROM dba_constraints WHERE constraint_t
ype ='F' AND r_constraint_name IS NULL and rownum<4 AND owner='SYS';
TABLE_NAME R_CONSTRAINT_NAME
--------------------------- ---------------------------
WRI$_REPT_COMPONENTS
WRI$_ADV_DEFINITIONS
AQ$_REPLAY_INFO
SQL>
Above tbales are owned by sys schema and are base tables
For them Why foreign key is not there?
A reader, November 25, 2016 - 9:04 am UTC
What is the difference between F,R?Please Explain with an Example.
November 26, 2016 - 3:06 am UTC
A reader, September 26, 2018 - 1:08 pm UTC
How to know the column name on which constraints are defined?
September 27, 2018 - 2:16 am UTC
SQL> desc user_cons_columns
Name Null? Type
----------------------------------------------------------------------- -------- --------------
OWNER NOT NULL VARCHAR2(128)
CONSTRAINT_NAME NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME VARCHAR2(4000)
POSITION NUMBER