By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,956 Members | 1,657 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,956 IT Pros & Developers. It's quick & easy.

Advanced Constraint

P: 8
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
Feb 1 '08 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 700
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
Feb 1 '08 #2

P: 8
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
Feb 1 '08 #3

Expert 100+
P: 700
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.
Feb 1 '08 #4

P: 8
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
Feb 1 '08 #5

Expert 100+
P: 700
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_num, it's my mistake
Feb 2 '08 #6

P: 8
Thanks

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

=substring(new.acct_dept_num from 5 for 5)

Thanks again

Kooter12000
Feb 2 '08 #7

Expert 100+
P: 700
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
Feb 2 '08 #8

P: 8
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
Feb 6 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.