Multiple Field Primary Key 1

[LittleSmudge]

I've created a table using the MySQL Control Centre and set two fields to be a Joint primary key. At least that's what I was intending to do and what would have happened if it had been an Access database ( I'm well used to working in Access )

What I seem to have ended up with is a table with two sepatate field each marked as a primary key and each one preventing duplicate entries in the separate fields.

I think that If I'd used the command line SQL to Create the table
CREATE TABLE tblLinkPersonDept (
PersonRef MediumInt;
DeptRef MediumInt
PRIMARY KEY ( PersonRef, DeptRef );

Then I'd have got what I want.

However, Is there a command line instruction to change the table I have to what I need ?
Or does anyone know what I'm doing wrong in the Control Centre ?






G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[vbkris]

is this what u want
Column1 Column2
1 2
2 1
3 5

the user must not enter another record like
1 2


is that it? if not give me an eg.

Known is handfull, Unknown is worldfull

 

[LittleSmudge]

What I WANT is to be able to enter

PersonRef    DeptRef
    1           1
    2           1
    3           1
    1           2
    4           2
    5           2
    6           2
    3           1

<<< This to be NOT allowed because it is a repeat

What I get is

PersonRef    DeptRef
    1           1
    2           1

<<< Objects here because DeptRef is a repeat

Also

PersonRef    DeptRef
    1           1
    1           2

<<< Also fails because PersonRef is repeating



'ope-that-'elps.







G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[vbkris]

it is better to do that using the frontend like php.
pass a query like
select * from table where personref='3' anddeptre='4'.

if there is a result then do not allow the user to add any rows...


Known is handfull, Unknown is worldfull

 

[LittleSmudge]

it is better to do that using the frontend like php.
pass a query like
select * from table where personref='3' anddeptre='4'.

if there is a result then do not allow the user to add any rows...


But doing it in the front end is totally against the whole principle and theory of Relational Database Design, Normalisation, Codd Rules etc, etc, etc.
If you rely on the Front End then your database schema does not have the inherent data integrity security/reliability that it should have.

It cannot possibly be better to fudge a solution in the wrong place rather than doing it properly.


G LS

 

[r937]

LittleSmudge, you are absolutely correct

doing it in code is, like, totally subverting the power of the relational data model -- if writing code is the answer, we might as well use flat files

new sig to add to my collection:

It cannot possibly be better to fudge a solution
in the wrong place rather than doing it properly.

as for your problem, i have no idea, i've never seen this control center of which you speak

yes, there probably are sql statements that will fix your table

perhaps

alter table tblLinkPersonDept
drop constraint bar1;

alter table tblLinkPersonDept
drop constraint bar2;

alter table tblLinkPersonDept
add constraint newpk
primary key (PersonRef, DeptRef);

but that's just off the top of my head without checking the manual for the correct syntax

you'll have to find out what the existing constraints are called

rudy

http://r937.com

p.s. congrats for not declaring an auto_increment for your linking table!!!

 

[vbkris]

hi,
i didnt mean to loosen ur concept. i simly suggeseted what struck me. i basically maniplate all my data using front end (with minimum backend code)..

what i suggesed is only one method out of many....



Known is handfull, Unknown is worldfull

 

[LittleSmudge]

Thanks for your comments r937

As you'll see from the Access Tables & Relationships board 'experts' list forum700 Database structure and schemas are not 'foreign' to me.

It is MySQL that is the new thing for me.


The &quot;Control Center&quot; that I refer to can be downloaded directly from the MySQL web site

http://www.mysql.com/products/mysqlcc/index.html

and to quote the page :-
&quot;MySQL Control Center (also known as MySQLCC) is a platform-independent GUI administration client for the MySQL database server.&quot;

&quot;MySQLCC is available under the GNU GPL&quot;


As for your suggested script line - that gives me a direction to work in, thanks.


Regards,

Graham

 

[LittleSmudge]

Finally I got to try it and, thanks to r937

ALTER TABLE tblLinkPersonDept
DROP PRIMARY KEY;

ALTER TABLE tblLinkPersonDept
Add PRIMARY KEY ( PersonRef, DeptRef );

gave me exactly what I wanted.



Have a Star for putting me on the right track rudy.
Your vote for r937 for TipMaster of the Week has been submitted.












G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[stlinuxily]

After read this thread ,i can't solve the problem ,i want to know the differences between the:
CREATE TABLE tblLinkPersonDept (
PersonRef MediumInt,
DeptRef MediumInt,
PRIMARY KEY ( PersonRef, DeptRef )
)type=innodb;

and the:

alter table tbllinkpersondept
drop primary key;
alter table tbllinkpersondept
add constraint newpk primary key (PersonRef, DeptRef);

things are not changed!!
any ideas?
stlinuxily

 

[r937]

what did you expect would be changed? it's the same key

rudy

http://r937.com/

SQL Consulting

 

[stlinuxily]

LittleSmudge (Programmer) Jun 25, 2003
What I WANT is to be able to enter

PersonRef DeptRef
1 1
2 1
3 1
1 2
4 2
5 2
6 2
3 1 <<< This to be NOT allowed because it is a repeat

What I get is
PersonRef DeptRef
1 1
2 1 <<< Objects here because DeptRef is a repeat

Also
PersonRef DeptRef
1 1
1 2 <<< Also fails because PersonRef is repeating



'ope-that-'elps.

i can't insert the same row twice, such as (1,1),the table already have one row ,but i can insert the rows like this :
(1,11),(1,100),(1,200)... at one time?

so ,i can't get the rows that different columns have different values !!

PersonRef DeptRef
1 1
2 1 <<it must can't insert
3 1 << also
1 2 << also
4 2
5 2 <<also
6 2 <<also
3 1 <<also

understand me ? we are now discussing the solution above!

 

[r937]

no, sorry, i do not understand you

what you said you wanted --

PersonRef DeptRef
1 1
2 1
3 1
1 2
4 2
5 2
6 2
3 1 <<< This to be NOT allowed because it is a repeat


is exactly what i get when i test it myself:

CREATE TABLE tblLinkPersonDept (
PersonRef MediumInt,
DeptRef MediumInt,
PRIMARY KEY ( PersonRef, DeptRef )
)type=innodb;

insert into tblLinkPersonDept 
( PersonRef , DeptRef )
values  
  ( 1, 1 )
, ( 2, 1 )
, ( 3, 1 )
, ( 1, 2 )
, ( 4, 2 )
, ( 5, 2 )
, ( 6, 2 )
;

PersonRef,DeptRef
1,1
1,2
2,1
3,1
4,2
5,2
6,2


insert into tblLinkPersonDept 
( PersonRef , DeptRef )
values  ( 3, 1 ) --  This to be NOT allowed because it is a repeat

"Duplicate entry '3-1' for key 1"

rudy

http://r937.com/

SQL Consulting

 

[stlinuxily]

Okay ,i think i didn't describe the question clearly! what i want is a table with two sepatate field each marked as a primary key and "each one" preventing duplicate entries in the separate fields. that is :when i insert a value (1,2),i must not insert another value such as column1=1 or column2=2

like this :
column1 column2 ; column1 column2
1 2 ; 1 2
2 3 ; 2 3
3 4 ; 2 4 #here want get error

because the value (2,3) and (2,4) have the same column1 value, it's not allowed. thus the (4, 5) and (5, 5) are not allowed in the table

Each column's value is unique, not the columns is unique

 

[r937]

oh, okay, i think i get it now

create table tblLinkPersonDept
( PersonRef mediumint
, DeptRef mediumint
, primary key ( PersonRef, DeptRef )
, unique index UniquePersonRef ( PersonRef )
, unique index UniqueDeptRef ( DeptRef )
);


rudy

http://r937.com/

SQL Consulting

 

[stlinuxily]

thanks i get too ! there are my sql queries
alter table tbllinkpersondept
add unique UniquePersonRef(PersonRef),
add unique UniqueDeptRef(DeptRef);

insert into tbllinkpersondept values(1, 1),(1,2);
ERROR 1062: Duplicate entry '1' for key 2