declaring constraint to consider prog logic

Question

I can open a trip only once but can close it multiple times. I can not declare the Trip_no + status as primary key since there can be multiple entries while closing the trip. Is there any way that will assure me that a trip number is opened only once?

For e.g. there should not be the second row with "Open" status for trip No. 3 since it is already there in the following table.

Trip No | Status
1     Open
1     Close
1     Close
2     Open
2     Close
3     Open
3     Close
3     Close
3     Close
3     Close

Answer 1

It is not clear why you have many entries for each trip status change.

But assuming that you have additional column, e.g. "Date", maybe you can store "OpenDate" in table "Trip" and use additional table TripCloses will contain only dates for trip Close.

Alternatively you may use two tables TripOpens and TripCloses and use different constraints on them.

In other words it is difficult to suggest without knowing details of information to store