Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Sanjay Sharma
  Where is Sanjay Sharma?
 Rishra
 India
 Sanjay Sharma
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: Check to prevent duplicate at Database
Thread ID: 331505 Message ID: 331523 # Views: 60 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Friday, December 30, 2011 6:53:47 PM         
   


> > I have a table like the following:
> >
> > chart of accounts | gl account group | number of G/L account (lower area limit) | number of G/L account (upper area limit) | long text
> >
> > CAPE | ADCU | 0014390000 | 0014399999 | Advance from customers
> > CAPE | ADIC | 0025010000 | 0025019999 | Advance Inter Company
> > CAPE | ADIN | 0025000000 | 0025009999 | Advance Inter Unit
> > CAPE | ADMS | 0025200000 | 0025299999 | Advance for Material / Servic
> >
> > I am unable to maintain checking to prevent any duplication between "number of G/L account (lower area limit)" and "number of G/L account (upper area limit)" against Chart of accounts at the database level.
> >
> > Thanks.
>
> Every, every!, absolutely each and every, table in a relational system must have a primary key, a unique key, in one or a combination of more columns.
> What's the PK of your table?
> Such a column is safeguarded by the system through a Primary Key index or a Candidate (Unique) index. See the commands CREATE TABLE and ALTER TABLE in the documentation. The MODIFY STRUCTURE window is a visible tool and can create those indexes too.
> I'm sorry but I don't understand upper and lower limits in this scenario.
> How do you maintain correctness of 'long text?
>
> -Anders


chart of accounts | gl account group | number of G/L account (lower area limit) | number of G/L account (upper area limit) | long text :: Caption
ktopl | ktoks | vornr | bisnr | text30 :; field name
-----------------------------------------------------------------------------------------------------------------------------------------------------
CAPE | ADCU | 0014390000 | 0014399999 | Advance from customers
CAPE | ADIC | 0025010000 | 0025019999 | Advance Inter Company
CAPE | ADIN | 0025000000 | 0025009999 | Advance Inter Unit
CAPE | ADMS | 0025200000 | 0025299999 | Advance for Material / Servic

index on ktopl with candidate key.
index on ktopl+ktoks with candidate key.

I would like to prevent any number range which is falling between any number range already exists for every ktopl.


Individual gl account will be opened between the number mentioned in another table, such as:

chart of accounts | gl account group | number of G/L account | long text
ktopl | ktoks | vornr | text30
---------------------------------------------------------------------------
CAPE | ADIC | 0025010012 | (Name of a company)
CAPE | ADIN | 0025000336 | (Name of a unit)

: Valid Record : since the vornr number is between 0025010000 and 0025019999.

CAPE | ADIC | 0014390059 | (Name of a company)

: Invalid Record, since the vornr number is not between 0025010000 and 0025019999 for every ktopl and ktoks.

Account posting will be made based on the last table.

Regards,

ENTIRE THREAD

Check to prevent duplicate at Database Posted by Swarup Modak @ 12/30/2011 3:00:15 PM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/30/2011 3:10:23 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 12/30/2011 6:53:47 PM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/30/2011 9:06:53 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 12/31/2011 4:28:26 AM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/31/2011 1:23:16 PM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 12/31/2011 7:24:41 PM
RE: Check to prevent duplicate at Database Posted by tushar @ 1/1/2012 5:45:49 AM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/4/2012 2:51:18 PM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 1/4/2012 3:32:17 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/8/2012 8:16:37 AM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 1/8/2012 4:32:19 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/13/2012 8:55:58 AM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 1/13/2012 1:04:26 PM