Hello!

i want to display the following isa-relationship:

see ISA.jpg

Now i think that this code is wrong:

CREATE TABLE T(ID_T INTEGER, NAME VARCHAR2(10), CATEGORY VARCHAR(10));
ALTER TABLE T ADD (CONSTRAINT PK_T PRIMARY KEY (ID_T));

CREATE TABLE A(ID_T INTEGER, ID_A INTEGER, NAME VARCHAR2(10));
ALTER TABLE A ADD (CONSTRAINT PK_A PRIMARY KEY (ID_T, ID_A));
ALTER TABLE A ADD (CONSTRAINT FK_A FOREIGN KEY (ID_T) REFERENCES T (ID_T));

CREATE TABLE B(ID_T INTEGER, ID_B INTEGER, NAME VARCHAR2(10));
ALTER TABLE B ADD (CONSTRAINT PK_B PRIMARY KEY (ID_T, ID_B));
ALTER TABLE B ADD (CONSTRAINT FK_B FOREIGN KEY (ID_T) REFERENCES T (ID_T));


CREATE TABLE C(ID_T INTEGER, ID_C INTEGER, NAME VARCHAR2(10));
ALTER TABLE C ADD (CONSTRAINT PK_C PRIMARY KEY (ID_T, ID_C));
ALTER TABLE C ADD (CONSTRAINT FK_C FOREIGN KEY (ID_T) REFERENCES T (ID_T));



It would be great if anybody could help me!

It should be:

CREATE TABLE T(ID_T INTEGER, NAME VARCHAR2(10), CATEGORY VARCHAR(10));
ALTER TABLE T ADD (CONSTRAINT PK_T PRIMARY KEY (ID_T));

CREATE TABLE A(ID_T INTEGER, {a_specific_columns});
ALTER TABLE A ADD (CONSTRAINT PK_A PRIMARY KEY (ID_T));
ALTER TABLE A ADD (CONSTRAINT FK_A FOREIGN KEY (ID_T) REFERENCES T (ID_T));

CREATE TABLE B(ID_T INTEGER, {b_specific_columns});
ALTER TABLE B ADD (CONSTRAINT PK_B PRIMARY KEY (ID_T));
ALTER TABLE B ADD (CONSTRAINT FK_B FOREIGN KEY (ID_T) REFERENCES T (ID_T));

CREATE TABLE C(ID_T INTEGER, {c_specific_columns});
ALTER TABLE C ADD (CONSTRAINT PK_C PRIMARY KEY (ID_T));
ALTER TABLE C ADD (CONSTRAINT FK_C FOREIGN KEY (ID_T) REFERENCES T (ID_T));

i.e. the ID for the supertype is also the ID for the subtype.

Thank You!
But what can i do if i need the following construction later:

That would require an intersection table:

create table ab (a_id_t references a, b_id_t references b,
constraint pk_ab primary key ( a_id_t, b_id_t));

Thank You!

Be sure to enforce a constraint that prohibits the supertype to be more than one subtype (If that is desired)...

ie: With the current schema this is possible.....

insert into T values (1)
insert into A Values (1)
insert into B Values (1)

Hello!
Now i have an advanced question.

###################################

table T
==============================
ID integer, (e.g. 1, 2, 3)
CATEGORY (e.g., A, B, C)
==============================

table A
===========
ID integer,
...

relat_table AB
==============
ID_A integer
ID_B integer


table B
============
ID integer,
...

table C
===========
ID integer,
...

###################################


Now i want to create the following trigger:

if (insert into T ( CATEGORY) values ('A') )
then (insert the same ID into table A)

if ................ 'B'..............
then ................ 'B'.........

and so on.

These operations must be atomic.

How can i realize that?

################################

CREATE OR REPLACE TRIGGER TRG_T_BEF_INS
BEFORE INSERT ON T
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar1 NUMBER;

BEGIN
tmpVar1 := 0;

SELECT SEQ_T.NEXTVAL INTO tmpVar1 FROM dual;
:NEW.ID := tmpVar1;
END;


It would be great if anybody could help me!

You would want an AFTER INSERT trigger something like this:
However, this only works if all the non-key columns in A, B, ... are defined to allow NULLs. I would more likely go for an approach like this:

Now the user inserts into view_a, and a record gets created in T and A with all columns populated.