UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Check constraint    
 
   
m4ttd4vies
post Jan 23 2007, 10:58 AM
Post #1

New Member
Posts: 2



Hi,

I got a table I want to add a check constraint to.
I got a query [on the table] with the desired constraint in the where clause:

SELECT LOG.WORK_ID, LOG.ACTIVITY_ID, LOG.SUBJECT, LOG.TIME
FROM LOG
WHERE
(
((LOG.ACTIVITY_ID) Between 1 And 5)
Or
((LOG.ACTIVITY_ID) Between 6 And 7) And ((LOG.SUBJECT) In ('Other','IT'))
);

perhaps not the best way to phrase the constraint [/where] but how may I add this as a check constraint to the LOG table?

btw
new to this apols for anything not correct/missing
thnx
Go to the top of the page
 
+
jsitraining
post Jan 23 2007, 11:10 AM
Post #2

UtterAccess VIP
Posts: 5,241
From: Scotland (Sunny Glasgow)



Hi, welcome to UA.
You can go to table design view and set the 'Validation rule' property of the field(s) that require(s) the check constraint. Alternatively you could code it

ALTER TABLE tablename ADD CONSTRAINT constraintname CHECK (check constraint)

HTH
Jim
Go to the top of the page
 
+
HiTechCoach
post Jan 23 2007, 11:11 AM
Post #3

UtterAccess VIP
Posts: 18,663
From: Oklahoma City, Oklahoma



Welcome to Utter Access

Try:

CODE
SELECT LOG.WORK_ID, LOG.ACTIVITY_ID, LOG.SUBJECT, LOG.TIME

FROM LOG

WHERE

(

      ((LOG.ACTIVITY_ID) Between 1 And 5)

Or

    (

        ((LOG.ACTIVITY_ID) Between 6 And 7) And ((LOG.SUBJECT) In ('Other','IT'))

     )

);


I added ( ) around " ((LOG.ACTIVITY_ID) Between 6 And 7) And ((LOG.SUBJECT) In ('Other','IT'))"




Edited by: HiTechCoach on Tue Jan 23 11:13:06 EST 2007.
Go to the top of the page
 
+
jsitraining
post Jan 23 2007, 11:15 AM
Post #4

UtterAccess VIP
Posts: 5,241
From: Scotland (Sunny Glasgow)



Hmmmm, not sure how that would add a check constraint to a table Boyd (IMG:http://www.utteraccess.com/forum/style_emoticons/default/shrug.gif)
Go to the top of the page
 
+
Peter46
post Jan 23 2007, 11:20 AM
Post #5

UtterAccess VIP
Posts: 7,489
From: Oadby Leics, UK



Field validation rules cannot refer to other fields in the table so you cannot do this there.

However, you can do one table-level validation which you set in table properties.

([ACTIVITY_ID] Between 1 And 5) Or ([ACTIVITY_ID] Between 6 And 7 And [SUBJECT] In ('Other','IT'))

Otherwise you would have to use a Form_Beforeupdate event procedure, or other suitable event procedure to apply the check during data entry.
Go to the top of the page
 
+
m4ttd4vies
post Jan 24 2007, 08:30 AM
Post #6

New Member
Posts: 2



Hello & Many Thnx All
Props:
jsitraining & Peter46
Table design view > properties > Validation Rule !Doh!
just couldnae find it!
All good now

academic point:

@ one point I did try to do a:
ALTER TABLE tablename ADD CONSTRAINT constraintname CHECK (check constraint)
but it was comming back with a syntax error...

I was in Queries > Create query in Design view > Show Table> LOG > SQL view >
ADD CONSTRAINT constraintname CHECK (check constraint)
I omited alter... and got "syntax error in constraint clause"

equaly I've just tried:
Queries > Create query in Design view > Show Table> LOG > SQL view >
ALTER TABLE LOG ADD CONSTRAINT ONE CHECK (1=1);
> "syntax error in constraint clause"

and
Queries > Create query in Design view > Show Table > X (close) > SQL view >
ALTER TABLE LOG ADD CONSTRAINT ONE CHECK (1=1);
> "syntax error in constraint clause"

Sorry if I ought to do this as new Q\thread, but,
1) where/how may i enter ddl such as ALTER TABLE?
2) what was wrong with the syntax?

thnx in advance
matt

Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 18th April 2014 - 08:48 AM