Postgres: Add Constraint If It Doesn't Already Exist

Postgres: Add constraint if it doesn't already exist

This might help, although it may be a bit of a dirty hack:

create or replace function create_constraint_if_not_exists (
    t_name text, c_name text, constraint_sql text
) 
returns void AS
$$
begin
    -- Look for our constraint
    if not exists (select constraint_name 
                   from information_schema.constraint_column_usage 
                   where table_name = t_name  and constraint_name = c_name) then
        execute constraint_sql;
    end if;
end;
$$ language 'plpgsql'

Then call with:

SELECT create_constraint_if_not_exists(
        'foo',
        'bar',
        'ALTER TABLE foo ADD CONSTRAINT bar CHECK (foobies < 100);')

Updated:

As per Webmut's answer below suggesting:

ALTER TABLE foo DROP CONSTRAINT IF EXISTS bar;
ALTER TABLE foo ADD CONSTRAINT bar ...;

That's probably fine in your development database, or where you know you can shut out the apps that depend on this database for a maintenance window.

But if this is a lively mission critical 24x7 production environment you don't really want to be dropping constraints willy nilly like this. Even for a few milliseconds there's a short window where you're no longer enforcing your constraint which may allow errant values to slip through. That may have unintended consequences leading to considerable business costs at some point down the road.

Postgres ALTER TABLE ADD CONSTRAINT IF NOT EXISTS not working

Since Postgres doesn't support this syntax with constraints (see a_horse_with_no_name's comment), I rewrote it as:

alter table requests_t
drop constraint if exists valid_bias_check;

alter table requests_t
add constraint 
valid_bias_check CHECK (bias_flag::text = ANY (ARRAY['Y'::character varying::text, 'N'::character varying::text]));

ALTER TABLE CREATE CONSTRAINT IF NOT EXIST possible?

Following my comment, this should be possible:

ALTER TABLE PUBLIC.CORE_USERROLE_TO_PARAMETER
  ADD CONSTRAINT IF NOT EXISTS PUBLIC.CURTBP_USER_ID
    FOREIGN KEY(USER_ID) REFERENCES PUBLIC.CORE_USER(ID) NOCHECK;

ALTER TABLE PUBLIC.CORE_USERROLE_TO_PARAMETER
  ADD CONSTRAINT IF NOT EXISTS PUBLIC.CURTBP_ROLE_ID
    FOREIGN KEY(ROLE_ID) REFERENCES PUBLIC.CORE_USER_ROLE(ID) NOCHECK;

How to create foreign key only if it doesn't exists already?

Use a DO block to execute it in PL/PgSQL.

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'client_contact_contact_id_fkey') THEN
        ALTER TABLE common.client_contact
            ADD CONSTRAINT client_contact_contact_id_fkey
            FOREIGN KEY (contact_id) REFERENCES common.contact_item(id);
    END IF;
END;
$$;

You seem to be relying on the default constraint name generation, which isn't ideal. It's probably safer to use information_schema to check for the presence of a constraint that links the two columns.

The following query checks for a foreign key between the two tables without relying on the generated constraint name:

SELECT 1
FROM information_schema.table_constraints tc 
INNER JOIN information_schema.constraint_column_usage ccu 
  USING (constraint_catalog, constraint_schema, constraint_name) 
INNER JOIN information_schema.key_column_usage kcu 
  USING (constraint_catalog, constraint_schema, constraint_name) 
WHERE constraint_type = 'FOREIGN KEY' 
  AND ccu.table_name = 'contact_item' 
  AND ccu.table_schema = 'common'  
  AND ccu.column_name = 'contact_id' 
  AND tc.table_schema = 'common' 
  AND tc.table_name = 'client_contact'
  AND kcu.column_name = 'id';

How to insert a username if it doesn't already exist with a postgresql POOL query

The best way to handle this is to let the database validate the uniqueness of the username:

alter table userlist add constraint unq_userlist_usename unique(username);

Now if you attempt to insert a single row, then it will return an error if the row already exists.

If you don't want an error you can use an on conflict clause. However, in this case, an exception seems useful to notify the user that username already exists.

How to add column if not exists on PostgreSQL?

Here's a short-and-sweet version using the "DO" statement:

DO $$ 
    BEGIN
        BEGIN
            ALTER TABLE <table_name> ADD COLUMN <column_name> <column_type>;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column <column_name> already exists in <table_name>.';
        END;
    END;
$$

You can't pass these as parameters, you'll need to do variable substitution in the string on the client side, but this is a self contained query that only emits a message if the column already exists, adds if it doesn't and will continue to fail on other errors (like an invalid data type).

I don't recommend doing ANY of these methods if these are random strings coming from external sources. No matter what method you use (client-side or server-side dynamic strings executed as queries), it would be a recipe for disaster as it opens you to SQL injection attacks.

MySQL: Add constraint if not exists

Interesting question. You may want to disable foreign keys before you call your CREATE TABLE statements and enable them afterwards. This will allow you to define the foreign keys directly in the CREATE TABLE DDL:

Example:

SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

CREATE TABLE IF NOT EXISTS `rabbits` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `main_page_id` INT UNSIGNED COMMENT 'What page is the main one',
    PRIMARY KEY (`id`),
    KEY `main_page_id` (`main_page_id`),
    CONSTRAINT `fk_rabbits_main_page` FOREIGN KEY (`main_page_id`) REFERENCES `rabbit_pages` (`id`)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

CREATE TABLE IF NOT EXISTS `rabbit_pages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `rabbit_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `content` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    KEY `rabbit_id` (`rabbit_id`),
    CONSTRAINT `fk_rabbits_pages` FOREIGN KEY (`rabbit_id`) REFERENCES `rabbits` (`id`)
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.16 sec)

SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

Test case:

INSERT INTO rabbits (name, main_page_id) VALUES ('bobby', NULL);
Query OK, 1 row affected (0.02 sec)

INSERT INTO rabbit_pages (rabbit_id, title, content) VALUES (1, 'My Main Page', 'Hello');
Query OK, 1 row affected (0.00 sec)

SELECT * FROM rabbits;
+----+-------+--------------+
| id | name  | main_page_id |
+----+-------+--------------+
|  1 | bobby | NULL         |
+----+-------+--------------+
1 row in set (0.00 sec)

SELECT * FROM rabbit_pages;
+----+-----------+--------------+---------+
| id | rabbit_id | title        | content |
+----+-----------+--------------+---------+
|  1 |         1 | My Main Page | Hello   |
+----+-----------+--------------+---------+
1 row in set (0.00 sec)

UPDATE rabbits SET main_page_id = 2 WHERE id = 1;
ERROR 1452 (23000): A foreign key constraint fails

UPDATE rabbits SET main_page_id = 1 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

UPDATE rabbit_pages SET rabbit_id = 2 WHERE id = 1;
ERROR 1452 (23000): A foreign key constraint fails

---

Related Topics