st_id_unique is a unique constraint that needs to be dropped from the staff_bio table. staff_bio is a table to be altered/modified. The stated error proves that duplicate entries can’t be inserted into a column that is created with a UNIQUE CONSTRAINT.Ī UNIQUE constraint can be dropped from a column using the DROP CONSTRAINT clause with ALTER TABLE statement. Let’s verify the working of the UNIQUE constraint by adding a couple of duplicate records in the st_id column: INSERT INTO staff_bio(st_id, st_name, st_department, st_age) The UNIQUE constraint will be applied to the column named "st_id". The “st_id_unique” represents unique constraint. The “staff_bio” is a table to be altered. To add a UNIQUE constraint on the “st_id” column, use the ALTER TABLE command alongside ADD CONSTRAINT clause: ALTER TABLE staff_bioĪDD CONSTRAINT st_id_unique UNIQUE (st_id) When a new record is inserted in a table, the UNIQUE constraint checks whether or not the input record already exists in the targeted table. How to Drop a UNIQUE CONSTRAINT in Postgres?Ī UNIQUE constraint in Postgres ensures that all the rows in a column are unique. The “ALTER TABLE” message in the output window proves that the foreign key named “fk_ord_cust” has been dropped successfully. To drop a foreign key constraint from a table, use the ALTER TABLE with the DROP CONSTRAINT clause: ALTER TABLE orders_details The output snippet shows that the foreign key constraint has been added to the orders_details table successfully. To do that, let’s run the below statement: ALTER TABLE orders_detailsįOREIGN KEY (customer_id) REFERENCES customers_info (c_id) Suppose we want to refer the “ customer_id” column from the “ orders_details” table to the “ c_id” column of the “ order_details” table. To explain the concept of foreign key constraint, we will use the “staff_info” and “employee_info” tables, whose details are shown below: SELECT * FROM customers_info SELECT * FROM orders_details In this section, we will learn how to add or drop a FOREIGN KEY CONSTRAINT in Postgres Using ALTER TABLE command. How to Drop a FOREIGN KEY CONSTRAINT in Postgres?Ī FOREIGN KEY is a column that points to the PRIMARY KEY of some other Postgres table. The output clarifies that the primary key constraint has been removed successfully. Let’s verify the constraint deletion via the below command: SELECT * FROM staff_bio In this coding example, we dropped a primary key constraint named st_id_pk from the staff_bio table: The DROP CONSTRAINT clause can be used in conjunction with ALTER TABLE to drop a primary key constraint from a Postgres table. This way, you can add a primary key to any table’s column. Let’s validate the table’s structure via the following command: SELECT * FROM staff_bio The “ALTER TABLE” message in the output window proves that the “staff_bio” table has been modified successfully. The “st_id” represents a primary key column. The “st_id_pk” represents the name of the primary key. This section will teach you how to add or drop a PRIMARY KEY CONSTRAINT in Postgres Using ALTER TABLE command.įirstly, let’s add a primary key constraint on the “st_id” column using ALTER TABLE command and ADD CONSTRAINT clause: ALTER TABLE staff_bio In Postgres, Primary keys are used to identify a table’s record uniquely. How to Drop a PRIMARY KEY CONSTRAINT in Postgres? The table name staff_bio has been created with four columns: st_id, st_name, st_department, and st_age. Afterward, we will add and drop a specific constraint according to the requirement.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |