6

I'm having problems setting a computed column as not null.

What I want to achieve is C001,C002..., etc. and at the same time set it as not null.

I have read on a forum that this can be achieved by using the default value 0 for NULL values.
E.g., ISNULL(Price + Taxes, 0)

I have tried to apply to this formula:

('C'+right('000'+CONVERT([varchar](3),[ID],(0)),(3)))

But it didn't seem to work. Can anyone tell me what am I missing?

ALTER CreditCard accountNo AS ISNULL('C'+right('000'+CONVERT([varchar](3),[idCreditCard],(0)),(3)),0)
Keenlearner
  • 704
  • 1
  • 7
  • 21
  • Your alter statement with `isnull` creates a column that does not allow nulls. At least in my tests. However you are missing a `add` between the table name and the column name. – Mikael Eriksson Jun 21 '12 at 07:50
  • Yup. What I'm trying to create is a column that does not allow null. I tried adding an add between the table name and the column name but it didn't work. – Keenlearner Jun 21 '12 at 08:01
  • But... eh.. Thats what I said. It *does* create a column that does not allow nulls. What happened for you? What did not work? – Mikael Eriksson Jun 21 '12 at 08:13
  • Please be more specific when you ask a question, make sure it's easy to understand what you try to achieve and if you are asking a question related to databases you should also add a tag with your RDBMS to the question. – Lajos Arpad Jun 21 '12 at 08:15
  • As in when i tried to alter my column accountNo to don't allow nulls, I reveived an Error message: Unknown object type 'CreditCard' used in a create, drop or alter statement – Keenlearner Jun 21 '12 at 08:17
  • 1
    You don't need to alter the column. If the computed column uses `isnull` then it is defined with `not null`. If you need to modify an already existing computed column you first need to drop the column and then add it again with the new formula. – Mikael Eriksson Jun 21 '12 at 08:58
  • Oh my bad....I thought I could modify the column without dropping the column, anyway thanks for your help, I've finally found the answer to my problem. – Keenlearner Jun 21 '12 at 09:15
  • You haven't mentioned what database you're using, but its more common to use an `ALTER TABLE` statement. e.g. [MySQL](http://dev.mysql.com/doc/refman/5.1/en/alter-table.html) [This is also a very popular question on SO](http://stackoverflow.com/questions/92082/add-column-with-default-value-to-existing-table-in-sql-server) regarding the syntax for SQL Server – Brad Jun 21 '12 at 09:01

2 Answers2

6

I've finally found the solution to my problem!

The correct query should be:

ALTER TABLE CreditCard ADD accountNo AS ISNULL('C'+right('000'+CONVERT([varchar](3),[idCreditCard],(0)),(3)),0)

Thanks for the help guys!

Keenlearner
  • 704
  • 1
  • 7
  • 21
3

If this relates to SQL Server, you might be interested in this from MSDN.

'NOT NULL can be specified for computed columns only if PERSISTED is also specified.' http://msdn.microsoft.com/en-us/library/ms190273.aspx

... but after trying to reverse-engineering the question from the answer, I think that 'keenlearner wanted only to ensure that there was never a null value in the column without having the constraint.

Phil Factor
  • 3,134
  • 2
  • 18
  • 10