Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2010
    Posts
    12

    Post Unanswered: Check / Trigger / Assertion Help

    I have these four tables:

    CREATE TABLE Event (
    [EventID] [bigint] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](32) NULL,
    [Created] [smalldatetime] NOT NULL,
    [Modified] [smalldatetime] NULL,
    [SmIcon] [varbinary](max) NULL,
    [LgIcon] [varbinary](max) NULL,
    [CategoryID] [tinyint] NULL,
    CONSTRAINT [PK_Event_EventID] PRIMARY KEY CLUSTERED
    )

    CREATE TABLE EventDream (
    [EventID] [bigint] NOT NULL,
    [Text] [nvarchar](max) NOT NULL,
    [WhenStart] [smalldatetime] NULL,
    [WhenEnd] [smalldatetime] NULL,
    CONSTRAINT [PK_EventDream] PRIMARY KEY CLUSTERED
    )

    CREATE TABLE EventJournal (
    [EventID] [bigint] NOT NULL,
    [Text] [nvarchar](max) NOT NULL,
    PRIMARY KEY CLUSTERED
    )

    CREATE TABLE Category (
    [CategoryID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](32) NOT NULL,
    [Description] [nvarchar](64) NULL,
    [SmIcon] [varbinary](max) NULL,
    [LgIcon] [varbinary](max) NULL,
    CONSTRAINT [PK_Category_CategoryID] PRIMARY KEY CLUSTERED
    )

    Each event in table Event can only belong to one category. Based on this type in table Event, I want to populate the appropriate corresponding table with the details (EventJournal and EventDream). But before I insert records into the appropriate table, I want to check that the other table does not already have a record for that particular EventID. EventID is used in Event, EventJournal, and EventDream to reference a particular post.

    I'm pretty new to databases (I'm using Microsoft SQL Server 2008) and saw some stuff on checks, assertions, and triggers, but I don't even know where to start. I tried using a check:

    check (( Select count(*) from EventDream where EventDream.EventID) = 0)

    but SQL Server Management Studio said that I couldn't use subqueries in checks. Can anyone throw me a bone?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is certainly in BoL, and I think it is in the CHECK constraint definition, but you can use a scalar function in a check constraint. As such you can you move your query to the scalar function and it will be allowed.

    Quote Originally Posted by jeff0000 View Post
    But before I insert records into the appropriate table, I want to check that the other table does not already have a record for that particular EventID. EventID is used in Event, EventJournal, and EventDream to reference a particular post.

    I'm pretty new to databases (I'm using Microsoft SQL Server 2008)
    However, I would be interested in knowing what you mean by the above. By definition you do not need to check this - your EventID is defined as a primary key so cannot exist anywhere assuming of course that you have set up the appropriate foreign keys.

    Also, if you say that an event can be one of two categories and can only appear in the appropriate table then it is also possible to define the foreign keys in such a way that we enforce this declaratively.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2010
    Posts
    12
    Thanks for your reply, pootle flump.

    According to Functions (Transact-SQL), scalar functions "Operate on a single value and then return a single value."

    If my check statement is:

    Select count(*) from EventDream where EventDream.EventID) = 0

    then my database needs to run a subquery because it has to first count the number of columns, then limit the results because of the 'where' clause (SQL Subquery). How do I construct a scalar function out of the statement above?

    As to your second question, I'm not sure I understand what you mean. I have different type of events in my database (EventDream and EventJournal), and the details for EventDream are different than the details of EvenJournal (different fields are populated). In EventDream, there is a start and end time to the dream (EventJournal does not have these fields).

    An event can only belong to one category, and based on that category (a numeric value), I only want to make an appropriate record in EventDream or EventJournal, based on the CategoryID field in the table Event. I want to implement this rule on the database level.

    Does this make more sense?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Move your query to the scalar function body, so it returns the count.

    The check is now:
    Code:
    ...CONSTRAINT ch_foo CHECK (dbo.Bar() = 0)
    As to your second point, that was exactly what I was describing!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to emphasise - although that is how you would check the contents of another table you do not need to do it in this case. I'll pick this up with you tomorrow (night time in UK) if no one has helped you sort it out before then.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Am I correct that Category only holds 2 values: "eventDream" and "eventJournal" ?

    Suppose the CategoryID of "eventDream" = 1 and the CategoryID of "eventJournal" = 2, you could enforce the constraint declarative:
    Code:
    CREATE TABLE EventDream (
    [EventID] [bigint] NOT NULL,
    [CategoryID] [tinyint] NOT NULL DEFAULT 1 
     CONSTRAINT CC_EventDream_CategoryID CHECK CategoryID = 1,
    [Text] [nvarchar](max) NOT NULL,
    [WhenStart] [smalldatetime] NULL,
    [WhenEnd] [smalldatetime] NULL,
    CONSTRAINT [PK_EventDream] PRIMARY KEY CLUSTERED,
    CONSTRAINT FK_EventDream_Event FOREIGN KEY (EventID, CategoryID) REFERENCES Event(EventID, CategoryID)
    )
    
    CREATE TABLE EventJournal (
    [EventID] [bigint] NOT NULL,
    [CategoryID] [tinyint] NOT NULL DEFAULT 2,
     CONSTRAINT CC_EventJournal_CategoryID CHECK CategoryID = 2,
    [Text] [nvarchar](max) NOT NULL,
    PRIMARY KEY CLUSTERED,
    CONSTRAINT FK_EventJournal_Event FOREIGN KEY (EventID, CategoryID) REFERENCES Event(EventID, CategoryID)
    )
    With the CategoryID DEFAULT you don't have to worry about specifying the correct value during record creation
    and with the CategoryID CONSTRAINT, you can assert that no false values can ever be entered in that column.

    As Event.CategoryID can only have 1 value at a given time, only one of EventDream or EventJournal (XOR) can reference it.

    I wonder if Pootle has anther way of enforcing such a constraint declaratively.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jul 2010
    Posts
    12
    Looks like I forgot to add the table with the category lookup values, and I also forgot to add the foreign key between table Event and table Category.

    CREATE TABLE Category (
    [CategoryID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](32) NOT NULL,
    [Description] [nvarchar](64) NULL,
    [SmIcon] [varbinary](max) NULL,
    [LgIcon] [varbinary](max) NULL,
    CONSTRAINT [PK_Category_CategoryID] PRIMARY KEY CLUSTERED
    )

    CREATE TABLE Event (
    [EventID] [bigint] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](32) NULL,
    [Created] [smalldatetime] NOT NULL,
    [Modified] [smalldatetime] NULL,
    [SmIcon] [varbinary](max) NULL,
    [LgIcon] [varbinary](max) NULL,
    [CategoryID] [tinyint] NOT NULL,
    CONSTRAINT [PK_Event_EventID] PRIMARY KEY CLUSTERED
    CONSTRAINT FK_Event_Category_CategoryID FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID)
    )

    But since CategoryID in Event is a foreign key to the primary key (CategoryID) in table Category (Category contains all the descriptions of the different types of events), will sql let me link CategoryID in EventDream to CategoryID in Event? I'm not sure if sql will let me join two foreign keys together?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes it will allow you to do that. You must create a UNIQUE constraint on (EventID, CategoryID) in Event. This is known as a Super Key (it uniquely identifies the row but also has some attributes that are not required for uniqueness) and then create the foreign keys Wim created.

    Also, I would change this:
    Code:
    CREATE TABLE Category (
    	[CategoryID] [tinyint] IDENTITY(1,1) NOT NULL,
    to this
    Code:
    CREATE TABLE Category (
    	[CategoryID] [tinyint] NOT NULL,
    You must control the value of CategoryID for this sort of design to work.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    But since CategoryID in Event is a foreign key to the primary key (CategoryID) in table Category (Category contains all the descriptions of the different types of events), will sql let me link CategoryID in EventDream to CategoryID in Event?
    Sure. The Foreign Key (KF) is defined from EventDream to Event. The FK constraint will only allow values for the (EventID, CategoryID)-combination in EventDream when that very same combination can also be found in Event.

    The same holds for EventJournal: the FK constraint will only allow values for the (EventID, CategoryID)-combination in EventJournal when that very same combination can also be found in Event.

    Just don't define the FK from EventDream to Category. You can do it, but it would not enforce the constraint you want: an Event is (an EventDream XOR an EventJournal).

    I'm not sure if sql will let me join two foreign keys together?
    It does.
    Code:
    SELECT ...
    FROM EventDream 
       INNER JOIN Event ON
          EventDream.EventID = Event.EventID AND  
          EventDream.CategoryID = Event.CategoryID
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Using Identity columns for lookup tables can be a pain when you go from development database to test database to production database if you cannot control the values of the Id's.

    Though with some care, you can handle that with numeric identity columns (first dropping the table then reinserting the records, or resetting the identity values after deleting the records) but it is totally impossible when you would use GUIDs as PK. I have never heard any good explanation on how to deal with going from development database to test database to production database when you use GUIDs for the PK's of lookup tables.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Wim View Post
    Though with some care, you can handle that with numeric identity columns (first dropping the table then reinserting the records, or resetting the identity values after deleting the records) but it is totally impossible when you would use GUIDs as PK. I have never heard any good explanation on how to deal with going from development database to test database to production database when you use GUIDs for the PK's of lookup tables.
    Never, ever hard code GUIDs. Vwalah - it no longer matters.

    The problem here is that the key is no longer surrogate - the values must be known.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2010
    Posts
    12
    Ok. Thanks for that. I had to create all my tables again because certain things weren't going right, but before I'm done with that I have another issue.

    I decided not to make CategoryID in Event have the "NOT NULL" constraint because I wanted to set the category to NULL in case the CategoryID from table Category got deleted. pootle, does this violate the definition of a Super Key? Or maybe I should set the category to a predetermined negative number if the CategoryID of an Event is deleted?

  13. #13
    Join Date
    Jul 2010
    Posts
    12
    Also, what should the primary keys in EventDream and EventJournal be? Can a key be both a primary and foreign key at the same time?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Given that you have specific entities related to your categories, allowing NULL does not make much sense based on what we know.
    However no, it doesn't affect the super key as such but it does complicate things a lot.

    Yes, an FK can be a PK. Your relationship from Event to EventDream is 1:1, correct? So EventID is your PK.

    BTW - do you really expect to record more than 2 billion dreams? At a rate of 1 million dreams a day that would take over five years to use up. If not, change your EventID to INT, or maybe even SmallInt.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I decided not to make CategoryID in Event have the "NOT NULL" constraint because I wanted to set the category to NULL in case the CategoryID from table Category got deleted.
    If a certain CategoryID gets deleted, you will also have to delete the corresponding table (like EventDream).

    If a CategoryID from table Category got deleted, you will also have to do something with the records from Event that point to it (like delete them or update the CategoryID to another value). It is not something that will happen out of the blue. And making it NULLable, is not a solution.

    Keeping or not keeping the NOT NULL is not a matter of what can happen to the Category records. But depends on the business rule that defines whether an Event can or cannot exist without belonging to any Category.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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