#Postgres add column with foreign key update#
Please note that you can only use "ON UPDATE RESTRICT" or "ON DELETE RESTRICT" and not necessarily both together. Let's modify the structure of column "customer_id" on table "orders" to prevent updates and deletions: # ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey ĪLTER # ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE RESTRICT ON DELETE RESTRICT If we follow our example above, we could delete the customer "Tom" because he has no orders but we may want to prevent the deletion of "Gabriel" because he has made an order. You can use a foreign key to prevent deletion and updates of a row referenced in another table. Restricting deletions and updates (RESTRICT constraint) However if I try to create an order for a nonexisting customer, PostgreSQL will notify me of my error: # INSERT INTO orders (customer_id, order_description, order_amount) VALUES (12, '2 pound of Coffee', 28.10) ĮRROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"ĭETAIL: Key (customer_id)=(12) is not present in table "customers". The main purpose of Foreign Keys is to ensure that when we create a new order we use a "customer_id" existing in the table "customers".įor example, if I try to create an order for "Gabriel" (id = 2), everything will work fine: # INSERT INTO orders (customer_id, order_description, order_amount) VALUES (2, '1 pound of Coffee', 14.55) There are currently 5 customers in the table "customers": # SELECT * FROM customers At the bottom of the structure you can also see we are using this column as a foreign key for the column "id" in the table "customers". The table "orders" includes a column called "customer_id" which is going to be our reference to "customers". You can see at the bottom of its structure it says that its "id" is used as a foreign key constraint in the table "orders". You have to specify that this "id" is unique in order to use it with a Foreign Key. The first table, "customers" has an "id" per entry. "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id) "orders_id_key" UNIQUE CONSTRAINT, btree (id)
Id | integer | not null default nextval('orders_id_seq'::regclass) TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES # \d orders "customers_id_key" UNIQUE CONSTRAINT, btree (id)
Id | integer | not null default nextval('customers_id_seq'::regclass) Let's have a quick look at the tables we created: # \d customers If you would like to try the following examples, please create these 2 tables: - Create table -customers-Ĭustomer_id int REFERENCES customers(id),
0 kommentar(er)
