Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 10:12:13
|
My table as follow,CREATE TABLE DERPosi_201001( [TID] int primary key identity(1,1) not null, [SeatN] [numeric](15, 0) NOT NULL, [Posi] [varchar](30) NOT NULL, [recGrp] [datetime] not null, Constraint ck_recGrp_201001 CHECK ( recGrp BETWEEN '1/1/2010' and '1/31/2010' ))CREATE TABLE DERPosi_201002( [TID] int primary key identity(1,1) not null, [SeatN] [numeric](15, 0) NOT NULL, [Posi] [varchar](30) NOT NULL, [recGrp] [datetime] not null, Constraint ck_recGrp_201002 CHECK ( recGrp BETWEEN '2/1/2010' and '2/31/2010' ))CREATE TABLE DERPosi_201003( [TID] int primary key identity(1,1) not null, [SeatN] [numeric](15, 0) NOT NULL, [Posi] [varchar](30) NOT NULL, [recGrp] [datetime] not null, Constraint ck_recGrp_201003 CHECK ( recGrp BETWEEN '3/1/2010' and '3/31/2010' ))I tried to create Partitioned Views as follow,create view DERPosi with schemabindingas select TID,SeatN,Posi,recGrp from DERPosi_201001 union all select TID,SeatN,Posi,recGrp from DERPosi_201002 union all select TID,SeatN,Posi,recGrp from DERPosi_201003;Unfortunately, error was return as follow,Cannot schema bind view 'DERPosi' because name 'DERPosi_201001' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.I'm stuck |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 11:02:04
|
try using schema names for tables as well as view likecreate view schemaname.DERPosi with schemabindingasselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201001union allselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201002union allselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201003; |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 11:49:30
|
quote: Originally posted by visakh16 try using schema names for tables as well as view likecreate view schemaname.DERPosi with schemabindingasselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201001union allselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201002union allselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201003;
It return erros as follow,Invalid object name 'schemaname.DERPosi_201001'.why schemaname.DERPosi_201001 not only DERPosi_201010but below will work,create view dbo.DERPosi with schemabindingasselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201001union allselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201002union allselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201003; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 11:52:16
|
quote: Originally posted by Delinda
quote: Originally posted by visakh16 try using schema names for tables as well as view likecreate view schemaname.DERPosi with schemabindingasselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201001union allselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201002union allselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201003;
It return erros as follow,Invalid object name 'schemaname.DERPosi_201001'.why schemaname.DERPosi_201001 not only DERPosi_201010
you should put your actual schema name not the text schemaname |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 11:56:00
|
quote: Originally posted by visakh16
quote: Originally posted by Delinda
quote: Originally posted by visakh16 try using schema names for tables as well as view likecreate view schemaname.DERPosi with schemabindingasselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201001union allselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201002union allselect TID,SeatN,Posi,recGrp from schemaname.DERPosi_201003;
It return erros as follow,Invalid object name 'schemaname.DERPosi_201001'.why schemaname.DERPosi_201001 not only DERPosi_201010
you should put your actual schema name not the text schemaname
me tried,create view dbo.DERPosi with schemabindingasselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201001union allselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201002union allselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201003;it's work.So, that all? Now, my DERPosi is Partitioned Views? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 11:57:36
|
seehttp://msdn.microsoft.com/en-us/library/aa933141(SQL.80).aspx |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 12:07:15
|
quote: Originally posted by visakh16 seehttp://msdn.microsoft.com/en-us/library/aa933141(SQL.80).aspx
Me tried run as follow,insert into dbo.DERPosi(SeatN,Posi,recGrp) values(23,'010','1/27/2010')insert into dbo.DERPosi(SeatN,Posi,recGrp) values(24,'10','3/27/2010')it return error,Msg 4436, Level 16, State 12, Line 1UNION ALL view 'ETICKETING.dbo.DERPosi' is not updatable because a partitioning column was not found.Msg 4436, Level 16, State 12, Line 2UNION ALL view 'ETICKETING.dbo.DERPosi' is not updatable because a partitioning column was not found.I'm using SQL Server 2005 Standard Edition |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 12:11:51
|
did you read part under "Partitioning Column Rules"? |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 12:13:44
|
quote: Originally posted by visakh16 did you read part under "Partitioning Column Rules"?
Yes. But i can't digest the logic, and fail to implement. Especially,Partitioning columns must be a part of the primary key of the table. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 12:22:18
|
quote: Originally posted by Delinda
quote: Originally posted by visakh16 did you read part under "Partitioning Column Rules"?
Yes. But i can't digest the logic, and fail to implement. Especially,Partitioning columns must be a part of the primary key of the table.
Partition column is column whose values are base for division into different tables. they will have check constraint to ensure values are in allowable range |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 12:28:52
|
quote: Originally posted by visakh16
quote: Originally posted by Delinda
quote: Originally posted by visakh16 did you read part under "Partitioning Column Rules"?
Yes. But i can't digest the logic, and fail to implement. Especially,Partitioning columns must be a part of the primary key of the table.
Partition column is column whose values are base for division into different tables. they will have check constraint to ensure values are in allowable range
My table as follow,CREATE TABLE DERPosi_201001([TID] int primary key identity(1,1) not null,[SeatN] [numeric](15, 0) NOT NULL,[Posi] [varchar](30) NOT NULL,[recGrp] [datetime] not null,Constraint ck_recGrp_201001 CHECK (recGrp BETWEEN '1/1/2010' and '1/31/2010'))CREATE TABLE DERPosi_201002([TID] int primary key identity(1,1) not null,[SeatN] [numeric](15, 0) NOT NULL,[Posi] [varchar](30) NOT NULL,[recGrp] [datetime] not null,Constraint ck_recGrp_201002 CHECK (recGrp BETWEEN '2/1/2010' and '2/31/2010'))CREATE TABLE DERPosi_201003([TID] int primary key identity(1,1) not null,[SeatN] [numeric](15, 0) NOT NULL,[Posi] [varchar](30) NOT NULL,[recGrp] [datetime] not null,Constraint ck_recGrp_201003 CHECK (recGrp BETWEEN '3/1/2010' and '3/31/2010'))You can see, 1. My primary is TID2. my partion column is recGrpMy Partitioned Views as follow,create view [dbo].[DERPosi] with schemabindingasselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201001union allselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201002union allselect TID,SeatN,Posi,recGrp from dbo.DERPosi_201003;It return error when i executeinsert into dbo.DERPosi(SeatN,Posi,recGrp) values(23,'010','1/27/2010')insert into dbo.DERPosi(SeatN,Posi,recGrp) values(24,'10','3/27/2010')Did I missing something? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 12:32:41
|
where are check constraints on partition column? also it should be part of primary key of table |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 12:37:30
|
quote: Originally posted by visakh16 where are check constraints on partition column? also it should be part of primary key of table
For DERPosi_201001, check constraint is Constraint ck_recGrp_201001 CHECK (recGrp BETWEEN '1/1/2010' and '1/31/2010'For DERPosi_201002, check constraint is Constraint ck_recGrp_201002 CHECK (recGrp BETWEEN '2/1/2010' and '2/31/2010'For DERPosi_201003, check constraint is Constraint ck_recGrp_201003 CHECK (recGrp BETWEEN '3/1/2010' and '3/31/2010'If me set recGrp is a primary key, how come i can insert data with same recGrp. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-27 : 12:39:35
|
quote: Originally posted by Delinda
quote: Originally posted by visakh16 where are check constraints on partition column? also it should be part of primary key of table
For DERPosi_201001, check constraint is Constraint ck_recGrp_201001 CHECK (recGrp BETWEEN '1/1/2010' and '1/31/2010'For DERPosi_201002, check constraint is Constraint ck_recGrp_201002 CHECK (recGrp BETWEEN '2/1/2010' and '2/31/2010'For DERPosi_201003, check constraint is Constraint ck_recGrp_201003 CHECK (recGrp BETWEEN '3/1/2010' and '3/31/2010'If me set recGrp is a primary key, how come i can insert data with same recGrp.
you can make a composite primary key involving date field also |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 12:51:00
|
quote: Originally posted by visakh16
quote: Originally posted by Delinda
quote: Originally posted by visakh16 where are check constraints on partition column? also it should be part of primary key of table
For DERPosi_201001, check constraint is Constraint ck_recGrp_201001 CHECK (recGrp BETWEEN '1/1/2010' and '1/31/2010'For DERPosi_201002, check constraint is Constraint ck_recGrp_201002 CHECK (recGrp BETWEEN '2/1/2010' and '2/31/2010'For DERPosi_201003, check constraint is Constraint ck_recGrp_201003 CHECK (recGrp BETWEEN '3/1/2010' and '3/31/2010'If me set recGrp is a primary key, how come i can insert data with same recGrp.
you can make a composite primary key involving date field also
My composite primary key as follow,alter table DERPosi_201001add primary key(TID,recGrp)alter table DERPosi_201002add primary key(TID,recGrp)alter table DERPosi_201003add primary key(TID,recGrp)Once me run,insert into dbo.DERPosi(SeatN,Posi,recGrp) values(23,'010','1/27/2010')insert into dbo.DERPosi(SeatN,Posi,recGrp) values(24,'10','3/27/2010')it return errors,Msg 4436, Level 16, State 12, Line 1UNION ALL view 'ETICKETING.dbo.DERPosi' is not updatable because a partitioning column was not found.Msg 4436, Level 16, State 12, Line 2UNION ALL view 'ETICKETING.dbo.DERPosi' is not updatable because a partitioning column was not found. |
|
|
Delinda
Constraint Violating Yak Guru
315 Posts |
Posted - 2010-01-27 : 13:14:29
|
1. I removed TID as identity2. Me make it TID and recGrp as Composite Primary KeyAs a result, me executeinsert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(1,23,'010','1/27/2010')insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(10,24,'10','3/27/2010')The result was success ... tq Mr Visakh. Your guide, is my inspiration |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 14:13:00
|
[code]...[recGrp] [datetime] not null,Constraint ck_recGrp_201002 CHECK (recGrp BETWEEN '2/1/2010' and '2/31/2010'...[/code]All months now have 31 days? I'm surprised SQL didn't error on that ... |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-27 : 15:03:27
|
It does error out, just not when the table is created...Create Table #testConstraint ( id int identity primary key, somecol datetime, Constraint testing CHECK (somecol BETWEEN '2010/02/01' and '2010/02/31)) Command completed successfully insert into #testConstraint (somecol) values ('2010/02/20') -- perfectly valid datetime value Msg 242, Level 16, State 3, Line 1The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.The statement has been terminated.The constraint only gets executed when a row is inserted/updated.--Gail ShawSQL Server MVP |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 15:59:56
|
Thanks for that Gail. Something for the O/P to fix ... I hope they aren't used to using MySQL which, I believe, allows dates with any number of days in a month (although Gotchas like that remain in circulation long after they are fixed, so I may be maligning MySQL ...) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-01-28 : 03:09:57
|
quote: Originally posted by Delinda 1. I removed TID as identity2. Me make it TID and recGrp as Composite Primary KeyAs a result, me executeinsert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(1,23,'010','1/27/2010')insert into dbo.DERPosi(TID,SeatN,Posi,recGrp) values(10,24,'10','3/27/2010')The result was success ... tq Mr Visakh. Your guide, is my inspiration
welcome |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-01-28 : 04:11:06
|
quote: Originally posted by KristenI hope they aren't used to using MySQL which, I believe, allows dates with any number of days in a month
And the 0th day of the month.When I worked with MySQL (4 I think) there was a note in the docs saying that this was by design and that it was not the DB's responsibility to validate data.However now...quote: Starting from MySQL 5.0.2, MySQL gives warnings or errors if you try to insert an illegal date. By setting the SQL mode to the appropriate value, you can specify more exactly what kind of dates you want MySQL to support. (See Section 5.1.7, “Server SQL Modes”.) You can get MySQL to accept certain dates, such as '2009-11-31', by using the ALLOW_INVALID_DATES SQL mode. (Before 5.0.2, this mode was the default behavior for MySQL.)
--Gail ShawSQL Server MVP |
|
|
Next Page
|
|
|
|
|