This forum is currently locked. You can't register or post questions at this time. (read more)

Foreign Key-Not null

All posts relating to Oracle database administration.

Moderator: Tim...

Monto
Senior Member
Posts: 195
Joined: Sat Jan 16, 2010 1:55 pm

Foreign Key-Not null

Postby Monto » Mon Mar 31, 2014 4:52 pm

Hi Tim,

11203,RHEL 6

Does Oracle by default puts a not null constraint on a foreign key ?Can you please explain the impact of foreign key being null and not null?

Thanks

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Foreign Key-Not null

Postby Tim... » Mon Mar 31, 2014 5:19 pm

Hi.

No. Oracle doesn't make FK columns mandatory.

If the relationship is compulsory, you should make the FK column mandatory. If it is optional, you should not!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Monto
Senior Member
Posts: 195
Joined: Sat Jan 16, 2010 1:55 pm

Re: Foreign Key-Not null

Postby Monto » Mon Mar 31, 2014 7:02 pm

Thanks.When i added a foreign key constraint it created a not null constraint also,is this expected behavior from oracle?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Foreign Key-Not null

Postby Tim... » Mon Mar 31, 2014 7:28 pm

Hi.

No. I don't know what you did, but you did not just create a Fk or it would have remained mandatory. See:

Code: Select all

CREATE TABLE t1 (
  id NUMBER,
  description VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE TABLE t2 (
  id NUMBER,
  t1_id NUMBER,
  description VARCHAR2(50),
  CONSTRAINT t2_pk PRIMARY KEY (id),
  CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1(id)
);

SQL> desc t2
 Name                  Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                  NOT NULL NUMBER
 T1_ID                      NUMBER
 DESCRIPTION                   VARCHAR2(50)

SQL>


Notice that T1_ID is optional.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Monto
Senior Member
Posts: 195
Joined: Sat Jan 16, 2010 1:55 pm

Re: Foreign Key-Not null

Postby Monto » Mon Mar 31, 2014 7:49 pm

Thanks,what exactly do you mean by mandatory or optional here?

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Foreign Key-Not null

Postby Tim... » Mon Mar 31, 2014 8:58 pm

Hi.

Mandatory : The column must have a value. Can not be NULL. NOT NULL!
Optional: The column can contain a NULL value.

As you can see from my example, I create a table (t2) with a FK column (t1_id) pointing to the table t1. The FK column has not been made mandatory (NOT NULL) by adding an FK.

Therefore, what you suggested is untrue. Adding an FK contraint does not make a column NOT NULL. Proved by my example!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Monto
Senior Member
Posts: 195
Joined: Sat Jan 16, 2010 1:55 pm

Re: Foreign Key-Not null

Postby Monto » Wed Apr 02, 2014 4:54 pm

Thanks.All i did was the below sequence of DDL which created a not null constraint on PGM_CDE column.

create table CLNT_CMTE_RVW_TST
(
CLNT_CMTE_RVW_STS_ID NUMBER not null,
PGM_CDE VARCHAR2(5),
TST_CDE VARCHAR2(5),
CLNT_CMTE_RVW_STS_CDE VARCHAR2(5) not null,
CLNT_CMTE_RVW_STS_DSC VARCHAR2(100) not null,
CRET_DTM DATE not null,
CRET_LGN_UID VARCHAR2(32) not null,
LST_UPDT_DTM DATE,
LST_UPDT_LGN_UID VARCHAR2(32),
constraint CLNT_CMTE_RVW_TST_PK primary key (CLNT_CMTE_RVW_STS_ID),
constraint CLNT_CMTE_RVW_TST_AK1 unique (PGM_CDE, TST_CDE, CLNT_CMTE_RVW_STS_CDE)
);

alter table CLNT_CMTE_RVW_TST
add constraint PGM_FK17 foreign key (PGM_CDE)
references PGM (PGM_CDE) novalidate;

alter table CLNT_CMTE_RVW_TST
add constraint TST_FK57 foreign key (PGM_CDE, TST_CDE)
references TST (PGM_CDE, TST_CDE);

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Foreign Key-Not null

Postby Tim... » Wed Apr 02, 2014 7:23 pm

Hi.

You've actually done several things here and the one making the column mandatory is not what you think it is. :)

Try missing out this line and see what happens...

"constraint CLNT_CMTE_RVW_TST_AK1 unique (PGM_CDE, TST_CDE, CLNT_CMTE_RVW_STS_CDE)"


You will fine the column stays optional... The FK does not make the column mandatory...

When you include that line, your column is part of a unique key, so it becomes mandatory. Different thing entirely...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 9 guests

cron