Creating a table using postgreSQL

Question

I was asked to create a table using PostgreSQL and use the following information:

The properties table should consist of: property_id (this should be the primary key as well as a unique number that increments automatically) name number of units owner_id (this should have the constraint NOT NULL) There should be also be a foreign key that references the owners table

I wrote the following

apartmentlab=# CREATE TABLE properties (
apartmentlab(# PRIMARY KEY property_id SERIAL,
apartmentlab(# name TEXT,
apartmentlab(# num_of_units numeric,
apartmentlab(# FOREIGN KEY (owner_id) REFERENCES owners (owner_id) NOT NULL
apartmentlab(# );

and I'm getting the following message:

ERROR: syntax error at or near "property_id" LINE 2: PRIMARY KEY property_id SERIAL,

Can someone please tell me what is wrong with the property_id and my syntax. I've looked at the documentation and this looks to be correct.

The documentation does not show primary key at the beginning of a a column definition — user330315
– user330315, Commented Mar 2, 2017 at 7:19

Patrick · Accepted Answer · 2017-03-02 06:39:04Z

0

The PRIMARY KEY clause comes after the data type:

CREATE TABLE properties (
    property_id SERIAL PRIMARY KEY,
    name TEXT,
    num_of_units numeric,
    FOREIGN KEY (owner_id) REFERENCES owners (owner_id) NOT NULL
);

Also note your mistake in the foreign key definition. You probably need to define the field owner_id, with the same data type as that in the table being referenced, in your table as well.

answered Mar 2, 2017 at 6:39

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

Creating a table using postgreSQL

I wrote the following

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

I wrote the following

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related