Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Lakshman.

Asked: September 26, 2018 - 3:04 am UTC

Last updated: October 04, 2018 - 12:45 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi,
I have parent table X (Code table), and a child table CUSTOMER.

I have a FK constraint enabled on CUSTOMER. But still database is accepting/inserting CDE in table CUSTOMER table which are not present in Table X.

When FK Constraint is enabled, it must reject record while inserting because there is no parent key found.

Here is the script from Power Designer:

alter table CUSTOMER
add constraint FK_CUST_04 foreign key (CDE)
references X(CDE);

I verified from SQL developer. FK exist and its enabled. am not sure what is the issue?

Note: we do not have IDENTITY column for CODE/Reference tables. _CDE will be PK.
-----------------
create table CUSTOMER_TEST(
CUSTOMER_ID INT
generated ALWAYS as identity ( start with 1 nocycle noorder),
FNAME VARCHAR2(50 CHAR),
MNAME VARCHAR2(50 CHAR),
LNAME VARCHAR2(50 CHAR),
FULL_NAME VARCHAR2(50 CHAR)
)
tablespace CORE;

alter table CUSTOMER_TEST
add constraint PK_CUSTOMER_TEST primary key (CUSTOMER_ID);
------------------
create table GENDER_TEST(
GENDER_CD CHAR(1),
GENDER_NME VARCHAR2(50 CHAR),
CREATE_TS TIMESTAMP(6)
)
tablespace CORE;

alter table GENDER_TEST
add constraint PK_GENDER_TEST primary key (GENDER_CD,GENDER_NME);

--------
SELECT * FROM CORE.GENDER_TEST;

ALTER TABLE CUSTOMER_TEST ADD (
GENDER_CD CHAR(1),
GENDER_NME VARCHAR2(50));
----------
SELECT * FROM CORE.CUSTOMER_TEST;

ALTER TABLE CORE.CUSTOMER_TEST
ADD CONSTRAINT FK_CUSTOMER_TEST_01 FOREIGN KEY (GENDER_CD,GENDER_NME)
REFERENCES CORE.GENDER_TEST(GENDER_CD,GENDER_NME);
--------
INSERT INTO CORE.CUSTOMER_TEST (FNAME,MNAME,LNAME,FULL_NAME,GENDER_CD,GENDER_NME)
SELECT 'LUCKY','KUMAR','ALA','LA','M','MALE' FROM DUAL; -- Pass

INSERT INTO CORE.CUSTOMER_TEST (FNAME,MNAME,LNAME,FULL_NAME,GENDER_CD,GENDER_NME)
SELECT 'LUCKY','KUMAR','ALA','LA','','' FROM DUAL; --PASS (shoudl fail)
INSERT INTO CORE.CUSTOMER_TEST (FNAME,MNAME,LNAME,FULL_NAME,GENDER_CD,GENDER_NME)
SELECT 'LUCKY','KUMAR','ALA','LA','M','' FROM DUAL; -- PASS (shoudl fail)

INSERT INTO CORE.CUSTOMER_TEST (FNAME,MNAME,LNAME,FULL_NAME,GENDER_CD,GENDER_NME)
SELECT 'LUCKY','KUMAR','ALA','LA','','MALE' FROM DUAL; -- PASS (shoudl fail)
---
INSERT INTO CORE.CUSTOMER_TEST (FNAME,MNAME,LNAME,FULL_NAME,GENDER_CD,GENDER_NME)
SELECT 'LUCKY','KUMAR','ALA','LA','T','TEST' FROM DUAL; -- FAIL

INSERT INTO CORE.CUSTOMER_TEST (FNAME,MNAME,LNAME,FULL_NAME,GENDER_CD,GENDER_NME)
SELECT 'LUCKY','KUMAR','ALA','LA','T','' FROM DUAL; -- PASS (There is no Gender_cd='T')

SELECT * FROM CORE.GENDER_TEST;
-- I want strictly enforce FK on parent table Primary key composite key both gender_cd, and gender_nme

and Connor said...

From the docs:

https://docs.oracle.com/database/121/CNCPT/datainte.htm#CNCPT33335

"If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key."

Your options are:

1) it looks to me like only GENDER_CD should be the primary key, not (GENDER_CD,GENDER_NME)

or

2) declare GENDER_CD, GENDER_NME both not null in CUSTOMER

or

3) add a constraint specifying that either both GENDER_CD, GENDER_NME are present or neither are present, eg

alter table CUSTOMER add
constraint CHK check
(
( GENDER_CD is null and GENDER_NME is null )
or
( GENDER_CD is not null and GENDER_NME is not null )
)


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

Lakshman Kumar, October 03, 2018 - 4:36 am UTC

Thanks for the response. this helps.
Yes, Gender_cde is enough to define PK in this case.
But I just made up this example to explain the issue

Thank you, again.
Connor McDonald
October 04, 2018 - 12:45 am UTC

glad we could help

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.