INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

DB2 Foreign Key Constraints

DB2 Foreign Key Constraints

(OP)
I'll try to explain this as simply as I can !

 

I have 2 tables (BRANCH & COUNTRY) as below

 

CREATE TABLE BRANCH (
  BRANCH_ID    VARCHAR(32)   NOT NULL,
  SORT_CODE  VARCHAR(20)   NOT NULL,
  BRANCH_NAME         VARCHAR(100) NOT NULL,
  ADDRESS_LINE1        VARCHAR(400),
  ADDRESS_LINE2        VARCHAR(400),
  ADDRESS_LINE3        VARCHAR(400),
  CITY   VARCHAR(100),
  ZONE VARCHAR(100),
  POSTAL_CODE          VARCHAR(20),
  COUNTRY_CODE        VARCHAR(3)
 );

ALTER TABLE BRANCH
  ADD CONSTRAINT BRANCH_PK PRIMARY KEY
    (BRANCH_ID);

ALTER TABLE COOP1.BRANCH
  ADD CONSTRAINT BRANCH_FK1 FOREIGN KEY
    (COUNTRY_CODE)
  REFERENCES COOP1.COUNTRY
    (COUNTRY_CODE)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    ENFORCED
    DISABLE QUERY OPTIMIZATION;

***

CREATE TABLE COUNTRY (
  COUNTRY_CODE        VARCHAR(3)     NOT NULL,
  COUNTRY_NAME       VARCHAR(50)   NOT NULL,
  ISO_CODE      VARCHAR(3),
  VERSION_ID   INTEGER
);

ALTER TABLE COUNTRY
  ADD CONSTRAINT COUNTRY_PK PRIMARY KEY
    (COUNTRY_CODE);

I have a foreign key constraint going from Branch to Country – so far so good !

When I insert a new record onto Branch, I ensure that the value in the column COUNTRY_CODE exists on the COUNTRY Table – no problem again.

As the BRANCH.COUNTRY_CODE column allows nulls, I tried to enter another BRANCH record with null in that column but it failed saying I had failed on a foreign key rule.

Now, in dear old Oracle, that is allowed !

So I altered the Foreign Key constraint statement to say 'NOT ENFORCED' but that just allows you to put any old rubbish in the COUNTRY_CODE column.  I'm not sure if there can be other clauses in that statement that could help me out.

So my question is, does DB2 allow you to put nulls in a column that you have said is a foreign key to another table ?

Thanks for any help

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close