If the table you are adding foreign key constraint contains data, then that. Specify one or more column names of the table on which you want to define foreign key constraint. The constraint name is optional if you do not specify it, PostgreSQL will give the name as per the default naming convention. I'm using Postgres 11, but this fiddle running 9.6 shows what I've tried to explain here. A foreign key constraint can be added to one or more columns of the existing table. ALTER FOREIGN TABLE changes the definition of an existing foreign table. In the above syntax, Use the CONSTRAINT keyword to define a constraint and then the name of the foreign key constraint. I know I can work around this by deleting/truncating all data in sales or prefilling dates before adding the constraint, but I would prefer not to if I can avoid it. PostgreSQL does not support foreign keys operating within a single database server, defining a link between two database tables in two different database. Seems easier than trying to query informationschema or catalogs, but might be slow on huge tables since it always recreates the constraint. Indexes and table constraints involving the column will be automatically dropped as well. There are several subforms: This form adds a new column to the table, using the same syntax as CREATE TABLE. ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar ALTER TABLE foo ADD CONSTRAINT bar. ALTER TABLE changes the definition of an existing table. Then an ETL process fills this table with data INSERT INTO sales VALUESīut later, I need to add a table with actual processed dates, such as CREATE TABLE dates (Īnd want to add a foreign key constraint on the old table: ALTER TABLE salesĪDD CONSTRAINT sales_date_fk FOREIGN KEY (sale_date)ĮRROR: insert or update on table "sales" violates foreign key constraint "sales_date_fk" Detail: Key (sale_date)=() is not present in table "dates". A possible solution is to simply use DROP IF EXISTS before creating the new constraint. Let's say I have one table defined as this CREATE TABLE sales (Īgent_id integer references agents(agent_id),
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |