Create Table Conditional

[askohen]

I have this CREATE TABLE statement:

CREATE TABLE howto (
    id INT IDENTITY(1,1) PRIMARY KEY,
    title VARCHAR(100) UNIQUE NOT NULL,
    url VARCHAR(20) UNIQUE NOT NULL,
    order_on_homepage SMALLINT UNIQUE, --just put an order on this and if it's not null, then its on the homepage
    CONSTRAINT CK_HOWTO_URL CHECK(url != ''),
    CONSTRAINT CK_HOWTO_TITLE CHECK(title != '')
);
GO

I want order_on_homepa ge col to either have a SMALLINT value or a NULL value. However, if there is a SMALLINT value entered, I want those to be unique. In other words, I want to be able to add multiple NULL values in this col, but NOT multiple SMALLINT values. Any suggestions? A check? Thanks!

[ck9663]

I have this CREATE TABLE statement:

CREATE TABLE howto (
    id INT IDENTITY(1,1) PRIMARY KEY,
    title VARCHAR(100) UNIQUE NOT NULL,
    url VARCHAR(20) UNIQUE NOT NULL,
    order_on_homepage SMALLINT UNIQUE, --just put an order on this and if it's not null, then its on the homepage
    CONSTRAINT CK_HOWTO_URL CHECK(url != ''),
    CONSTRAINT CK_HOWTO_TITLE CHECK(title != '')
);
GO

I want order_on_homepa ge col to either have a SMALLINT value or a NULL value. However, if there is a SMALLINT value entered, I want those to be unique. In other words, I want to be able to add multiple NULL values in this col, but NOT multiple SMALLINT values. Any suggestions? A check? Thanks!

I think a trigger would be better for this one.

-- CK

[askohen]

Any hints on how to do this? I created a test trigger, but not sure how to go forward,

CREATE TRIGGER test_howto
    ON howto
    FOR INSERT
        AS Print('foo');
GO

[ck9663]

I did not compile this:

CREATE TRIGGER TRG_HowtO
ON HowTo
FOR INSERT, UPDATE
as
   begin tran
      if exists (select 1 from HowTo inner join inserted on HowTo.Order_on = inserted.Order_On)
         rollback tran
      else
         commit tran
 go

If the value of Order_On you inserted is NULL the exists function will still return false since you can not compare 2 NULLs.

-- CK