More than one column in partitioned view?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

More than one column in partitioned view??

Author

Topic

janus_007
Starting Member

2 Posts

Posted - 2003-04-15 : 11:08:17

Hello..

Im having trouble to make a partitioned view when I use more than one column in check constraint!

Should I use check constraint on all the primary keys? or is't possible to have 5 primary and check constraints on 2 of them ?

I've read
[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_06_17zr.asp?frame=true[/url]

But i cannot see limitations on this???

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-15 : 11:57:52

Why not post your sql to show us what you're trying to do....

Brett

8-)

janus_007
Starting Member

2 Posts

Posted - 2003-04-16 : 03:00:15

Oh yes.. sorry :)

CREATE TABLE Orders2000a
( orderid int NOT NULL,
customerid varchar(5) NOT NULL,
orderdate datetime NOT NULL,
ordername varchar(5) NOT NULL,
CONSTRAINT PK_Orders2000a
PRIMARY KEY(orderdate,orderid,ordername),
CONSTRAINT UQ_Orders2000a_orderid
UNIQUE(orderid),
CONSTRAINT CHK_Orders2000a_orderdate
CHECK(orderdate >= '20000101' AND orderdate < '20010101'),
CONSTRAINT CHK_Orders2000a_ordername
CHECK(ordername = 'a'))

CREATE TABLE Orders2000b
( orderid int NOT NULL,
customerid varchar(5) NOT NULL,
orderdate datetime NOT NULL,
ordername varchar(5) NOT NULL,
CONSTRAINT PK_Orders2000b
PRIMARY KEY(orderdate,orderid,ordername),
CONSTRAINT UQ_Orders2000b_orderid
UNIQUE(orderid),
CONSTRAINT CHK_Orders2000b_orderdate
CHECK(orderdate >= '20000101' AND orderdate < '20010101'),
CONSTRAINT CHK_Orders2000b_ordername
CHECK(ordername = 'b'))

CREATE TABLE Orders2001a
( orderid int NOT NULL,
customerid varchar(5) NOT NULL,
orderdate datetime NOT NULL,
ordername varchar(5) NOT NULL,
CONSTRAINT PK_Orders2001a
PRIMARY KEY(orderdate,orderid,ordername),
CONSTRAINT UQ_Orders2001a_orderid
UNIQUE(orderid),
CONSTRAINT CHK_Orders2001a_orderdate
CHECK(orderdate >= '20010101' AND orderdate < '20020101'),
CONSTRAINT CHK_Orders2001a_ordername
CHECK(ordername = 'a'))

CREATE TABLE Orders2001b
( orderid int NOT NULL,
customerid varchar(5) NOT NULL,
orderdate datetime NOT NULL,
ordername varchar(5) NOT NULL,
CONSTRAINT PK_Orders2001b
PRIMARY KEY(orderdate,orderid,ordername),
CONSTRAINT UQ_Orders2001b_orderid
UNIQUE(orderid),
CONSTRAINT CHK_Orders2001b_orderdate
CHECK(orderdate >= '20010101' AND orderdate < '20020101'),
CONSTRAINT CHK_Orders2001b_ordername
CHECK(ordername = 'b'))

GO
CREATE VIEW Orders
AS
SELECT * FROM Orders2000a
UNION ALL
SELECT * FROM Orders2000b
UNION ALL
SELECT * FROM Orders2001a
UNION ALL
SELECT * FROM Orders2001b

When I insert through the view i get an error.
INSERT INTO orders(orderid,customerid,orderdate,ordername)
VALUES(1,'aa','20000501','a')

error:
Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'Orders' is not updatable because a partitioning column was not found.

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-16 : 09:56:52

janus_007,

It appears that you need to select only 1 column as your partitioning column, so I would imagine Year would be your best option. I had to comment out parts of the code before it would work. You should be able to cut and paste this to see it work.

Good Luck


USE Northwind
GO

CREATE TABLE Orders2000a ( 
	  orderid int NOT NULL 
	, customerid varchar(5) NOT NULL 
	, orderdate datetime NOT NULL 
	, ordername varchar(5) NOT NULL 
PRIMARY KEY(	  orderdate
		, orderid
		, ordername
	   )
)
GO

ALTER TABLE Orders2000a ADD CONSTRAINT CK_DT_Orders2000a CHECK ( orderdate >= '20000101' AND orderdate < '20010101')
--ALTER TABLE Orders2000a ADD CONSTRAINT CK_NM_Orders2000a CHECK ( ordername = 'a')
GO

--CREATE TABLE Orders2000b ( 
--	  orderid int NOT NULL 
--	, customerid varchar(5) NOT NULL 
--	, orderdate datetime NOT NULL 
--	, ordername varchar(5) NOT NULL 
--PRIMARY KEY(	  orderdate
--		, orderid
--		, ordername
--	   )
--)
--GO

--ALTER TABLE Orders2000b ADD CONSTRAINT CK_DT_Orders2000b CHECK ( orderdate >= '20000101' AND orderdate < '20010101')
--ALTER TABLE Orders2000b ADD CONSTRAINT CK_NM_Orders2000b CHECK ( ordername = 'b')
--GO

CREATE TABLE Orders2001a ( 
	  orderid int NOT NULL 
	, customerid varchar(5) NOT NULL 
	, orderdate datetime NOT NULL 
	, ordername varchar(5) NOT NULL 
PRIMARY KEY(	  orderdate
		, orderid
		, ordername
	   )
)
GO

ALTER TABLE Orders2001a ADD CONSTRAINT CK_DT_Orders2001a CHECK ( orderdate >= '20010101' AND orderdate < '20020101')
--ALTER TABLE Orders2001a ADD CONSTRAINT CK_NM_Orders2001a CHECK ( ordername = 'a')
GO

--CREATE TABLE Orders2001b ( 
--	  orderid int NOT NULL 
--	, customerid varchar(5) NOT NULL 
--	, orderdate datetime NOT NULL 
--	, ordername varchar(5) NOT NULL 
--PRIMARY KEY(	  orderdate
--		, orderid
--		, ordername
--	   )
--)
--GO

--ALTER TABLE Orders2001b ADD CONSTRAINT CK_DT_Orders2001b CHECK ( orderdate >= '20010101' AND orderdate < '20020101')
--ALTER TABLE Orders2001b ADD CONSTRAINT CK_NM_Orders2001b CHECK ( ordername = 'b')
--GO


SET XACT_ABORT ON
GO
 

CREATE VIEW Orders2k AS
	SELECT orderid, customerid, orderdate, ordername FROM Orders2000a
     UNION ALL
--	SELECT orderid, customerid, orderdate, ordername FROM Orders2000b
--     UNION ALL
	SELECT orderid, customerid, orderdate, ordername FROM Orders2001a
--     UNION ALL
--	SELECT orderid, customerid, orderdate, ordername FROM Orders2001b
GO


INSERT INTO orders2k (orderid,customerid,orderdate,ordername) 
     VALUES(1,'aa','20000501','a') 
GO

SELECT * FROM Orders2k
GO

DROP TABLE Orders2000a
Go
--DROP TABLE Orders2000b
--Go
DROP TABLE Orders2001a
Go
--DROP TABLE Orders2001b
--GO
DROP VIEW Orders2k
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources