Hi,

I have a simple question.

Two tables:

CREATE TABLE table1 (
Attr1 VARCHAR(2) NOT NULL WITH DEFAULT 'XY',
Attr2 INTEGER NOT NULL
);
CREATE UNIQUE INDEX table1_UI ON table1 ( Attr1 ) PCTFREE 0;
ALTER TABLE table1 ADD CONSTRAINT table1_PK PRIMARY KEY ( Attr1 );
ALTER TABLE table1 ADD CONSTRAINT table1_CK CHECK (Attr1 in ( 'XY', 'AB', 'CD' ));

CREATE TABLE table2 (
Attr1 VARCHAR(2) NOT NULL.
Attr3 INTEGER NOT NULL,
Attr4 INTEGER NOT NULL,
);
CREATE UNIQUE INDEX table2_UI ON table2 ( Attr1, Attr3 ) PCTFREE 0;
ALTER TABLE table2 ADD CONSTRAINT table2_PK PRIMARY KEY ( Attr1, Attr3 );
ALTER TABLE table2 ADD FOREIGN KEY table2_FK (Attr1) REFERENCES table1 (Attr1) ON DELETE NO ACTION;

Is it necessary to add the same CHECK CONSTRAINT and WITH DEFAULT 'XY' to the second table or are this properties automatically induced by the the first table?

Appreciate everyones help.

Thanks in Advance,

S.B.

If you want the default of Attr1 on Table2 to be 'XY' then you will have to specify that default on the column definition of Attr1 for Table2. But a CHECK CONSTRAINT on table2 like the one on table1 would be redundant since that is handled by the RI.

Note that even if the default is assigned to Attr1 on table1 and table2, a parent row for 'XY’ must exist on table1 for the RI constraint on table2 to be valid if it defaulted to 'XY'.

Thanks a lot!

Assumed, there is a third table like this:

CREATE TABLE table3 (
Attr1 VARCHAR(2) NOT NULL WITH DEFAULT 'XY'.
Attr3 INTEGER NOT NULL,
Attr5 INTEGER NOT NULL,
Attr6 INTEGER NOT NULL,
);
CREATE UNIQUE INDEX table3_UI ON table3 ( Attr1, Attr3, Attr5 ) PCTFREE 0;
ALTER TABLE table3 ADD CONSTRAINT table3_PK PRIMARY KEY ( Attr1, Attr3, Attr5 );
ALTER TABLE table3 ADD FOREIGN KEY table3_FK (Attr1, Attr3) REFERENCES table2 (Attr1, Attr3) ON DELETE NO ACTION;

In this case table1 and table3 are not directy connected... Is this construction valid without any additional check constraints?