Postgresql data validation checks based on table values

Question

I'm trying to create a database that tracks electrical cables. Each cable contains 1 or more cores that are connected to terminals at each end. The number of cores in each cable is defined in a table.

| number_of_cores  | cable_id
|----------2-------|---1-----|

The core table is as follows

cable_no   | from_id | core_mark | to_id
1001       |       1 | 1 Black   |     2
1001       |       2 | 1 White   |     4

I want to create a check that will prevent another 1001 cable core from being inserted.

Is this possible in postgresql?

Ideally, if I tried to insert another 1001 cable with another unique core number, the error would be something like "all cores used on cable 1001"

Thanks,

Does that mean you should have only 2 similar (cable_no) at the maximum? — Mohamed Anees A
– Mohamed Anees A, Commented Apr 25, 2019 at 8:38

Jaisus · Accepted Answer · 2019-04-25 10:30:39Z

1

I think what you need is something like a check constraint. (https://www.postgresql.org/docs/current/ddl-constraints.html)

Follow those steps :

1. Create some table properly

create table cable (cable_id int primary key, number_of_cores int);
create table core (core_id int primary key, cable_id int references cable (cable_id), from_id int, core_mark varchar (50), to_id int);

2. Create the function that will verify the inserts

create or replace function test_max_core_number(in_cable_id int)
 returns boolean
 language plpgsql
as $function$
declare
    res boolean := false;
begin
    if exists (
        select *
        from cable 
        where cable_id = in_cable_id
            and number_of_cores > (select count(*) from core where cable_id = in_cable_id )
    )
    then 
        res := true;
    end if;

    return res;
end;
$function$;

3. Add the constraint to your table

alter table core 
add constraint cstr_check check (test_max_core_number(cable_id));

4. Now it is time for some testing :)

insert into cable (cable_id, number_of_cores) values (1, 2), (2, 3);
insert into core (core_id, cable_id, from_id, core_mark, to_id)
values
    (1, 1, 1, '1 Black', 2)
    ,(2, 1, 2, '1 White', 4);

Normally all goes fine for now.

5. And now the wanted error !

insert into core (core_id, cable_id, from_id, core_mark, to_id)
values
    (3, 1, 3, '1 Green', 2);

Hope this helps !

answered Apr 25, 2019 at 10:30

Jaisus

1,1176 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sergey Menshov Over a year ago

A good answer. I added my answer with a cross-check for cable.number_of_cores.

Sergey Menshov · Accepted Answer · 2019-04-26 03:12:48Z

0

I think @Jaisus gave a good answer.

I would add only a cross-check into the cable to prevent to set bad values into number_of_cores:

create or replace function test_cable_number_of_cores(in_cable_id int,in_number_of_cores int)
 returns boolean
 language plpgsql
as $function$
declare
    res boolean := false;
begin
    res := (in_number_of_cores>0 and (select count(cable_id) from core where cable_id=in_cable_id) <= in_number_of_cores);

    return res;
end;
$function$;


alter table cable add check(test_cable_number_of_cores(cable_id, number_of_cores));

-- ok
insert into cable(cable_id, number_of_cores) values (3, 2);
update cable set number_of_cores=3 where cable_id=3;

-- error
update cable set number_of_cores=1 where cable_id=1;

answered Apr 26, 2019 at 3:12

Sergey Menshov

3,9062 gold badges11 silver badges22 bronze badges

1 Comment

Kyabram Over a year ago

Thanks to all of you helping out. I followed the steps for my version and got it working.

Collectives™ on Stack Overflow

Postgresql data validation checks based on table values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related