mysql - SQL: Creating a relation table with 2 different auto

i have 2 tables, both own auto incremented ids, of course primary keys.

when want create 3rd table establish relation between these 2 tables, have error.

first 1 can have 1 automatically-incremented column, second 1 occurs when delete auto_increment statement 2, therefore sql doesn't allow me make them foreign keys, because of type matching failure.

is there way can create relational table without losing auto increment features?

another possible (but not preffered) solution may there primary key in first table, username of user, not auto increment statement, of course. inevitable?

thanks in advance.

concepts

you have misunderstood basic concepts, , difficulties result that. have address concepts first, not problem perceive it, , consequently, problem disappear.

auto incremented ids, of course primary keys.

no, not. common misconception. , problems guaranteed ensue.

an id field cannot primary key in english or technical or relational senses.

sure, in sql, can declare any field primary key, doesn't magically transform primary key in english, technical, or relational senses. can name chihuahua "rottweiller", doesn't transform rottweiller, remains chihuahua. language, sql executes commands give it, not understand primary key mean relational, whacks unique index on column (or field).
the problem is, since have declared id primary key, think of primary key, , may expect has of qualities of primary key. except uniqueness of id value, provides no benefit. has none of qualities of primary key, or sort of relational key matter. not key in english, technical, or relational senses. declaring non-key key, confuse yourself, , find out there terribly wrong when user complains duplicates in table.

relational tables must have row uniqueness

a primary key on id field not provide row uniqueness. therefore not relational table containing rows, , if isn't that, file containing records. doesn't have of integrity, or power (at stage aware of join power only), or speed, table in relational database has.

execute this code (ms sql 2008) , prove yourself. please not read , understand it, , proceed read rest of answer, this code must executed before reading further. has curative value.

    create table dumb_file (         id         int      not null  identity  primary key,         name_first char(30) not null,         name_last  char(30) not null         )      insert dumb_file values ( "mickey", "mouse" )  -- succeeds     insert dumb_file values ( "mickey", "mouse" )  -- succeeds, not intended     insert dumb_file values ( "mickey", "mouse" )  -- succeeds, not intended      select * dumb_file

notice have duplicate rows. relational tables required have unique rows. further proof not have relational table, or of qualities of one.

notice in report, thing unique id field, no user cares about, no user sees, because not data, additional nonsense stupid "teacher" told put in every file. have record uniqueness not row uniqueness.

in terms of data (the real data minus extraneous additions), data name_last , name_first can exist without id field. person has first name , last name without id being stamped on forehead.

the second thing using confuses autoincrement. if implementing record filing system no relational capability, sure, helpful, don't have code increment when inserting records. if implementing relational database, serves no purpose @ all, because never use it. there many features in sql people never use.

corrective action

so how upgrade, elevate, dumb_file full of duplicate rows relational table, in order of qualities , benefits of relational table ? there 3 steps this.

you need understand keys
- and since have progressed isam files of 1970's, relational model, need understand relational keys. is, if wish obtain benefits (integrity, power, speed) of relational database.
dr e f cood, in rm, declared that:

a key made data

and

the rows in table must unique

your "key" not made data. additional, non-data parasite, caused being infected disease of "teacher". recognise such, , allow full mental capacity god gave (notice not ask think in isolated or fragmented or abstract terms, elements in database must integrated each other). make real key from data, , data. in case, there 1 possible key: (name_last, name_first).

try code, declare unique constraint on data:

     create table dumb_table (         id         int      not null  identity  primary key,         name_first char(30) not null,         name_last  char(30) not null          constraint uk              unique ( name_last, name_first )         )      insert dumb_table values ( "mickey", "mouse" )  -- succeeds     insert dumb_table values ( "mickey", "mouse" )  -- fails, intended     insert dumb_table values ( "minnie", "mouse" )  -- succeeds      select * dumb_table

now have row uniqueness. sequence happens people: create file allows dupes; have no idea why dupes appearing in drop-downs; user screams; tweak file , add index prevent dupes; go next bug fix. (they may correctly or not, different story.)

the second level. thinking people think beyond fix-its. since have row uniqueness, in heaven's name purpose of id field, why have ??? oh, because chihuahua named rotty , afraid touch it.

the declaration primary key false, remains, causing confusion , false expectations. genuine key there is, (name_last, name_fist), , alternate key @ point.

therefore id field totally superfluous; , index supports it; , stupid autoincrement; , false declaration primary key; , expectations may have of false.

therefore remove superfluous id field. try code:
```
    create table honest_table (         name_first char(30) not null,         name_last  char(30) not null          constraint pk          primary key ( name_last, name_first )         )      insert honest_table values ( "mickey", "mouse" )  -- succeeds     insert honest_table values ( "mickey", "mouse" )  -- fails, intended     insert honest_table values ( "minnie", "mouse" )  -- succeeds      select * honest_table 
```
works fine, works intended, without extraneous fields , indices.

please remember this, , right, every single time.

false teachers

in these end times, advised, have many of them. note well, "teachers" propagate id columns, virtue of detailed evidence in post, not understand relational model or relational databases. write books it.

as evidenced, stuck in pre-1970 isam technology. understand, , can teach. use sql database container, ease of access, recovery, backup, etc, content pure record filing system no relational integrity, power, or speed. afaic, serious fraud.

in addition id fields, of course, there several items key relational-or-not concepts, taken together, cause me form such grave conclusion. other items beyond scope of post.

one particular pair of idiots mounting assault on first normal form. belong in asylum.

answer

now rest of question.

is there way can create relational table without losing auto increment features?

that self-contradicting sentence. trust understand explanation, relational tables have no need autoincrement "features"; if file has autoincrement, not relational table.

autoincrement 1 thing only: if, , if, want create excel spreadsheet in sql database container, replete fields named a, b, , c, across top, , record numbers down left side. in database terms, result of select, flattened view of data, not source of data, organised (normalised).

another possible (but not preffered) solution may there primary key in first table, username of user, not auto increment statement, of course. inevitable?

in technical work, don't care preferences, because subjective, , changes time. care technical correctness, because objective, , not change.

yes, inevitable. because matter of time; number of bugs; number of "can't dos"; number of user screams, until face facts, overcome false declarations, , realise that:

the way ensure user rows unique, user_names unique, declare unique constraint on it
and rid of user_id or id in user file
which promotes user_name primary key

yes, because entire problem third table, not coincidentally, eliminated.

that third table associative table. key required (primary key) composite of 2 parent primary keys. ensures uniqueness of rows, identified keys, not ids.

i warning because same "teachers" taught error of implementing id fields, teach error of implementing id fields in associative table, where, ordinary table, superfluous, serves no purpose, introduces duplicates, , causes confusion. , doubly superfluous because 2 keys provide there, staring in face.

since not understand rm, or relational terms, call associative tables "link" or "map" tables. if have id field, in fact, files.

lookup tables

id fields particularly stupid thing do lookup or reference tables. of them have recognisable codes, there no need enumerate list of codes in them, because codes (should be) unique.

further, having codes in child tables fks, thing: code more meaningful, , saves unnecesary join:

    select ...         child_table           -- not lookup table         gender_code = "m"    -- fk in child, pk in lookup

instead of:

    select ...         child_table         gender_id = 6        -- meaningless maintainer

or worse:

    select ...         child_table c         -- trying determine         join lookup_table l             on c.gender_id = l.gender_id         l.gender_code = "m"  -- meaningful, known

note 1 cannot avoid: need uniqueness on lookup code and uniqueness on description. method prevent duplicates in each of 2 columns:

    create table gender (         gender_code  char(2)  not null,         name         char(30) not null          constraint pk              primary key ( gender_code )          constraint ak              unique ( name )         )

full example

from details in question, suspect have sql syntax , fk definition issues, give entire solution need example (since have not given file definitions):

    create table user (                 -- typical identifying table         user_name  char(16) not null,   -- short pk         name_first char(30) not null,   -- alt key.1         name_last  char(30) not null,   -- alt key.2         birth_date date     not null    -- alt key.3          constraint pk                   -- unique user_name             primary key ( user_name )          constraint ak                   -- unique person identification             primary key ( name_last, name_first, birth_date )         )      create table sport (                  -- typical lookup table         sport_code  char(4)  not null,    -- pk short code         name        char(30) not null     -- ak          constraint pk              primary key ( sport_code )          constraint ak              primary key ( name )         )      create table user_sport (           -- typical associative table         user_name  char(16) not null,   -- pk.1, fk         sport_code char(4)  not null,   -- pk.2, fk         start_date date     not null          constraint pk              primary key ( user_name, sport_code )          constraint user_plays_sport_fk             foreign key     ( user_name )             references user ( user_name )          constraint sport_occupies_user_fk             foreign key      ( sport_code )             references sport ( sport_code )         )

there, primary key declaration honest, primary key; no id; no autoincrement; no indices; no duplicate rows; no erroneous expectations; no consequential problems.

data model

here data model go definitions.

example user sport data model
if not used notation, please advised every little tick, notch, , mark, solid vs dashed lines, square vs round corners, means specific. refer idef1x notation.
a picture worth thousand words; in case standard-complaint picture worth more that; bad 1 not worth paper drawn on.
please check verb phrases carefully, comprise set of predicates. remainder of predicates can determined directly model. if not clear, please ask.

Search This Blog

Remember