Results 1 to 4 of 4

Thread: Newbie

  1. #1
    Join Date
    Mar 2002
    Posts
    2

    Arrow Unanswered: Newbie

    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 those two for my project!
    Thanks!


  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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
    Thanks,

    Matt

  3. #3
    Join Date
    Mar 2002
    Posts
    2

    Example

    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?

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    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/onlinebook...TextView/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/onlinebook...eric__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.
    Thanks,

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •