Advanced Constraint

[kooter12000]

Hi! ALL

I'm an novice programmer. Been trying to set up a database for an accounting program. What I want to do is have one table that contains the department number and name and another table that contains the account number and name. However I want the last 5 digits of the account number to refer to the department number that would be in the department table. I also want it to give me an error message if the last 5 digits do not match with a department number from the depatment table.

Thanks
Kooter12000

[rski]

Hi! ALL

I'm an novice programmer. Been trying to set up a database for an accounting program. What I want to do is have one table that contains the department number and name and another table that contains the account number and name. However I want the last 5 digits of the account number to refer to the department number that would be in the department table. I also want it to give me an error message if the last 5 digits do not match with a department number from the depatment table.

Thanks
Kooter12000

in think you will need a trigger. give more details (table and column names etc.) and i willl try to help you

[kooter12000]

Thanks for the quick reply

What I've done thus far

CREATE TABLE acct_type(
acct_type char(1) check(acct_type IN ('b','i','r') primary key not null);

*b represents balance sheet accounts, i represents income statement accounts and r represents retained account*

CREATE TABLE acct(
acct_num char(4) not null primary key,
acct_name text not null,
acct_type char(1) references acct_type);

*acct table for balance sheet accounts*

CREATE TABLE dept(
dept_num char(5) not null primary key,
dept_name text not null);

For income statement accounts trying to do this

CREATE TABLE accdept(
acct_dept_num char(9) *this where I'm stuck, I want the last 5 characters to refer to the dept_num of the dept table*
acct_dept_name text not null,
acct_type char(1) references acct_type

*accdept table to be used for income statement accounts*

hope this info is helpful

Kooter12000

[rski]

Thanks for the quick reply

What I've done thus far

CREATE TABLE acct_type(
acct_type char(1) check(acct_type IN ('b','i','r') primary key not null);

*b represents balance sheet accounts, i represents income statement accounts and r represents retained account*

CREATE TABLE acct(
acct_num char(4) not null primary key,
acct_name text not null,
acct_type char(1) references acct_type);

*acct table for balance sheet accounts*

CREATE TABLE dept(
dept_num char(5) not null primary key,
dept_name text not null);

For income statement accounts trying to do this

CREATE TABLE accdept(
acct_dept_num char(9) *this where I'm stuck, I want the last 5 characters to refer to the dept_num of the dept table*
acct_dept_name text not null,
acct_type char(1) references acct_type

*accdept table to be used for income statement accounts*

hope this info is helpful

Kooter12000

try this
1. first create a function

create or replace function f() returns trigger as $$
declare
r record;
begin
select * into r from dept where dept_num =substring(NEW. i from 5 for 5);
if not found then
raise info 'constraint error';
return NULL;
else
raise info 'constraint fulfill';
return NEW;
end if;
end;
$$ language plpgsql

next create a trigger

create trigger t_acdept before insert on accdept for each row execute procedure f();

it's late but i think it should work.
let me know if it is helpfull.

[kooter12000]

hi!
When I try to enter values in acctdept get the following message:

ERROR: record "new" has no field "i"
CONTEXT: PL/pgSQL function "f" line 4 at sql statement

Kooter12000

[rski]

hi!
When I try to enter values in acctdept get the following message:

ERROR: record "new" has no field "i"
CONTEXT: PL/pgSQL function "f" line 4 at sql statement

Kooter12000

oh sorry instead of NEW.i you should write NEW.acct_dept_n um, it's my mistake

[kooter12000]

Thanks

Working perfectly I would like to understand what I did specially:

=substring(new. acct_dept_num from 5 for 5)

Thanks again

Kooter12000

[rski]

Thanks

Working perfectly I would like to understand what I did specially:

=substring(new. acct_dept_num from 5 for 5)

Thanks again

Kooter12000

i'm not good at english so it would be better you read the postgres string functions manual here
see you

[kooter12000]

i'm not good at english so it would be better you read the postgres string functions manual here
see you

Thanks rski

The link you provided was very helpful. I understand it now.

Kooter12000