I need to create a new table with all columns from other 4 tables.
My idea for creating the table would be to use the structure:
CREATE TABLE IF NOT EXISTS table_name AS SELECT ~select from other tables~
So far, so good. But I can't find a proper way to do the joins without the foreign key / primary key columns in the table repeating.
Is there a way to use something as
distinct, but for columns, no for values?
I know I could do a simpler way, which would be to select exactly the fields I need (like
select column.field1, column2.field2), but the 4 tables I need to use to create the new one, have several fields, would be a long code, I don't know if it would be the best way, I wanted something more optimized.
I was also wondering if using this select table structure as per the example would there be any way to set a primary key on creation?
I just tried typing the
primary key (column) command after the end of select in various ways (as it would be if I was creating the table by normally setting the fields) and was unsuccessful. Creating the table with
AS SELECT, can I only define primary and foreign keys with an
alter table after creation?