PDA

View Full Version : Newbie

franz1999
03-07-02, 16:28
I am a student and I am learning how to use sybase...
Doesn anybody know how to CREATE a DOMAIN? And how to CREATE an ASSERTION? I'd appreciate some help because I can't find anything in the manual and I kind of need:D those two for my project!
Thanks!

:confused:
MattR
03-18-02, 20:19
It sounds like you're using relational algebra names (e.g. mathematical terms). Sybase knows 'databases', 'tables', 'constraints', etc. Maybe try and explan what you mean by them, or what they're called in other database management systems. Have you checked out the link at the top of the forum page with 'Sybase Documentation'? It should give you the answers once we figure out what you're trying to do ;)
franz1999
03-18-02, 22:48
Let's say that you have one table with professors, one table with courses taught in a university and one table with the relationship 'teaching' that relates profID to courseCodes...
Now if you want to enforce that 'every professor must teach AT LEAST one course' on my book i see something called 'partecipation constraint'.

To do that my teacher taught me that we need to create an assertion, which is a constraint defined outside of every table; something like:

CREATE ASSERTION TeachAtLeastOneCourse
CHECK( NOT EXIST(
SELECT * FROM Professor P
WHERE NOT EXIST (
SELECT * FROM Teaching T
WHERE P.id = T.profId )))

which means 'check that no professor exist such that he is not teaching any course'.

Now, Sybase is not allowing ASSERTIONS, so how can I enforce something like this?
MattR
03-18-02, 23:11
Well, that can be accomplished one of two ways (or even more, but automagically this way):
1) CHECK constraints (what you call ASSERTIONs)
2) Triggers

Check constraints are run upon data modification and are typically included in the CREATE TABLE statement. For a more in-depth look, please visit:
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/20776;pt=20776?DwebQuery=check#X

Constraints cannot contain subqueries and the like, so your example may require a trigger.

Triggers are essentially bits of T-SQL code that is automatically run when you change data in tables (update, delete, insert). A check constraint is basically a trigger which you do not explicitly create and is run every time a column is modified (e.g. for a trigger you can specify action on DELETE only, and not insert; check constraints are run every time a column is modified). Triggers are generally used for more complex constraints.

ASE does not support 'before' triggers so essentially after you insert a row, then you'd run your check, and you'd have to 'roll back' the transaction which was just created (the insert statement).

To read more about triggers, please visit (I can't link to it directly for some reason):
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookView

Click on "Transact SQL Users Guide" in the left pane, then click "Chapter 16 Triggers: Enforcing Referential Integrity". It speaks specifically of foreign key integrity but you can easily adapt to your type of assertion.