is this CHECK legal? - SQL Server Forums

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)

is this CHECK legal?

Author

Topic

k0001200
Starting Member

7 Posts

Posted - 2004-05-11 : 13:59:57

I am trying to check an attribute value to see that as long as it exists in one of the tables, it should be ok

CHECK StudentId IN ((SELECT StudentId FROM Enrolment) OR (SELECT StudentId FROM Student))

how else would you code the above, if this isnt legal ?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-11 : 14:02:19

where StudentId IN (SELECT StudentId FROM Enrolment UNION SELECT StudentId FROM Student)

Duane.

k0001200
Starting Member

7 Posts

Posted - 2004-05-11 : 14:34:02

So is the bottom create statement legal with the WHERE check ????
Doesnt seem to work for me :-s


CREATE TABLE ASN_DETAILS
(
  ASN_ID                            Number(6) CONSTRAINT ASN_ID_PK PRIMARY KEY,
  Order_ID                         Varchar2(6) NOT NULL WHERE Order_ID IN(SELECT Order_ID FROM Orders UNION SELECT Order_ID FROM NewOrders),
  ASN_DATE                       Date DEFAULT sysdate
);

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-11 : 14:38:36

I thik that a check contraint needs to be deterministic...

You could use a trigger to do this...I wonder if you can do a foreign key to a view?

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-11 : 14:41:45

No you can't use it there!
(I misunderstood the question)
That's only for selects.

You are going to have to use the references clause in your create table statement (I'm not sure about 2 referencing 2 tables though, you will have to try it out)

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-11 : 14:43:28

Nope..


USE Northwind
GO

CREATE VIEW myView99
AS
SELECT OrderId FROM Orders
UNION ALL
SELECT OrderId FROM [Order Details]
GO

CREATE TABLE myTable99(
	  Col1 int IDENTITY(1,1)
	, Col2 int
	, FOREIGN KEY (Col2) REFERENCES myView99(OrderId)
) 
GO

DROP VIEW myView99
GO

Need to use a trigger....

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-11 : 14:49:20

Well I guess you could do this


USE Northwind
GO

SELECT OrderId INTO NewOrders FROM Orders
 
ALTER TABLE newOrders ADD PRIMARY KEY (OrderId)

CREATE TABLE myTable99(
	  Col1 int IDENTITY(1,1)
	, Col2 int
	, FOREIGN KEY (Col2) REFERENCES Orders(OrderId)
	, FOREIGN KEY (Col2) REFERENCES NewOrders(OrderId)
) 
GO

DROP TABLE myTable99
DROP TABLE newOrders
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-11 : 15:21:47

two words: design problem.

why can the studentID be in one of these two tables? Why aren't they all stored in one? How can a studentID exist in the entrolled table but not exist in the student table?

- Jeff

k0001200
Starting Member

7 Posts

Posted - 2004-05-11 : 15:23:23

thanks for testing the above.

Would the following TRIGGER make sense? not very sure about the select part of it

Create trigger chech_orderID

On ASN_DETAILS

for insert
as
if (select count(*)
     from Orders, neworders
     where orders.order_ID=neworders.Order_ID) != @@rowcount
/* Cancel the insert and print a message.*/
  begin
    rollback transaction 
    print "No, the Order_id does not exist in orders or new orders." 
  end  
/* Otherwise, allow it. */
else
  print "Added! All title_id's exist in titles."

X002548
Not Just a Number

15586 Posts

Posted - 2004-05-11 : 15:34:30

I'm with Jeff on this one...while a child with 2 parents is not unique to biology,
I'm sure it is in data modeling....gotta be breaking some rule


IF NOT EXISTS(SELECT * FROM inserted i 
	       WHERE EXISTS (SELECT OrderId FROM Orders o1
			      WHERE i.OrderId = o1.OrderId 
		          UNION ALL
			     SELECT OrderId FROM NewOrders o2
			      WHERE i.OrderId = o2.OrderId))
BEGIN
  ROLLBACK
END

Brett

8-)

k0001200
Starting Member

7 Posts

Posted - 2004-05-11 : 16:02:18

thats for that!1 mate ur a life saver !!
well basically it is two kinds of orders and I split them into two tables as appose to having one table where some attribute may have been left empty depending on what order it is.
I guess you could deal with it via a trigger but am learning triggers (above was my first attempt at a trigger)

all in all..thanks brett!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources