CODEWITHSUNDEEP
sqlmysql
Eric Pruitt
Eric Pruitt

Reputation: 1903

Set primary key with two integers

I have a table with primary key (ColumnA, ColumnB). I want to make a function or procedure that when passed two integers will insert a row into the table but make sure the largest integer always goes into ColumnA and the smaller one into ColumnB.

So if we have SetKeysWithTheseNumbers(17, 19) would return

|-----------------|
|ColumnA | ColumnB|
|-----------------|
|19      | 17     |
|-----------------|

SetKeysWithTheseNumbers(19, 17) would return the same thing

|-----------------|
|ColumnA | ColumnB|
|-----------------|
|19      | 17     |
|-----------------|

Upvotes: 1

Views: 216

Answers (3)

outis
outis

Reputation: 77400

You could try triggers:

delimiter //

CREATE TRIGGER greater_ColA_table
  BEFORE INSERT ON table 
  FOR EACH ROW 
  BEGIN
    DECLARE a INTEGER;
    IF NEW.ColumnA < NEW.ColumnB THEN
      SET a=NEW.ColumnA; 
      SET NEW.ColumnA=NEW.ColumnB, NEW.ColumnB=a;
    END IF; 
  END //

delimiter ;

Repeat for the BEFORE UPDATE event.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332591

Use:

INSERT INTO your_table
  (COLUMN_A, COLUMN_B)
VALUES(GREATEST(@param_a, @param_b), 
       LEAST(@param_a, @param_b))

Reference:

Upvotes: 3

Enrique
Enrique

Reputation: 10117

Here is a store procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `SetKeysWithTheseNumbers` $$
CREATE PROCEDURE `SetKeysWithTheseNumbers` (

n1 integer,
n2 integer


)
BEGIN

declare n3 integer;
declare n4 integer;

if( n1 > n2 ) then
 set n3=n1;
 set n4=n2;
else
 set n3=n2;
 set n4=n1;
end if;

insert into mytable values(n3,n4);



END $$

DELIMITER ;

Upvotes: 0

Related Questions