Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Sql Anywhere


  • Please log in to reply
7 replies to this topic

#1 Geoff777

Geoff777

  • Members
  • 235 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:England
  • Local time:09:55 AM

Posted 24 October 2006 - 10:18 AM

Hi all,
l am creating a database and wish to implement this constraint by adding it to the create table statement.

I have a members club where each new member must be introduced by an existing member. However to prevent little 'gangs' forming an existing member may only introduce a maximum of two members each month .(The founder member 'm01' introduced himself, included in the maximum constraint).

I am trying to implement this constraint using the two columns date_joined and introduced_by_id.


I have tried various methods of using COUNT and am now wondering if l am barking up the wrong tree!

can anyone point me in the right direction please?

I have considered:

SELECT COUNT (*)
FROM member
GROUP BY introduced_by_id, MONTH(date_joined)

if this query returns a count greater than 2 for any group then the constraint is violated.

How do l implement this query as a CHECK constraint using SQL Aywhere??


Many thanks
Geoff

BC AdBot (Login to Remove)

 


m

#2 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:03:55 AM

Posted 24 October 2006 - 03:46 PM

Are you trying to prevent the condition from being violated, or are you trying to flag when a violation is discovered? (Just to clarify)?

Their help pages leave a lot to be desired.

#3 Geoff777

Geoff777
  • Topic Starter

  • Members
  • 235 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:England
  • Local time:09:55 AM

Posted 25 October 2006 - 01:12 AM

Hi

I am trying to prevent a member from introducing more then two people per month.

So, l want to place a CHECK constraint within my CREATE TABLE statement which will accomplish this.

It should go something like:

CHECK ( ?????????? =
SELECT COUNT (*)
FROM member
GROUP BY introduced_by_id, MONTH(date_joined)

Now, if this query returns a count greater than 2 for any group then the constraint is violated.
What l want to know is how to incorporate the query into a check constraint, and ultimately into a create table statement.

yes l agree, their help pages are of little use, l have been trying to find the answer for this forever :thumbsup:

many thanks
Geoff

#4 Geoff777

Geoff777
  • Topic Starter

  • Members
  • 235 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:England
  • Local time:09:55 AM

Posted 25 October 2006 - 01:14 AM

sorry Groove,

trying to prevent the condition from being violated
Geoff

#5 Geoff777

Geoff777
  • Topic Starter

  • Members
  • 235 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:England
  • Local time:09:55 AM

Posted 25 October 2006 - 01:11 PM

Hi,

l have got this far :

CHECK ( ?????????? =
(SELECT COUNT (*)
FROM member
GROUP BY introduced_by_id, MONTH(date_joined)
HAVING COUNT (*) <= 2 );


Any more ideas guys please??

Many thanks
Geoff

#6 Geoff777

Geoff777
  • Topic Starter

  • Members
  • 235 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:England
  • Local time:09:55 AM

Posted 26 October 2006 - 07:07 AM

Hi all,

eureka!!!

if anyone is interested, here is the code:

CREATE TABLE member
(member_id CHAR(3),
name VARCHAR(20) NOT NULL,
address CHAR(40) NOT NULL,
telephone_no CHAR(15),
email CHAR(25),
date_joined DATE,
introduced_by_id CHAR(3) NOT NULL,
PRIMARY KEY (member_id),
FOREIGN KEY (introduced_by_id) references member,
CHECK (NOT EXISTS (SELECT introduced_by_id
FROM member
GROUP BY introduced_by_id, MONTH(date_joined), YEAR(date_joined)
HAVING COUNT (*)>2 )));


Thank you to everyone who is still searching for me.
Geoff

#7 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:03:55 AM

Posted 26 October 2006 - 07:11 AM

Thanks for sharing your conclusion. Now that I look at your answer, I am able to understand your question. Otherwise I would have been bale to help yesterday. :thumbsup:

#8 Geoff777

Geoff777
  • Topic Starter

  • Members
  • 235 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:England
  • Local time:09:55 AM

Posted 26 October 2006 - 01:38 PM

No problem Groovicus. Thanks for taking the time :thumbsup:
Geoff




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users