PostgreSQL - CREATE TABLE - Apply constraints like PRIMARY KEY in attributes that are inside composite types

Question

I want to implement an object-relational database, using PostgreSQL. I do not want to use ORACLE. Can I create a composite type, and then use it in a table, adding a restriction for example of a primary key in one of its attributes? Below I leave an example:

CREATE TYPE teamObj AS (
    idnumeric,
    name character varying,
    city character varying,
    estadiumName character varying,
    jugadores playerObj[]
);

CREATE TABLE teamTable (
    equipo equipoobj,
    PRIMARY KEY (equipo.id)
);

The line PRIMARY KEY (equipo.id) gives an error, and I've read a lot of documentation of this topic, and I don't find the solution, maybe PostgreSQL has not implemented yet, or will never implement it, or I don't understand how runs PostgreSQL...

Does somebody have a solution?

Thank you.

I feel you only need a table team.. PostgreSQL will automatically provide you the type team with the same datatypes and attributenames as a row of the table. — Islingre
– Islingre, Commented Oct 6, 2019 at 13:58
I don't think you can; I've tried every variation, including those claimed to work by other SO questions, and didn't find anything to work. I'd concur with Islingre though; using a type in the way you propose to here will only make everything more hard work — Caius Jard
– Caius Jard, Commented Oct 6, 2019 at 17:17

Laurenz Albe · Accepted Answer · 2019-10-06 18:10:51Z

1

No, you cannot do that, and I recommend that you do not create tables like that.

Your table definition should look pretty much like your type definition does. There is no need for an intermediate type definition.

Reasons:

that will not make your schema more readable
that violates the first normal form for relational databases
that does not match an object oriented design any better than the simple table definition

As a comfort, PostgreSQL will implicitly define a composite type of the same name whenever you define a table, so you can do things like

CAST(ROW(12, 'Raiders', 'Wolfschoaßing', 'Dorfwiesn', NULL) AS team)

or

CREATE FUNCTION getteam(id) RETURNS team

answered Oct 6, 2019 at 18:10

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

José Manuel Over a year ago

I have given up trying. I've been using Oracle for years, and I think PostgreSQL doesn't implement it the same way. So it is I who must change. I will do it as you tell me. Thank you.

Laurenz Albe Over a year ago

You wouldn't define a table like that in Oracle either. It is just not a good database design to use composite types unnecessarily.

Collectives™ on Stack Overflow

PostgreSQL - CREATE TABLE - Apply constraints like PRIMARY KEY in attributes that are inside composite types

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related