unique constraint on a (virtual) column while keep NULL able

Toad Data Modeler
1

Case:
Want to use a unique constraint on a virtual column and use a foreign constraint on this column.
The column used should keep a NULL able type.

Problem
Choose a Unique constraint in the attribute, causes also the enabling of the Not Null constraint.

Question
Is it possible to define a unique constraint and uses this in the foreign key constraint, but without to enable the Not Null constraint in TDM?

Technical it is possible to do for example:
ALTER TABLE table_with_unique_constraint
ADD CONSTRAINT SREF_DOELEN_UK
UNIQUE (DOELEN_FK);

ALTER TABLE table_with_refto
ADD CONSTRAINT “table_refto_twuc” FOREIGN KEY (DOELEN) REFERENCES table_with_unique_constraint (DOELEN_FK) ON DELETE SET NULL
/ Message was edited by: g.kuivenhoven@rijnland.net

Message was edited by: vladka

2

Hello,

Thanks for your question.
I will get back to you tomorrow - as soon as I know more.

Thanks for your patience.

Regards,

Vladka

3

Hi,

I’m back.

After the first reading, we thought you mean the issue of a column being automatic NotNull when applying unique/alternate key or primary key. Users want to have an option to turn this off. We will work on it for the next v. 3.6 (CR 48 139)

However, having a closer look at your example, we don’t see a reason why the NotNull is worrying you. NotNull will be set only at attribute in parent table, so there is no problem with DELETE SET NULL - if this is the point you mean.

If we’ve misunderstood, please write us back. Thanks.

Regards,

Vladka + Mario

4

the problem exist only if an attribute have not set “Not Null” in the [Attributes]-tab, followed by adding an unique index on that attribute.

WorkAround:

  1. Set the “Not Null” true in the [Attributes]
  2. Add an Unique Index to this attribute [Indexes]
  3. UnSet the “Not Null” in the [Attributes]