| View previous topic :: View next topic |
| Author |
Message |
dietry
Newbie
Joined: 06 Sep 2005
Posts: 1
Location: belgium
|
 Posted: Tue Sep 06, 2005 1:03 am Post subject: unique combination of data |
 |
|
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 |
|
 |
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
| Posted: Sun Oct 02, 2005 1:20 pm Post subject: |
|
|
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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sun Oct 02, 2005 2:56 pm Post subject: |
|
|
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 |
|
|
|
|
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
|
