By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,549 Members | 1,717 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,549 IT Pros & Developers. It's quick & easy.

Create Table Conditional

P: 3
I have this CREATE TABLE statement:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE howto (
  2.     id INT IDENTITY(1,1) PRIMARY KEY,
  3.     title VARCHAR(100) UNIQUE NOT NULL,
  4.     url VARCHAR(20) UNIQUE NOT NULL,
  5.     order_on_homepage SMALLINT UNIQUE, --just put an order on this and if it's not null, then its on the homepage
  6.     CONSTRAINT CK_HOWTO_URL CHECK(url != ''),
  7.     CONSTRAINT CK_HOWTO_TITLE CHECK(title != '')
  8. );
  9. GO
I want order_on_homepage 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!
Mar 24 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
I have this CREATE TABLE statement:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE howto (
  2.     id INT IDENTITY(1,1) PRIMARY KEY,
  3.     title VARCHAR(100) UNIQUE NOT NULL,
  4.     url VARCHAR(20) UNIQUE NOT NULL,
  5.     order_on_homepage SMALLINT UNIQUE, --just put an order on this and if it's not null, then its on the homepage
  6.     CONSTRAINT CK_HOWTO_URL CHECK(url != ''),
  7.     CONSTRAINT CK_HOWTO_TITLE CHECK(title != '')
  8. );
  9. GO
I want order_on_homepage 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
Mar 24 '08 #2

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

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER test_howto
  2.     ON howto
  3.     FOR INSERT
  4.         AS Print('foo');
  5. GO
Mar 24 '08 #3

ck9663
Expert 2.5K+
P: 2,878
I did not compile this:


Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER TRG_HowtO
  2. ON HowTo
  3. FOR INSERT, UPDATE
  4. as
  5.    begin tran
  6.       if exists (select 1 from HowTo inner join inserted on HowTo.Order_on = inserted.Order_On)
  7.          rollback tran
  8.       else
  9.          commit tran
  10.  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
Mar 24 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.