OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

unique combination of data

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dietry
Newbie
Newbie


Joined: 06 Sep 2005
Posts: 1
Location: belgium

PostPosted: Tue Sep 06, 2005 1:03 am    Post subject: unique combination of data Reply with quote

Hey,

I'm working on a database and have following question. I want to make a table with 3 colums (nr_client; month; year) and the combination of the 3 colums should be unique.

Is this possible?
Back to top
View user's profile Send private message Send e-mail
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Sun Oct 02, 2005 1:20 pm    Post subject: Reply with quote

Is this an OOo question?
On which database are you working? OOob2?
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs
Back to top
View user's profile Send private message Visit poster's website
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Oct 02, 2005 2:56 pm    Post subject: Reply with quote

What you will need is a unique constraint that uses all three columns. This can be just a constraint, or you could make all of them a primary key for a table. I would recommend the former.

Looking at how to do this in OOBase native data format we would do this.

I will use the following example table
Code:

CREATE CACHED TABLE "ClientSignUp"("ID" IDENTITY PRIMARY KEY, "nr_client" INTEGER NOT NULL,"month" TINYINT NOT NULL,"year" SMALLINT NOT NULL)


Now to add the unique constraint open the SQL window (Tools>SQL) and enter the ALTER TABLE ADD CONSTRAINT command there. You can find the proper syntax in the HSQLDB users manual at www.hsqldb.org

Code:

ALTER TABLE "ClientSignUp" ADD CONSTRAINT unq_signup UNIQUE ("nr_client", "month", "year")


This will allow for example

0, 1001, 11, 2004
1,.1001, 12, 2004
2, 1002, 11, 2004
3, 1003, 11, 2004

But trying to add

4, 1001, 11, 2004

again will generate a CONSTRAINT VIOLATION error and not allow the update to the table.

The syntax, as I said, is that for OOBase native data format, but the concept is pretty much the same in any RDBMS.

HTH

Drew Jensen
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group