当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - SQL database tables relationship?

问题描述:

I have two tables in mysql:

CREATE TABLE names (name_ID INT NOT NULL AUTO_INCREMENT,

name VARCHAR(40) NOT NULL, PRIMARY KEY (name_ID));

CRETE TABLE surnames (surname_ID INT NOT NULL AUTO_INCREMENT,

name_ID INT NOT NULL FOREIGN KEY (name_ID) REFERENCES names (name_ID),

surnames VARCHAR(40) NOT NULL, PRIMARY KEY(surname_ID));

My question is, what values do I need to insert into the FOREIGN KEY column in the SURNAMES table?

For example, if I enter this data in NAMES table:

INSERT INTO names VALUES (NULL, 'Pamela');

INSERT INTO names VALUES (NULL, 'Sarah');

What should I use as the FOREIGN KEY in the SURNAMES table? What do I need to enter here for these two table to be related? How MySQL sorts values in foreign key in surnames tables?


this is example from forum DB:

CREATE TABLE languages (

lang_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,

lang VARCHAR(60) NOT NULL,

lang_eng VARCHAR(20) NOT NULL,

PRIMARY KEY (lang_id),

UNIQUE (lang)

);

CREATE TABLE threads (

thread_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

lang_id TINYINT(3) UNSIGNED NOT NULL,

user_id INT UNSIGNED NOT NULL,

subject VARCHAR(150) NOT NULL,

PRIMARY KEY (thread_id),

INDEX (lang_id),

INDEX (user_id)

);

CREATE TABLE posts (

post_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

thread_id INT UNSIGNED NOT NULL,

user_id INT UNSIGNED NOT NULL,

message TEXT NOT NULL,

posted_on DATETIME NOT NULL,

PRIMARY KEY (post_id),

INDEX (thread_id),

INDEX (user_id)

);

CREATE TABLE users (

user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,

lang_id TINYINT UNSIGNED NOT NULL,

time_zone VARCHAR(30) NOT NULL,

username VARCHAR(30) NOT NULL,

pass CHAR(40) NOT NULL,

email VARCHAR(60) NOT NULL,

PRIMARY KEY (user_id),

UNIQUE (username),

UNIQUE (email),

INDEX login (username, pass)

);

CREATE TABLE words (

word_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,

lang_id TINYINT UNSIGNED NOT NULL,

title VARCHAR(80) NOT NULL,

intro TINYTEXT NOT NULL,

home VARCHAR(30) NOT NULL,

forum_home VARCHAR(40) NOT NULL,

`language` VARCHAR(40) NOT NULL,

register VARCHAR(30) NOT NULL,

login VARCHAR(30) NOT NULL,

logout VARCHAR(30) NOT NULL,

new_thread VARCHAR(40) NOT NULL,

subject VARCHAR(30) NOT NULL,

body VARCHAR(30) NOT NULL,

submit VARCHAR(30) NOT NULL,

posted_on VARCHAR(30) NOT NULL,

posted_by VARCHAR(30) NOT NULL,

replies VARCHAR(30) NOT NULL,

latest_reply VARCHAR(40) NOT NULL,

post_a_reply VARCHAR(40) NOT NULL,

PRIMARY KEY (word_id),

UNIQUE (lang_id)

);

Inserting Foreign Key is What I don't get? For example:

INSERT INTO `threads` (`thread_id`, `lang_id`, `user_id`, `subject`) VALUES

(1, 4, 1, 'Byttet til PHP 5.0 fra PHP 4.0 - variabler utilgjengelige'),

(2, 4, 2, 'Automatisk bildekontroll'),

(3, 3, 5, 'Lancer une Page HTML en PHP'),

(4, 3, 4, 'Ajouter des adresses a PHP List depuis un formulaire'),

(5, 9, 4, 'Ovo je dio teksta koji se ubacuje u forum'),

(7, 1, 1, 'Sample Thread');

Are Thread_id, lang_id, foreign keys here? If no, they are manually inserted? If yes, are they manually inserted? lol

网友答案:

I'm note sure I understand what you need and I don't understand why you're relating a surname to a name.
Anyway, when you use INSERT INTO names VALUES (NULL, 'Pamela') an id in names table is created (say it's 1, because the first inserted); so, if you want to create a surname related to name Pamela you should do INSERT INTO surnames (NULL, 1, 'Anderson').
Same for second (say its id is 2) INSERT INTO surnames (NULL, 2, 'Gellar').

How MySQL sorts values in foreign key in surnames tables?
What do you need? Why is this important?

EDITED after new example:
Your threads table doesn't contain any foreigh key; user_id should probably be related to users table, as lang_id should probably be realted to languages table, but they are not.
The only thing I see is that both lang_id and user_id fields have an index to make queries fater if you use them in WHERE or ORDER BY condition.
Foreign keys must be declared while designing database, not with queries!!
You can "logically" relate fields inserting same id both in parent and in child table, but it's not exactly the same.

网友答案:

I would create another table id | name_id | surname_id and well, insert the ids of the names there. Check out InnoDB

网友答案:

You would have to insert whatever the value of name_id is after inserting your data. The name_id field appears to be an identity column, so whenever you insert a row into the names table, that column should be automatically populated (starting at 1 and incrementing as more rows are added).

That said, you shouldn't be trying to insert 'NULL' into the name_id column as you have defined it to be NOT NULL.

分享给朋友:
您可能感兴趣的文章:
随机阅读: