CODEWITHSUNDEEP
mysqlsqlkey
james
james

Reputation: 35

sql COMPOSITE KEY help

Using sql, i need to make Lineno and orderline into a composite key how can i do this this, so far i've got 

CREATE TABLE ORDERLINE (
   ORDERNO INTEGER NOT NULL, 
   LINENO VARCHAR(4) NOT NULL, 
   NUMBERORDERED NUMERIC DEFAULT '1', 
   QUOTEDPRICE NUMERIC DEFAULT '0.00', 
   CONSTRAINT ORDERLINE_ORDERNO_PK PRIMARY KEY (ORDERNO), 
   CONSTRAINT ORDERLINE_ORDERNO_CC CHECK (ORDERNO BETWEEN 10000 AND 99999), 
   CONSTRAINT ORDERLINE_NUMBERORDERED CHECK (NUMBERORDERED BETWEEN 1 AND 100) 
);

Upvotes: 1

Views: 337

Answers (2)

Karl
Karl

Reputation: 3372

CREATE TABLE ORDERLINE (
    ORDERNO INTEGER NOT NULL,
    LINENO VARCHAR(4) NOT NULL,
    NUMBERORDERED NUMERIC DEFAULT '1',
    QUOTEDPRICE NUMERIC DEFAULT '0.00',
    CONSTRAINT ORDERLINE_ORDERNO_PK PRIMARY KEY(ORDERNO, LINENO), -- note alteration on this line
    CONSTRAINT ORDERLINE_ORDERNO_CC CHECK (ORDERNO BETWEEN 10000 AND 99999),
    CONSTRAINT ORDERLINE_NUMBERORDERED CHECK (NUMBERORDERED BETWEEN 1 AND 100)
);

You're very close. Note the addition of the column name above.

Identify which field is more likely to be a leading column in any query and put it first. For example; if you often access by OrderNO, LineNo that would be the best order, if however you access by LineNo, OrderNO go with that order.

Upvotes: 3

Salman Arshad
Salman Arshad

Reputation: 272296

If you've already created the table, you can drop the existing primary key (presumably ORDERNO) and then re-create the primary key on two columns like this:

ALTER TABLE `orderline`  DROP PRIMARY KEY;
ALTER TABLE `orderline`  ADD PRIMARY KEY (`ORDERNO`, `LINENO`);

Alternately, you can define the primary key in your create table statement, like this:

CREATE TABLE `orderline` (
    ORDERNO INTEGER NOT NULL, 
    LINENO VARCHAR(4) NOT NULL, 
    NUMBERORDERED NUMERIC DEFAULT '1', 
    QUOTEDPRICE NUMERIC DEFAULT '0.00',
    PRIMARY KEY (`ORDERNO`,`LINENO`),
    CONSTRAINT ORDERLINE_ORDERNO_CC CHECK (ORDERNO BETWEEN 10000 AND 99999), 
    CONSTRAINT ORDERLINE_NUMBERORDERED CHECK (NUMBERORDERED BETWEEN 1 AND 100) 
)

Upvotes: 2

Related Questions