CODEWITHSUNDEEP
postgresql
Ivonne Terrero
Ivonne Terrero

Reputation: 83

Creating a table using postgreSQL

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.

Upvotes: 1

Views: 49

Answers (1)

Patrick
Patrick

Reputation: 32276

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.

Upvotes: 0

Related Questions