delete data

Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO

(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?Originally posted by naveen_mehta
Hi,

I have two tables.

CREATE TABLE [one] (
[roleno] [int] NOT NULL ,
[schno] [int] NULL ,
CONSTRAINT [PK_one] PRIMARY KEY CLUSTERED
(
[roleno]
) ON [PRIMARY] ,
CONSTRAINT [FK_one_two] FOREIGN KEY
(
[schno]
) REFERENCES [two] (
[schno]
)
) ON [PRIMARY]
GO

CREATE TABLE [two] (
[roleno] [int] NULL ,
[schno] [int] NOT NULL ,
CONSTRAINT [PK_two] PRIMARY KEY CLUSTERED
(
[schno]
) ON [PRIMARY] ,
CONSTRAINT [FK_two_one] FOREIGN KEY
(
[roleno]
) REFERENCES [one] (
[roleno]
)
) ON [PRIMARY]
GO

(I fact i created Primary & Foreign keys after inserting data in both of these tables.)

I want to delete data from these two tables.
How do i do that...Any Ideas?

ALTER TABLE ONE NOCHECK CONSTRAINT FK_one_two
ALTER TABLE TWO NOCHECK CONSTRAINT FK_two_one
DELETE ONE
DELETE TWO
ALTER TABLE ONE CHECK CONSTRAINT FK_one_two
ALTER TABLE TWO CHECK CONSTRAINT FK_two_one|||That really works...Thanks a ton...|||You could similarly use the alter statements while inserting data if you do not want to check for constraints

Delete across tables

hi,

I have two tables that are related, ie I created them with;

create table cm_message (
msgid varchar(40) not null primary key,
location varchar(240),
ts timestamp default 'now' not null,
lastsent timestamp
);

create table cm_data (
pkey integer not null primary key,
subdata varchar(255),
msgid varchar(40),
foreign key (msgid) references cm_message(msgid)
);

basically for each entry in cm_message there can be several cm_data entries and they're linked using the msgid fields.

I'm trying to write a purge script that will delete entries (in cm_message and cm_data) that have a cm_message.ts timestamp older than n hours. Can I do a 'delete from ... where cm_message.ts > n' which does some kind of union between the two tables and delete entries from both tables at one stroke?

At the moment I'm looking at selecting all old entries from cm_message and deleting all in cm_data for each msgid, but there must be a more efficient way of using the relational stuff...

thanks,
nikUse the ON DELETE CASCADE option:
...foreign key (msgid) references cm_message(msgid) ON DELETE CASCADE :rolleyes:|||which database system is this? because i don't know of any that will support this --timestamp default 'now'|||thanks LKBrwn_DBA.

rudy, the database is firebird - I think it also accepts TODAY, TOMORROW and YESTERDAY which is nice and handy...

nik|||wow, ya learn sumpin new every day ;)

thanks nik|||Hi,

Does it work on a MySQL database?

//M|||Does it work on a MySQL database?the ON DELETE CASCADE? only for InnoDB tables

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
Don
Yes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don
|||Thank you very much
"Dan Guzman" wrote:

> Yes, there is a SET NULL option when the foreign key constraint is created.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
>
>

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
DonYes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don|||Thank you very much
"Dan Guzman" wrote:

> Yes, there is a SET NULL option when the foreign key constraint is created
.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
>
>

Delclarative RI in SQL Server 2005

Does anyone know if in SQL Server 2005 declarative RI can be used to NULL a
referenced field on delete of a record from the parent table?
Any help is appreciated.
DonYes, there is a SET NULL option when the foreign key constraint is created.
The foreign key column(s) must of course be nullable.
--
Happy Holidays
Dan Guzman
SQL Server MVP
"Don" <Don@.discussions.microsoft.com> wrote in message
news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> a
> referenced field on delete of a record from the parent table?
> Any help is appreciated.
> Don|||Thank you very much
"Dan Guzman" wrote:
> Yes, there is a SET NULL option when the foreign key constraint is created.
> The foreign key column(s) must of course be nullable.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:FC073BAB-8767-44C7-9FDC-CACF6E66AC84@.microsoft.com...
> > Does anyone know if in SQL Server 2005 declarative RI can be used to NULL
> > a
> > referenced field on delete of a record from the parent table?
> >
> > Any help is appreciated.
> >
> > Don
>
>

Defining week ending date.

Hello,
I have a w ending date question. Here is my table (just for demo
purposes)
CREATE TABLE [dbo].[TestTable] (
[userID] [varchar] (10) NULL ,
[u_key] [int] NULL ,
[TS] [datetime] NULL
) ON [PRIMARY]
GO
insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
'// This is the query I run
Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
From testtable a
Group by a.u_key, DATEPART(ww,a.ts)
'// I get this Result set, which is exactly what I want.
u_key Period Counter
2 28 2
3 28 1
2 30 1
4 30 1
2 31 2
2 32 3
I am assuming that using the DatePart(ww..) automagically makes the
wending a Saturday. Now, my client wants to change the w ending to
Thursday (or whatever). I have no idea how I would change the query. I
most definitely need to have the period number returned as part of the
select clause.
Thanks for all your help.
-JackJack,
1. Don't use DATEPART to calculate w number if you want to calculate acco
rding to the ISO
standard (where this w is w 38). SQL Server DATEPART considers this we
ek to be w number 39.
If you want to calculate according to ISO, install the ISOWEEK function whic
h you find in Books
Online.
2. Use SET DATEFIRST con set first day of w. I think ISOWEEK respects thi
s setting, but test just
to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <jack@.jack.net> wrote in message news:9RWXe.39021$Cc5.3100@.lakeread06...ed">
> Hello,
> I have a w ending date question. Here is my table (just for demo purpo
ses)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the wen
ding a Saturday. Now,
> my client wants to change the w ending to Thursday (or whatever). I ha
ve no idea how I would
> change the query. I most definitely need to have the period number return
ed as part of the select
> clause.
> Thanks for all your help.
> -Jack
>|||You could use a calendar table, you'd have to define the ws yourself, but
it gives you complete flexibility (and you only have to do it once).
http://www.aspfaq.com/2519
"Jack" <jack@.jack.net> wrote in message
news:9RWXe.39021$Cc5.3100@.lakeread06...
> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>|||The DATEFIRST setting specifies the first day of the w.
SET DATEFIRST sets the first day and @.@.DATEFIRST returns the current setting
So...
SELECT CASE @.@.DATEFIRST
WHEN 1 THEN 7
ELSE @.@.DATEFIRST -1
END AS last_day_of_w
"Jack" wrote:

> Hello,
> I have a w ending date question. Here is my table (just for demo
> purposes)
> CREATE TABLE [dbo].[TestTable] (
> [userID] [varchar] (10) NULL ,
> [u_key] [int] NULL ,
> [TS] [datetime] NULL
> ) ON [PRIMARY]
> GO
> insert into testTable values ('a', 3, '7/9/2005 6:12:59 PM')
> insert into testTable values ('b', 2, '7/9/2005 6:13:35 PM')
> insert into testTable values ('d', 2, '7/9/2005 6:14:07 PM')
> insert into testTable values ('d', 2, '7/22/2005 11:26:08 AM')
> insert into testTable values ('d', 4, '7/22/2005 11:26:08 AM')
> insert into testTable values ('e', 2, '7/27/2005 1:27:18 PM')
> insert into testTable values ('f', 2, '7/27/2005 5:21:36 PM')
> insert into testTable values ('a', 2, '8/1/2005 12:02:02 PM')
> insert into testTable values ('b', 2, '8/1/2005 12:02:05 PM')
> insert into testTable values ('c', 2, '8/1/2005 3:49:16 PM')
> '// This is the query I run
> Select a.u_key, DATEPART(ww,a.ts) as Period, count(*) as Counter
> From testtable a
> Group by a.u_key, DATEPART(ww,a.ts)
> '// I get this Result set, which is exactly what I want.
> u_key Period Counter
> 2 28 2
> 3 28 1
> 2 30 1
> 4 30 1
> 2 31 2
> 2 32 3
> I am assuming that using the DatePart(ww..) automagically makes the
> wending a Saturday. Now, my client wants to change the w ending to
> Thursday (or whatever). I have no idea how I would change the query. I
> most definitely need to have the period number returned as part of the
> select clause.
> Thanks for all your help.
> -Jack
>
>

DefaultValue

Hi all,
I have a table like
Tablename: Test
tst_id uniqueidentifier Not null PK
= RowGuid
tst_ts2_id uniqueidentifier Not null
Default '?
tst_code Varchar 50 Not null
And Another table
Tablename: Test2
ts2_id uniqueidentifier Not null PK
ts2_name Varchar 50 Not null
ts2_default Boolean False
there is defined a FK tst_ts2_id and ts2_id
I would to insert into table Test a row with the folloing insert
INSERT INTO test
(tst_code)
VALUES ('123456')
Now I would, that there is a trigger, or something else, that inserts me an
tst_ts2_id the ID from Table Test2, where ts2_default = True.
How to du. I tryed with trigger, but it seams, that the trigger fires to
late, if I delete the FK, then it will be inserted.
I wrote a function that returns the Id to insert, but is it possible, that
like default-value can be insertted the result of a skalar function ?
Thank you all
Klaus AstnerI'm not certain if this is what you are trying to do and as you did not post
DDL or the statements or triggers that you tried I made some guesses, but
the following worked fine for me:
create table Test2 (
ts2_id uniqueidentifier Not null primary key default(newid()),
ts2_name Varchar(50) Not null,
ts2_default bit default(0)
)
go
create function dbo.def_ts2id() returns uniqueidentifier
as
begin
declare @.guid uniqueidentifier
select @.guid = ts2_id from test2 where ts2_default = 1
return @.guid
end
go
create table Test (
tst_id uniqueidentifier Not null primary key default(newid()),
tst_ts2_id uniqueidentifier Not null foreign key references test2(ts2_id)
default(dbo.def_ts2id()),
tst_code Varchar(50) Not null
)
go
insert into test2(ts2_name,ts2_default) values('default',1)
go
INSERT INTO test(tst_code) VALUES('123456')
go
select * from test
select * from test2
go
Result:
tst_id tst_ts2_id
tst_code
-- -- --
---
18CBDA2A-6B29-43C9-8A42-B86B9102E6D4 70DC14A0-F744-4006-8CE0-A9C8695D8E83
123456
ts2_id ts2_name
ts2_default
-- ---
-- --
70DC14A0-F744-4006-8CE0-A9C8695D8E83 default
1
Craig
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Astner Klaus" <k.astnerremove_this@.virgilio.it> wrote in message
news:eFHHSRPXDHA.2648@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>
> I have a table like
> Tablename: Test
> tst_id uniqueidentifier Not null PK
> = RowGuid
> tst_ts2_id uniqueidentifier Not null
> Default '?
> tst_code Varchar 50 Not null
>
> And Another table
> Tablename: Test2
> ts2_id uniqueidentifier Not null PK
> ts2_name Varchar 50 Not null
> ts2_default Boolean False
>
> there is defined a FK tst_ts2_id and ts2_id
>
> I would to insert into table Test a row with the folloing insert
>
> INSERT INTO test
> (tst_code)
> VALUES ('123456')
> Now I would, that there is a trigger, or something else, that inserts me
an
> tst_ts2_id the ID from Table Test2, where ts2_default = True.
>
> How to du. I tryed with trigger, but it seams, that the trigger fires to
> late, if I delete the FK, then it will be inserted.
> I wrote a function that returns the Id to insert, but is it possible, that
> like default-value can be insertted the result of a skalar function ?
>
> Thank you all
>
> Klaus Astner
>
>
>

Default Values in Queries

Hello, I'm trying not to have NULL values in columns.
So I defined a default value ('n.d.') on a column named DDT.
How can i retrieve the rows with the default value in DDT column ?
I mean:
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is [DDT Column DEFAULT]
instead of :
select OrderID, DateOfOrder
from
OrderTable
where
CustomerID=@.CustID
and
DDT is NULL (or DDT='n.d.')
How does the query change if i define a Default Value DF_NULL char(4) = 'n.d
.'
and assign the defalut value of DDT column to DF_NULL ?
Thank you for help.
MicheleMichele wrote:
> Hello, I'm trying not to have NULL values in columns.
So, have you set the column to "NOT NULL"? That's the best way to prevent
NULLS from being stored in the column ...

> So I defined a default value ('n.d.') on a column named DDT.
> How can i retrieve the rows with the default value in DDT column ?
> I mean:
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is [DDT Column DEFAULT]
> instead of :
> select OrderID, DateOfOrder
> from
> OrderTable
> where
> CustomerID=@.CustID
> and
> DDT is NULL (or DDT='n.d.')
> How does the query change if i define a Default Value DF_NULL char(4)
> = 'n.d.' and assign the defalut value of DDT column to DF_NULL ?
> Thank you for help.
> Michele
If you set the DDT column to "NOT NULL", then it will never contain NULL.
Are you saying that you won't know at runtime what the default value for DDT
is? Why wouldn't you just use:
WHERE DDT='n.d.'
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||> So, have you set the column to "NOT NULL"? That's the best way to prevent
> NULLS from being stored in the column ...
Yes the column DDT is set to NOT NULL.

> Are you saying that you won't know at runtime what the default value for D
DT
> is?
Yes.

>Why wouldn't you just use:
> WHERE DDT='n.d.'
Because for some NOT NULL columns the default is 'n.d.' for others is
'<unknown>' for others '000000', so I'd like to treat the default value of
the column as a parameter for the query (if possible), like a ... where DDT
is NULL (in case of DDT column NULL, but this is not the case)
Thank's.|||Michele wrote:
> Yes the column DDT is set to NOT NULL.
>
> Yes.
>
> Because for some NOT NULL columns the default is 'n.d.' for others is
> '<unknown>' for others '000000', so I'd like to treat the default
> value of the column as a parameter for the query (if possible), like
> a ... where DDT is NULL (in case of DDT column NULL, but this is not
> the case)
>
I've never attempted to do this (I always know what the default values are
in my columns ... <g,d&r> )
I suppose you could create a scaler udf that uses the sp_columns procedure,
or queries the INFORMATION_SCHEMA.Columns table, to retrieve the column's
default value.
WHERE DDT=fColDefault(DDT)
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Default value: ISNULL()

Hi!

I'm wondering whether it's possible to set up the MS SQL function
ISNULL() as a default value to avoid NULL entries when importing data
into a table?!

For example, I want the column1, to have a 0 (zero) as default value,
when entering/importing data: isnull("column1",0)

I remember that it is possible to set up with a date function like
now(), having for each record the current time as default value. Is
that also with isnull() somehow possible?

Thx a lot!
PeterYou can create a default constraint to specify a default value for a column.
For example:

CREATE TABLE MyTable
(
Col1 int NOT NULL,
Col2 int NULL
CONSTRAINT DF_MyTable_Col1 DEFAULT 0
)
GO
INSERT INTO MyTable (Col1) VALUES(1)
SELECT * FROM MyTable
GO

However, an explicit NULL will override the default constraint value:

INSERT INTO MyTable (Col1, Col2) VALUES(2, NULL)
SELECT * FROM MyTable
GO

If you need to import data containing a mix of nulls and not nulls, you have
options depending on your data source and import tool. In the case of a
query, you could use ISNULL or COALESCE to specify the desired value when
NULL. With DTS, a column transformation could do the job.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Peter Neumaier" <Peter.Neumaier@.gmail.com> wrote in message
news:1138013760.433105.245790@.g43g2000cwa.googlegr oups.com...
> Hi!
> I'm wondering whether it's possible to set up the MS SQL function
> ISNULL() as a default value to avoid NULL entries when importing data
> into a table?!
> For example, I want the column1, to have a 0 (zero) as default value,
> when entering/importing data: isnull("column1",0)
> I remember that it is possible to set up with a date function like
> now(), having for each record the current time as default value. Is
> that also with isnull() somehow possible?
> Thx a lot!
> Peter

default value on quantity and price field

should I set to "0" as a default value on a quantity and a price field
or set to "null"?
if a user enter nothing on the quantity or price field on a web
browser, should I treat it as null or "0"? I am so confused about this
concept. please advise me. thank you.HandersonVA wrote:
> should I set to "0" as a default value on a quantity and a price field
> or set to "null"?
> if a user enter nothing on the quantity or price field on a web
> browser, should I treat it as null or "0"? I am so confused about this
> concept. please advise me. thank you.

I would do the following :
1) Set quantity and price to "not null"
2) Put 0 when there is no value, instead of NULL.

This way,
a) if you use aggrate function like AVG, thoses functions will use
everyrow
b) non unique index on thoses fields will contained every rows
c) if you query thoses field (where quantit=0) rows will come out. IF
quantity is null and you query thoses rows, youll need (where
quantity=0 or quantity is null)...
etc.|||francois.bourdages@.harfan.com wrote:
> HandersonVA wrote:
> > should I set to "0" as a default value on a quantity and a price field
> > or set to "null"?
> > if a user enter nothing on the quantity or price field on a web
> > browser, should I treat it as null or "0"? I am so confused about this
> > concept. please advise me. thank you.

Here is a link to an oracle document (outch), that explain some
problems with null...
http://www.oracle.com/technology/or...jul/o45sql.html|||HandersonVA (handersonva@.hotmail.com) writes:
> should I set to "0" as a default value on a quantity and a price field
> or set to "null"?
> if a user enter nothing on the quantity or price field on a web
> browser, should I treat it as null or "0"? I am so confused about this
> concept. please advise me. thank you.

That depends on the business rules. If not entering any price, means
"this item is for free", yes the 0 is the right thing. If not entering
any price means "I don't know", you should use NULL.

The same goes for Quantity. If this is an inventory 0 probably means "out
of stock". Which could be a good default. Then again, it's probably better
to have the user to state that explicitly.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 1 Jun 2006 09:41:52 -0700, HandersonVA wrote:

>should I set to "0" as a default value on a quantity and a price field
>or set to "null"?
>if a user enter nothing on the quantity or price field on a web
>browser, should I treat it as null or "0"? I am so confused about this
>concept. please advise me. thank you.

Hi HandersonVA,

There's a very big difference between 0 and NULL.

Suppose you're in a restaurant and the menu lists the price for some
dishes as $0.00. You'd probably order it right away, and tell all your
friend that yoou've foound a place with free food. But I'm pretty sure
that you wouldn't do the same is the price for some (or all) dishes was
simply omitted from the list.

In a database, NULL represents the price on a menu without price list.
NULL is specifically designed to represent the fact that no data is
available for a specific column in a specific row in the table.

Alowing data to be missing in a database introduces some interesting
problems. I won't describe them here (but I skimmed the article Francois
linked to, and on first glance it appears to be a good start on the
subject), but they are the reason that the common advise is to avoid
missing data whenever possible (or, to state it very shortly: avoid
NULLS). Of course, the real world sometimes confronts us with situations
where part of the data IS missing, so we can't always avoid it.

Unfortunately, some people have taken the "avoid NULLS" advise way too
litteraly - I have seen people avoiding NULL, but using some other
"magic value" to represent missing data. And believe me - that only
results in more problems, not in less!!
(Quick example - the average of {8.5, 9.5, NULL} would be calculated as
9.0 - not exactly right [since one of the input values is missing, the
only correct answer would be that it's impossible to calculate the
average], but it is at least the real average of the values that are
present in the database. But use 0 as "magic value" to represent missing
data, and you're calculating the average of {8.5, 9.5, 0}, which yields
6.0 - and that''s just plain silly!)

After this explanation about NULLs, let's get back to your question. A
default should be either a commonly used value (eg countrycode USA when
doing business in the USA), or a "safe" value (eg no automatic unlimited
bidding on an auctioning site).

For quantity, the value 1 is quite common in many industries. It's also
safe. So I'd recommend setting the default quantity to 1. Unless you're
in an industry that usually orders large quantities at once - in that
case, either choose a better default that fits the business or don't use
a default at all. I would definitely NOT use 0 as default quantity, as
an order for a quantity of 0 items is pointless (and shouldd in fact be
rejected by a CHECK constraint).

For price, there is no goood default value in most industries, so I
would not create a default for the column at all. Define the column as
NOT NULL (to force the user to enter a price) or, if the business has to
deal with orders before a price is known, allow NULLs and don't set up a
default - the price will remain NULL until one is explicitly entered.
Make sure you handle the missing information adequately and set up
constraints to ensure that the price must be known once the order passes
the stage where the prices should be known according to the industry's
business rules.

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> Suppose you're in a restaurant and the menu lists the price for some
> dishes as $0.00. You'd probably order it right away, and tell all your
> friend that yoou've foound a place with free food. But I'm pretty sure
> that you wouldn't do the same is the price for some (or all) dishes was
> simply omitted from the list.

That usually means that it's very expensive!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

default value not function

I have set a column A in table test with default value = 0 (money data type)
and not allow null, but when I run my application and want to save the
record, there is an error like ...cannot
insert value Null into column A table test, column does not allow null,
insert fail..., why the default value 0 not function (why there is null
insert?, not 0 insert)
Hi
The default will only be used if you don't specify a value for the column or
if you use the default keyword.
e.g.
CREATE TABLE MyTest ( id int not null ,
val char(1) not null default 'A' )
INSERT INTO MyTest ( id ) VALUES ( 1 )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 2, 'C' )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 3, DEFAULT )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 4, NULL )
/*
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'val', table 'tempdb.dbo.MyTest';
column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
INSERT INTO MyTest ( id, val ) VALUES ( 4, ISNULL(NULL,'A') )
--(1 row(s) affected)
SELECT * FROM MyTest
/*
id val
-- --
1 A
2 C
3 A
4 A
(4 row(s) affected)
*/
John
"tong" wrote:

> I have set a column A in table test with default value = 0 (money data type)
> and not allow null, but when I run my application and want to save the
> record, there is an error like ...cannot
> insert value Null into column A table test, column does not allow null,
> insert fail..., why the default value 0 not function (why there is null
> insert?, not 0 insert)
>

default value not function

I have set a column A in table test with default value = 0 (money data type)
and not allow null, but when I run my application and want to save the
record, there is an error like ...cannot
insert value Null into column A table test, column does not allow null,
insert fail..., why the default value 0 not function (why there is null
insert', not 0 insert)Hi
The default will only be used if you don't specify a value for the column or
if you use the default keyword.
e.g.
CREATE TABLE MyTest ( id int not null ,
val char(1) not null default 'A' )
INSERT INTO MyTest ( id ) VALUES ( 1 )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 2, 'C' )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 3, DEFAULT )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 4, NULL )
/*
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'val', table 'tempdb.dbo.MyTest';
column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
INSERT INTO MyTest ( id, val ) VALUES ( 4, ISNULL(NULL,'A') )
--(1 row(s) affected)
SELECT * FROM MyTest
/*
id val
-- --
1 A
2 C
3 A
4 A
(4 row(s) affected)
*/
John
"tong" wrote:
> I have set a column A in table test with default value = 0 (money data type)
> and not allow null, but when I run my application and want to save the
> record, there is an error like ...cannot
> insert value Null into column A table test, column does not allow null,
> insert fail..., why the default value 0 not function (why there is null
> insert', not 0 insert)
>

default value not function

I have set a column A in table test with default value = 0 (money data type)
and not allow null, but when I run my application and want to save the
record, there is an error like ...cannot
insert value Null into column A table test, column does not allow null,
insert fail..., why the default value 0 not function (why there is null
insert', not 0 insert)Hi
The default will only be used if you don't specify a value for the column or
if you use the default keyword.
e.g.
CREATE TABLE MyTest ( id int not null ,
val char(1) not null default 'A' )
INSERT INTO MyTest ( id ) VALUES ( 1 )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 2, 'C' )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 3, DEFAULT )
-- (1 row(s) affected)
INSERT INTO MyTest ( id, val ) VALUES ( 4, NULL )
/*
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'val', table 'tempdb.dbo.MyTest';
column does not allow nulls. INSERT fails.
The statement has been terminated.
*/
INSERT INTO MyTest ( id, val ) VALUES ( 4, ISNULL(NULL,'A') )
--(1 row(s) affected)
SELECT * FROM MyTest
/*
id val
-- --
1 A
2 C
3 A
4 A
(4 row(s) affected)
*/
John
"tong" wrote:

> I have set a column A in table test with default value = 0 (money data typ
e)
> and not allow null, but when I run my application and want to save the
> record, there is an error like ...cannot
> insert value Null into column A table test, column does not allow null,
> insert fail..., why the default value 0 not function (why there is null
> insert', not 0 insert)
>

default value after replication

Hi all,
I have problem when conducting the replication.
Some tables in subscriber side contain default values and null values
respectively. After replication, all these fields are blank.
Is there any setting to keep the default and null values repectively after
replication?
Thanks a lot!!
If this is transactional replication I suspect you will need to modify the
replication stored procedures to obtain the behaviors you are looking for.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andrew" <andrew@.iplr.com.hk> wrote in message
news:uX0zZMxVGHA.3760@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have problem when conducting the replication.
> Some tables in subscriber side contain default values and null values
> respectively. After replication, all these fields are blank.
> Is there any setting to keep the default and null values repectively after
> replication?
> Thanks a lot!!
>
|||Andrew,
have a look at the @.schema_option argument for sp_addarticle, which has a
value for defaults: 0x800.
AFAIR if you select the checkboxes on the article properties, you will end
up with the defaults added, even though it isn't explicitly mentioned.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul,
You're so great! It works now.
Thanks for you help again!!
Cheers!
Andrew
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ebsqhbyVGHA.5064@.TK2MSFTNGP10.phx.gbl...
> Andrew,
> have a look at the @.schema_option argument for sp_addarticle, which has a
> value for defaults: 0x800.
> AFAIR if you select the checkboxes on the article properties, you will end
> up with the defaults added, even though it isn't explicitly mentioned.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Default Value

Hi!
I have the following sp:
ALTER PROCEDURE dbo.[Buscar Clientes]
(
@.Begin As DateTime = Null,
@.End As DateTime = Null
)
In VS 2003, If i dont enter any value in the parameters, shouldnt both be
Null?
If i dont enter any value for the parameters i get the following error when
i use the Fill method: "String was not recognized as a valid datetime"
When i look into the Parameters in my DataAdpter, the value field is empty.
Can anyboy help me out?
Thanks,
BRuno N> In VS 2003, If i dont enter any value in the parameters, shouldnt both be
> Null?
YEs, they are NULL as far as T-SQL is concerned.

> If i dont enter any value for the parameters i get the following error
when
> i use the Fill method: "String was not recognized as a valid datetime"
I assume this stored procedure then returns a resultset, and this is what
you are using for Fill. Your C# or VB.Net code needs to deal with DBNull...
or, you need to change your SELECT statement so that IT deals with the NULL
values appropriately.
In other words, your error is not because your date is NULL within SQL
Server, your error is because your .NET code does not deal with it
correctly.|||Thank you!
I will look into my code
"Bruno N" <nylren@.hotmail.com> escreveu na mensagem
news:%23Z5g3pkLFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I have the following sp:
> ALTER PROCEDURE dbo.[Buscar Clientes]
> (
> @.Begin As DateTime = Null,
> @.End As DateTime = Null
> )
> In VS 2003, If i dont enter any value in the parameters, shouldnt both be
> Null?
> If i dont enter any value for the parameters i get the following error
> when i use the Fill method: "String was not recognized as a valid
> datetime"
> When i look into the Parameters in my DataAdpter, the value field is
> empty.
>
> Can anyboy help me out?
> Thanks,
> BRuno N
>|||Bruno,
You can assign DBNull.Value to the parameter value. You can also not append
the parameters to the collection at all, ADO.NET use named parameters when
using sqlClient data provider.
AMB
"Bruno N" wrote:

> Thank you!
> I will look into my code
> "Bruno N" <nylren@.hotmail.com> escreveu na mensagem
> news:%23Z5g3pkLFHA.2604@.TK2MSFTNGP10.phx.gbl...
>
>