composite key..or anything else...

[nomanoma]

Hellooo

I created table in mysql:

CREATE TABLE activ
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
ms CHAR(15),
year CHAR(4),
month CHAR(2),
day CHAR(2)
);

I want to prevent when I manualy insert this table same input..basicly to prevent that by accident I execute 2 times same .sql script that has 5-6 insert records..

how to do that?

sample of insert is:

INSERT INTO activ (ms, year, month, day) VALUES ('32434432','2007','03','24');

any help ??

 

[r937]

alter table activ
add constraint the_real_pk unique (ms, year, month, day)

r937.com | rudy.ca

 

[nomanoma]

Nothing happened..I still can insert same data like:

INSERT INTO activ (ms, year, month, day) VALUES ('32434432','2007','03','24');

 

[r937]

try adding that exact same row again



r937.com | rudy.ca

 

[nomanoma]

I tried and it allows me to insert same row again..

 

[r937]

would you kindly do a SHOW CREATE TABLE

either you did not define the index properly or else there's a bug in your copy of mysql

r937.com | rudy.ca

 

[nomanoma]

CREATE TABLE `activ` (
`id` int(10) unsigned NOT NULL auto_increment,
`ms` char(15) NOT NULL default '',
`year` char(4) default NULL,
`month` char(2) default NULL,
`day` char(2) default NULL,
PRIMARY KEY (`id`,`ms`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

that is show create table...

 

[r937]

where's the UNIQUE key i recommended in post #2?

r937.com | rudy.ca

 

[nomanoma]

Yes but what if I for one day have to insert these values:

INSERT INTO activ (ms, year, month, day) VALUES ('99999423','2007','03','24');
INSERT INTO activ (ms, year, month, day) VALUES ('44433431','2007','03','24');

etc..

It should allow me to do so..but if I try to insert it again after one minute those same values that I have in .sql file:

INSERT INTO activ (ms, year, month, day) VALUES ('99999423','2007','03','24');
INSERT INTO activ (ms, year, month, day) VALUES ('44433431','2007','03','24');

it should report error..

 

[nomanoma]

and correct me if I am wrong with unique it will not allow me to do issue I explained in previous post..

 

[r937]

if you create the UNIQUE index as i suggested, then yes, you will not be able to insert the same values again

r937.com | rudy.ca