Composite Primary Key - How to increment one, while keeping the other constant?

[thedunnyman]

Hey guys,

Just trying to design a table which as one key that signifies a document. And the other key signifies the sections in the document. But the overall document can change as well.

I.e.

DOCUMENT 1 - Section 1/2/3/4
DOCUMENT 2 - Section 1/2
DOCUMENT 3 - Section 1
DOCUMENT 4 - Section 1/2/3/4

So basically, the section IDs should start again.

Here is the table i'm making with in MySQL:

CREATE TABLE TRAVELSEGMENT (
SegmentId int(10) unsigned NOT NULL auto_increment,
TravelPlanId int(10) unsigned NOT NULL,
CityId int(10) unsigned NOT NULL,
ArrivalDate datetime NOT NULL,
DepartureDate datetime NOT NULL,

PRIMARY KEY (SegmentId, TravelPlanId),
FOREIGN KEY (TravelPlanId) REFERENCES TRAVELPLAN (TravelPlanId)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (CityId) REFERENCES CITY (CityId)
ON DELETE CASCADE ON UPDATE CASCADE
);


And then in my ASP pages, I tried to do this:

SQLText = "Insert into TravelSegment Values (null,'" & Session("TravelPlanID") &_
"','" & Request.Form("CityID") & "','" & Request.Form("ArrivalDate") & "','" &_
Request.Form("DepartureDate") & "');"

But I get this error when the page runs:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver][mysqld-5.0.45-community-nt]Column 'SegmentId' cannot be null

/Ugrad/d/duncanl/group/AddTravelSegment.asp, line 78


So is there a way to get something implemented this way?
Sorry I am new at SQL/ASP so anything would be helpful!

Thanks,
Duncan :)

[brazenskies]

you have set the Segment ID column to NOT NULL, so you cannot put a null value in there.

You also have it set to auto increment so there is no need to put a value in there. Specify which fields you wish to put data in, then put matching values into VALUES(...

i.e...


SQLText = "Insert into TravelSegment(TravelPlanID, CityID, ArrivalDate, departureDate) Values (Session("TravelPlanID") &_
"','" & Request.Form("CityID") & "','" & Request.Form("ArrivalDate") & "','" &_
Request.Form("DepartureDate") & "');"


Basically, leave the auto increment column alone and it will do all the work for you