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
