Tuesday, February 14, 2012

Constraints in SQL server

I wanna know how to define constraints in sql server.
everytime i try something it says "error validating constraint'

does someone have an example how a constraint have to look like?

PS: i want to the constraint that check's this:
i have an order.. an order haves orderlines
and we have a 'contract' .. and contract have 'contractlines'

.. an order can became a 'contract' .. and some of the orderlines will be 'contractlines'

so a contract have a FK:orderID.. and a contractline have FK:contractID&orderlineID

i want to check if the orderlineID in contractline exists in the correspondending order..

>>>something like:
orderlineID exists in ( select orderlineID from orderline left join
order on order.orderID = orderLine.orderID left join contract on
contract.orderID = order.orderID left join contract on contractLine.contractID = contract.contractIDIt sounds like you want foreign key constraints:


create table Orders
(
OrderID int not null,
primary key (OrderID),
)
create table OrderLines
(
OrderID int not null,
OrderLineID int not null,
primary key (OrderID, OrderLineID),
)
create table Contracts
(
ContractID int not null,
OrderID int null,
primary key (ContractID),
foreign key (OrderID) references Orders(OrderID),
)
create table ContractLines
(
ContractID int not null,
ContractLineID int not null,
OrderID int null,
OrderLineID int null,
primary key (ContractID, ContractLineID),
foreign key (OrderID, OrderLineID) references OrderLines(OrderID, OrderLineID),
)

I don't think this is a very good design for several reasons, including the following:
1) I used nulls
2) I have no idea if the various IDs are natural or surrogate keys
3) I assumed a lot of things about how the four concepts orders, contracts, order lines and contract lines are related

No comments:

Post a Comment