How to enter manually a "uniqueIdentifier" type value

I created a DB and a table in Visual Studio .NET 2003. For one of the column
I choose the data type uniqueidentifier.
How can I add manually a unique ID value in the table value editor?
OK, figured it out: select newID() as the default value.
"Frank" <someone@.nospam.com> wrote in message
news:c5f6j4$dpe$1@.news.mch.sbs.de...
> I created a DB and a table in Visual Studio .NET 2003. For one of the
column
> I choose the data type uniqueidentifier.
> How can I add manually a unique ID value in the table value editor?
>

how to ensure unique value over multiple columns

I'm looking for a way to ensure a value of a column in an insert or update is unique over multiple columns. For example, in this table
accounts
id varchar(32)
readkey varchar(16)
writekey varchar(16)
I want to ensure that when a row of accounts is inserted or updated that the union of all readkey values and writekey values contains no duplicates.

I know some "hard" ways to do this (like a second table of all keys, or a trigger that tests new readkey values against all readkey and writekey values, and likewise new writekey values against all readkey and writekey values, and so on). But I'm betting that savvy SQL folks know a better way. In case it matters, I'm using IBM DB2 8.1.

Thanks
Billcreate a composite key with unique attribute.
not sure if it will work in DB2 though|||Thanks but that doesn't do it. I'm not trying to ensure that no combination of readkey || writekey ever occurs twice. I need to ensure that no readkey is the same as any other readkey or writekey, and no other writekey is the same as any other readkey or writekey.

Bill|||I don't know DB2. In standard SQL you can create a constraint something like:

ALTER TABLE accounts a1
ADD CONSTRAINT c1
CHECK (NOT EXISTS (SELECT NULL FROM accounts a2 WHERE a2.readkey = a1.writekey));

That, along with UNIQUE constraints on the 2 columns, would do it.

Alternatively, perhaps a Materialized View based on:

SELECT 'R' AS mode, readkey AS key FROM accounts
UNION
SELECT 'W' AS mode, writekey AS key FROM accounts

... with a unique constraint on (key).|||Thanks for the suggestions. I couldn't make either work, but the ideas in them provided a way.

The CHECK constraint was my first idea, but I learned that CHECK constraints cannot depend on values from more than one row, so the SELECT which examines the whole table will not work.

The materialized view was a good idea but doesn't work, since materialized views do not permit UNION - the query has to be a subset query.

What I did get to work was to create a (regular) VIEW using the UNION roughly as you suggested, then create triggers for insert and update that throw an error if the key already exists in the union view. The view and two triggers is more complex than I hoped, but at this point I'm happy just to have a solution.

Thanks again for the help.|||In DB2 v8 you can use a sequence object for this purpose.

How to ensure column uniqueness

Hi,
Coming from an Oracle background, I'm used to being able
to create a unique key on a column that allows many null
values, ie if a value exists it must be unique, otherwise
it can be null.
It appears as though SQL Server allows only 1 null value
in the same situation, ie create a unique constraint on a
column and once you try to insert a 2nd row with a null
value I get a constraint violation. [Thx to those that
replied to my last question on this]
I don't really want to write triggers testing for such
conditions. Is there some form of constraint that can do
this for me?
TIA,
SJTYour observations are correct. You can create a view conatining all rows but the NULL. Then create a
unique index (or possible a unique constraint) on that index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"SJT" <scott.taylor@.pwcs.com.au> wrote in message news:054601c36ad9$46dd1f90$a501280a@.phx.gbl...
> Hi,
> Coming from an Oracle background, I'm used to being able
> to create a unique key on a column that allows many null
> values, ie if a value exists it must be unique, otherwise
> it can be null.
> It appears as though SQL Server allows only 1 null value
> in the same situation, ie create a unique constraint on a
> column and once you try to insert a 2nd row with a null
> value I get a constraint violation. [Thx to those that
> replied to my last question on this]
> I don't really want to write triggers testing for such
> conditions. Is there some form of constraint that can do
> this for me?
>
> TIA,
> SJT|||You can use an indexed view to enforce uniqueness only for non-NULL values:
CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)
GO
CREATE VIEW Sometable_Unique_Non_NULL
WITH SCHEMABINDING
AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)
INSERT INTO Sometable VALUES (1,1)
INSERT INTO Sometable VALUES (2,NULL)
INSERT INTO Sometable VALUES (3,NULL)
--
David Portas
--
Please reply only to the newsgroup
--