CODEWITHSUNDEEP
sqlpostgresql
Hyfy
Hyfy

Reputation: 344

How to create a table with ONE existing row from another table?

I'm frankly new to sql and this is a project I'm doing.

I would like to know if there's a way to connect one column in one table to another table when creating tables. I know of the join method to show results of, but I want to minimized my code as possible.

CREATE TABLE players (
    id INT PRIMARY KEY, -->code I want connect with table match_record
    player_name CHARACTER
);

CREATE TABLE match_records (
    (id INT PRIMARY KEY /*FROM players*/),  --> the code I want it to be here
    winner INT,
    loser INT
);

Upvotes: 0

Views: 47

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51416

CREATE TABLE players (
    id INT not null PRIMARY KEY, -->code I want connect with table match_record
    player_name CHARACTER
);

CREATE TABLE match_records (
    id INT not null PRIMARY KEY references players(id),  --> the code I want it to be here
    winner INT,
    loser INT
);

this way you restrict that match_records.id is only from players.id:

t=# insert into match_records select 1,1,0;

ERROR:  insert or update on table "match_records" violates foreign key constraint "match_records_id_fkey"
DETAIL:  Key (id)=(1) is not present in table "players".

So I add players:

t=# insert into players(id) values(1),(2);
INSERT 0 2

And now it allows insert:

t=# insert into match_records select 1,1,0;
INSERT 0 1

update https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-PROMPTING

%#

If the session user is a database superuser, then a #, otherwise a >. (The expansion of this value might change during a database session as the result of the command SET SESSION AUTHORIZATION.)

Upvotes: 1

Piotr Rogowski
Piotr Rogowski

Reputation: 3880

in this way:

CREATE TABLE new_table as SELECT id,... from old_table where id = 1;

Upvotes: 0

Related Questions