Create table - default for column (sql 2000)

When I have a table with two columns, can the second column default to
a value based on the value from the first column on an inserted record?
I read the section below in BOL ALTER TABLE but can't make head nor
toes.
E. Alter a table to add several columns with constraints
...
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),
...
Can someone explain what REFERENCES is for?
regards,
Gerard> When I have a table with two columns, can the second column default to
> a value based on the value from the first column on an inserted record?
CREATE TABLE dbo.foo
(
column_a VARCHAR(32),
column_b AS CONVERT(CHAR(8), LEFT(column_a, 8))
);
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo;
DROP TABLE dbo.foo;
However, my suggestion is usually to have this kind of thing in a view,
since you can always calculate it at SELECT time, without having to store it
and without tempting users to try and update it, have it be included in
column lists produced by code generators, etc. etc. For example, this
accomplishes the same thing:
CREATE TABLE dbo.foo
(
column_a VARCHAR(32)
);
GO
CREATE VIEW dbo.foo_view
AS
SELECT
column_a,
column_b = LEFT(column_a, 8)
FROM
dbo.foo
GO
SET NOCOUNT ON;
INSERT dbo.foo(column_a) SELECT 'barblatmortsplunge';
SELECT column_a, column_b FROM dbo.foo_view;
DROP VIEW dbo.foo_view;
DROP TABLE dbo.foo;

> Can someone explain what REFERENCES is for?
A foreign key constraint is completely different from what you are asking
about (computed columns). REFERENCES is indicating a separate table (think
master/detail, child/parent, and just about any type of entity
relationship). If you have an Orders table, a Customers table, a Products
table and an OrderDetails table, it is usually set up something like this
(Celko, you know where you can cram your IDENTITY comments):
CREATE TABLE dbo.Products
(
ProductID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Customers
(
CustomerID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
/*...other columns...*/
);
GO
CREATE TABLE dbo.Orders
(
OrderID BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
CustomerID BIGINT NOT NULL FOREIGN KEY REFERENCES
dbo.Customers(CustomerID),
/*...other columns...*/
);
GO
CREATE TABLE dbo.OrderDetails
(
OrderID BIGINT FOREIGN KEY REFERENCES dbo.Orders(OrderID),
ProductID INT FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT,
/*...other columns...*/
PRIMARY KEY(OrderID, ProductID)
);
GO|||"References" token as shown here is a method to explain that the new
column contents must conform to the contents of another table/column
before an INSERT or UPDATE is allowed.
No related to what you are asking to get accomplished. Sounds more like
you might be asking for a trigger which should only be used as a last
ditch effort when making the changes at the (each) of the client
interface is not possible.
Example of simple trigger:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tgr_sample_insert_update]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[tgr_sample_insert_update]
GO
CREATE TRIGGER dbo.tgr_sample_insert_update ON dbo.tmp_sample
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
UPDATE inserted SET colb = cola * tax_percentage
GO
Cheers
http://rickhathaway.blogspot.com/|||Thanks to you both for your replies. I will experiment a little to see
which is best for me.
regards,
Gerard|||The computed column was not an option as it can not be updated, quite
logical really.
A trigger was too much overhead for what I was trying to achieve so I I
have resolved my issue by including the logic to set the value of the
column on the "client side"
The reason I was wondering about REFERENCES was that I hoped that
something like this would be possible:
create table aTest (
col_a int default 0,
col_b as case when col_a = 1 then 1 when col_a = 2 then 2 else 3 end
)
insert into aTest (col_a) values (0)
select * from aTest
update aTest set col_b = 9
drop table aTest
--
But as I noted above, the update cannot be done.
Thanks again for your replies.
regards,
Gerard