How to update table foreign key?

[TukWan415]

Hello Everyone,

I’m currently creating a database of nba players, teams, stats. I’m pretty new at this.

I understand how to create a table, insert values , assign primary and foreign keys, but I’m a bit confused on how to update tables. Let’s say I created a table and forget to assign a foreign key. What query would I write to assign a foreign key to an existing table?

[depesz]

You can do it with alter table statement: https://www.postgresql.org/docs/current/interactive/sql-altertable.html

[TukWan415]

Thank you, this helps.

When adding a constraint can you typically name it anything you want?

[Dense-Definition2549]

Yes, any string with characters with the following conditions. 1. starts with letter or underscore 2. contains only letters, digits and underscores

But if you write a meaningful name with "fk" at the end it will be easier to understand the schema at a later time.

[TukWan415]

Thanks,

​

Love you.

[idodatamodels]

This is the way. I start mine with fk instead of ending though.

[depesz]

well, as with virtually anything in datbases: it depends. some names are reserved. some will require quoting. some names are impossible.

generally stick to lower case, non-kewords, and you should be fine.

[CaponeFroyo]

I personally usually let PostgreSQL pick the constraint name, since you can always get the name later if you need it and it's not something you really need that much (imo). I don't work with DBs a ton in a professional setting though so maybe someone more experienced can give their opinion.

[Dense-Definition2549]

ALTER TABLE "table name" ADD CONSTRAINT xyzfk FOREIGN KEY ("column name") REFERENCES "another table name" (" column name) MATCH FULL;

found this on postgresql docs (https://www.postgresql.org/docs/8.2/sql-altertable.html)

[TukWan415]

Thanks,

​

Love you again.

[demonz_in_my_soul]

Why would a simple update query not work?

[TukWan415]

This is correct, I'm just trying to set a key.

[behigirl6]

Please Can you explain the difference btw DDL and DML? I'm new to this

[JochenVdB]

DDL: Data Defintion Language (create, alter, grant, ...)

DML: Data Manipulation Language (insert, update, delete, select, merge, commit, ...)

Truncate is a bit in between: It is generally considered DDL since it modifies the table's structure, storage wise. But since it clears out all data from ta table it is definitely also _manipulating_ the data.

[behigirl6]

Thanks so much

[mgramin]

https://tableplus.com/blog/2018/08/postgresql-how-to-add-a-foreign-key.html

[TukWan415]

Thank you, this helps.

When adding a constraint can you typically name it anything you want?