|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
==again== constraints with nullshttp://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=sqlster&dg=microsoft.public.sqlserver.programming&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us Thanks Aaron and Celko for addressing it yesterday. I am posting the question again with ddl and sample data use testdb go set nocount on go create table mytbl ( pkid int not null identity(1,1) primary key, d1 int not null unique, c1 int null, c2 int null, c3 int null, okay varchar(10) -- indicates whether row is valid ) go insert into mytbl(d1,c1,c2,c3,okay) select 1,11,2,3,'yes' union select 2,21,5,6,'yes' union select 3,23,23,7,'no' union select 4,99,99,99,'no' union select 5,8,7,7,'no' union select 6,null,null,null,'yes' union select 7,null,null,null,'yes' union select 8,9,7,9,'no' union select 9,19,null,null,'yes' union select 10,99,7,null,'yes' go select * from mytbl go /* pkid d1 c1 c2 c3 okay ----------- ----------- ----------- ----------- ----------- ---------- 1 1 11 2 3 yes 2 2 21 5 6 yes 3 3 23 23 7 no 4 4 99 99 99 no 5 5 8 7 7 no 6 6 NULL NULL NULL yes 7 7 NULL NULL NULL yes 8 8 9 7 9 no 9 9 19 NULL NULL yes 10 10 99 7 NULL yes */ drop table mytbl go How could I create constraint such that for a given unique d1; c1,c2,and c3 would be unique and still allow all nulls. Okay column in sample results indicate yes and no for the allowed and disallowed row. I hope this made it clear. Please help me with the constraint and thank you again for addressing it. well this passed your test harness
create table #mytbl ( pkid int not null identity(1,1) primary key, d1 int not null unique, c1 int null, c2 int null, c3 int null, okay varchar(10), -- indicates whether row is valid , check(not((c1=c2)or(c1=c3)or(c2=c3))) ) go insert into #mytbl(d1,c1,c2,c3,okay) select 1,11,2,3,'yes' go insert into #mytbl(d1,c1,c2,c3,okay) select 2,21,5,6,'yes' go insert into #mytbl(d1,c1,c2,c3,okay) select 3,23,23,7,'no' go insert into #mytbl(d1,c1,c2,c3,okay) select 4,99,99,99,'no' go insert into #mytbl(d1,c1,c2,c3,okay) select 5,8,7,7,'no' go insert into #mytbl(d1,c1,c2,c3,okay) select 6,null,null,null,'yes' go insert into #mytbl(d1,c1,c2,c3,okay) select 7,null,null,null,'yes' go insert into #mytbl(d1,c1,c2,c3,okay) select 8,9,7,9,'no' go insert into #mytbl(d1,c1,c2,c3,okay) select 9,19,null,null,'yes' go insert into #mytbl(d1,c1,c2,c3,okay) select 10,99,7,null,'yes' go select * from #mytbl go drop table #mytbl ahh - c1,c2 and c3 cannot equal each other..
are there disallowed values for these columns? (e.g., c1,c2 & c3 cannot be negative)? what you need for this is a check constraint, not a unique constraint. - a unique constraint applies to rows, but you can create a check constraint to apply to columns e.g. (-1 being an "impossible" values) create table mytbl ( pkid int not null identity(1,1) primary key, d1 int not null unique, c1 int null, c2 int null, c3 int null, okay varchar(10), -- indicates whether row is valid CHECK (coalesce(c1,-1) <> coalesce(c2,-2) and coalesce(c2,-1) <> coalesce(c3,-3)) ) sqlster wrote: Show quote > I posted this question yesterday with out good table structure and sample data > > http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=sqlster&dg=microsoft.public.sqlserver.programming&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us > > Thanks Aaron and Celko for addressing it yesterday. > > I am posting the question again with ddl and sample data > > use testdb > go > set nocount on > go > create table mytbl > ( > pkid int not null identity(1,1) primary key, > d1 int not null unique, > c1 int null, > c2 int null, > c3 int null, > okay varchar(10) -- indicates whether row is valid > ) > go > insert into mytbl(d1,c1,c2,c3,okay) > select 1,11,2,3,'yes' union > select 2,21,5,6,'yes' union > select 3,23,23,7,'no' union > select 4,99,99,99,'no' union > select 5,8,7,7,'no' union > select 6,null,null,null,'yes' union > select 7,null,null,null,'yes' union > select 8,9,7,9,'no' union > select 9,19,null,null,'yes' union > select 10,99,7,null,'yes' > > go > select * from mytbl > go > /* > pkid d1 c1 c2 c3 okay > ----------- ----------- ----------- ----------- ----------- ---------- > 1 1 11 2 3 yes > 2 2 21 5 6 yes > 3 3 23 23 7 no > 4 4 99 99 99 no > 5 5 8 7 7 no > 6 6 NULL NULL NULL yes > 7 7 NULL NULL NULL yes > 8 8 9 7 9 no > 9 9 19 NULL NULL yes > 10 10 99 7 NULL yes > */ > drop table mytbl > go > > How could I create constraint such that for a given unique d1; c1,c2,and c3 > would be unique and still allow all nulls. Okay column in sample results > indicate yes and no for the allowed and disallowed row. I hope this made it > clear. > > Please help me with the constraint and thank you again for addressing it. An INSERT or UPDATE is allowed on a table with a CHECK constraint if the
condition evaluates to TRUE or UNKNOWN. The statement NULL = NULL evaluates to UNKNOWN, so multiple NULLs in c1, c2, and c3 would pass the check constraint. CHECK(c1<>c2 AND c1<>c3 AND c2<>c3) For a row with c1, c2, and c3 of 23, 23, and NULL you get CHECK(FALSE AND UNKNOWN AND UNKNOWN) Ternary logic rules state FALSE AND UNKNOWN = FALSE, so the overall CHECK constraint returns FALSE, and the row would not be allowed. If you went with CHECK(NOT(c1=c2 OR c1=c3 OR c2=c3)) The row 23, 23, NULL would go through this logic CHECK(NOT(TRUE OR UNKNOWN OR UNKNOWN)) CHECK(NOT(TRUE)) CHECK(FALSE) Show quote "sqlster" wrote: > I posted this question yesterday with out good table structure and sample data > > http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=sqlster&dg=microsoft.public.sqlserver.programming&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us > > Thanks Aaron and Celko for addressing it yesterday. > > I am posting the question again with ddl and sample data > > use testdb > go > set nocount on > go > create table mytbl > ( > pkid int not null identity(1,1) primary key, > d1 int not null unique, > c1 int null, > c2 int null, > c3 int null, > okay varchar(10) -- indicates whether row is valid > ) > go > insert into mytbl(d1,c1,c2,c3,okay) > select 1,11,2,3,'yes' union > select 2,21,5,6,'yes' union > select 3,23,23,7,'no' union > select 4,99,99,99,'no' union > select 5,8,7,7,'no' union > select 6,null,null,null,'yes' union > select 7,null,null,null,'yes' union > select 8,9,7,9,'no' union > select 9,19,null,null,'yes' union > select 10,99,7,null,'yes' > > go > select * from mytbl > go > /* > pkid d1 c1 c2 c3 okay > ----------- ----------- ----------- ----------- ----------- ---------- > 1 1 11 2 3 yes > 2 2 21 5 6 yes > 3 3 23 23 7 no > 4 4 99 99 99 no > 5 5 8 7 7 no > 6 6 NULL NULL NULL yes > 7 7 NULL NULL NULL yes > 8 8 9 7 9 no > 9 9 19 NULL NULL yes > 10 10 99 7 NULL yes > */ > drop table mytbl > go > > How could I create constraint such that for a given unique d1; c1,c2,and c3 > would be unique and still allow all nulls. Okay column in sample results > indicate yes and no for the allowed and disallowed row. I hope this made it > clear. > > Please help me with the constraint and thank you again for addressing it. > If you went with Mark,> CHECK(NOT(c1=c2 OR c1=c3 OR c2=c3)) > The row 23, 23, NULL would go through this logic I don't see it happening: create table #mytbl ( pkid int not null identity(1,1) primary key, d1 int not null unique, c1 int null, c2 int null, c3 int null, okay varchar(10), -- indicates whether row is valid , check(not((c1=c2)or(c1=c3)or(c2=c3))) ) go insert into #mytbl(d1,c1,c2,c3,okay) select 9,19,19,null,'yes' Server: Msg 547, Level 16, State 1, Line 1 INSERT statement conflicted with TABLE CHECK constraint 'CK__#mytbl__060958A8'. The conflict occurred in database 'tempdb', table '#mytbl______________________________________________________________________________________________________________000000000451'. The statement has been terminated. go select * from #mytbl pkid d1 c1 c2 c3 okay ----------- ----------- ----------- ----------- ----------- ---------- (0 row(s) affected) go drop table #mytbl Sorry, I didn't mean that the row would "go through" or pass the check
constraint. I meant that the logic flow would be as I demonstrated. With the row that you tried inserting, you would check CHECK(NOT((19=19) OR (19=NULL) OR (19=NULL)) CHECK(NOT(TRUE OR UNKNOWN OR UNKNOWN)) CHECK(NOT(TRUE)) CHECK(FALSE) The insert would fail. Show quote "Alexander Kuznetsov" wrote: > > If you went with > > CHECK(NOT(c1=c2 OR c1=c3 OR c2=c3)) > > The row 23, 23, NULL would go through this logic > > Mark, > > I don't see it happening: > > create table #mytbl > ( > pkid int not null identity(1,1) primary key, > d1 int not null unique, > c1 int null, > c2 int null, > c3 int null, > okay varchar(10), -- indicates whether row is valid , > check(not((c1=c2)or(c1=c3)or(c2=c3))) > ) > go > insert into #mytbl(d1,c1,c2,c3,okay) > select 9,19,19,null,'yes' > > Server: Msg 547, Level 16, State 1, Line 1 > INSERT statement conflicted with TABLE CHECK constraint > 'CK__#mytbl__060958A8'. The conflict occurred in database 'tempdb', > table > '#mytbl______________________________________________________________________________________________________________000000000451'. > The statement has been terminated. > > > go > select * from #mytbl > > pkid d1 c1 c2 c3 okay > ----------- ----------- ----------- ----------- ----------- ---------- > > (0 row(s) affected) > > go > drop table #mytbl > > It sounds like what you may need is an ANSI unique constraint. SQL Server
UNIQUE and PRIMARY KEY constraints treat NULLs as a value, and only allow one instance to occur. The following link shows several methods you can use to implement an ANSI unique constraint, which allows duplicate NULL values. My preference is the view method. http://support.microsoft.com/default.aspx?scid=kb;en-us;322002 Show quote "sqlster" <nospam@nospam.com> wrote in message news:7E90E887-E3E5-46FD-A875-6229F7B447CC@microsoft.com... >I posted this question yesterday with out good table structure and sample >data > > http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=sqlster&dg=microsoft.public.sqlserver.programming&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us > > Thanks Aaron and Celko for addressing it yesterday. > > I am posting the question again with ddl and sample data > > use testdb > go > set nocount on > go > create table mytbl > ( > pkid int not null identity(1,1) primary key, > d1 int not null unique, > c1 int null, > c2 int null, > c3 int null, > okay varchar(10) -- indicates whether row is valid > ) > go > insert into mytbl(d1,c1,c2,c3,okay) > select 1,11,2,3,'yes' union > select 2,21,5,6,'yes' union > select 3,23,23,7,'no' union > select 4,99,99,99,'no' union > select 5,8,7,7,'no' union > select 6,null,null,null,'yes' union > select 7,null,null,null,'yes' union > select 8,9,7,9,'no' union > select 9,19,null,null,'yes' union > select 10,99,7,null,'yes' > > go > select * from mytbl > go > /* > pkid d1 c1 c2 c3 okay > ----------- ----------- ----------- ----------- ----------- ---------- > 1 1 11 2 3 yes > 2 2 21 5 6 yes > 3 3 23 23 7 no > 4 4 99 99 99 no > 5 5 8 7 7 no > 6 6 NULL NULL NULL yes > 7 7 NULL NULL NULL yes > 8 8 9 7 9 no > 9 9 19 NULL NULL yes > 10 10 99 7 NULL yes > */ > drop table mytbl > go > > How could I create constraint such that for a given unique d1; c1,c2,and > c3 > would be unique and still allow all nulls. Okay column in sample results > indicate yes and no for the allowed and disallowed row. I hope this made > it > clear. > > Please help me with the constraint and thank you again for addressing it.  |
|||||||||||||||||||||||
Other interesting topics