How to Drop a PRIMARY KEY CONSTRAINT in Postgres?.How to Drop a FOREIGN KEY CONSTRAINT in Postgres?.How to Drop a UNIQUE CONSTRAINT in Postgres?.How to Drop a CHECK CONSTRAINT in Postgres?.How to Remove/Drop a NOT NULL CONSTRAINT From a Postgres Table?įirstly, we will create a sample table with some columns without any constraints.Afterward, we will add and drop a specific constraint according to the requirement. 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. 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. The “st_id_pk” represents the name of the primary key. The “st_id” represents a primary key 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. This way, you can add a primary key to any table’s column. 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. The output clarifies that the primary key constraint has been removed successfully. 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. 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. 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 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. The “ALTER TABLE” message in the output window proves that the foreign key named “fk_ord_cust” has been dropped successfully. How to Drop a UNIQUE CONSTRAINT in Postgres?Ī UNIQUE constraint in Postgres ensures that all the rows in a column are unique. 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. The “staff_bio” is a table to be altered. The “st_id_unique” represents unique constraint. 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 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. staff_bio is a table to be altered/modified. St_id_unique is a unique constraint that needs to be dropped from the staff_bio table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |