Create Table Conditional

[askohen]

I have this CREATE TABLE statement:

[CODE=mysql]CREATE TABLE howto (
id INT IDENTITY(1,1) PRIMARY KEY,
title VARCHAR(100) UNIQUE NOT NULL,
url VARCHAR(20) UNIQUE NOT NULL,
order_on_homepa ge 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[/CODE]

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:


[CODE=mysql]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[/CODE]

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