How to CREATE TABLE with disjoint relationship in SQL

Question

I am trying to create a table using a disjoint subtype relationship.

For example, if the Supertype is furniture, and I have 3 Subtypes of furniture: chair, couch, and table.

Then:

CREATE TABLE Furniture
(order_num NUMBER(15), desc VARCHAR2(20), type VARCHAR2(10));

How do I make an option to pick type of chair, couch or table?

http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database — Sebas, Dec 10 '14 at 19:08
FWIW, [Oracle has objects](https://docs.oracle.com/cd/B28359_01/appdev.111/b28371/adobjint.htm) supporting inheritance. — Sylvain Leroux, Dec 10 '14 at 19:31

score 2 · Answer 1 · answered Dec 10 '14 at 18:49

You can use REFERENCES in the CREATE TABLE.

CREATE TABLE Furniture_SubTypes
(
    sub_type     VARCHAR(10) PRIMARY KEY
);

INSERT INTO Furniture_SubTypes VALUES ('Chair');
INSERT INTO Furniture_SubTypes VALUES ('Couch');
INSERT INTO Furniture_SubTypes VALUES ('Table');

CREATE TABLE Furniture
(
    order_num    NUMBER,
    description  VARCHAR(20),
    sub_type     REFERENCES Furniture_SubTypes(sub_type)
);

score 1 · Answer 2 · answered Dec 10 '14 at 18:37

1

Use a check constraint:

CREATE TABLE Furniture (
    order_num NUMBER(15),
    description VARCHAR2(20),
    type VARCHAR2(10),
    check (type in ('chair', 'couch', 'table'))
);

Note that desc is a poor choice for a column name, because it is a keyword in SQL (used for order by).

answered Dec 10 '14 at 18:37

Gordon Linoff

1,198,228
53
572
709

One last question. Each type of furniture has its own attributes. e.g. Chair has attributes of legs, color. Couch has attributes of fabric. etc. Would i create separate tables for each type of furniture? – webminer07 Dec 10 '14 at 18:46
That is a very different question. In some cases yes, and in some cases no. If you want a more detailed answer, you should ask another question, with more information about the attributes and how you will use the tables. – Gordon Linoff Dec 11 '14 at 00:06

How to CREATE TABLE with disjoint relationship in SQL

2 Answers2