one to many and one to one relationship in mysql

[JerieLsky]

Hi, not very familiar with mysql. I have here a mysql statement:

table customer:

CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

table orders:

CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));

My question is, how are you going to identify if the referencing is a one-to-many or a one-to-one relationship??

[untitledking]

Hey there,

Now I am still learning MySQL, and I do not have much experience in this portion of SQL, not there yet. but here is a site you can go to, it may give you the help you are looking for.

http://dev.mysql.com/doc/refman/4.1/en/index.html

This is a MySQL manual.

Hope I could be of service to you.

;)

[JerieLsky]

Hey there,

Now I am still learning MySQL, and I do not have much experience in this portion of SQL, not there yet. but here is a site you can go to, it may give you the help you are looking for.

http://dev.mysql.com/doc/refman/4.1/en/index.html

This is a MySQL manual.

Hope I could be of service to you.

;)

Nah.. I guess the answer that I'm looking for is not here. Thanks anyway.

[colweb]

There is a similar question here: http://www.codeguru.com/forum/showthread.php?t=494378 with a lot of reply's. Maybe it will bring you closer to an answer.

[d5e5]

I can't point to an authority to back me up, but my guess is that we discover the one-one vs. on-many relationship by studying the PRIMARY key of the tables in question. Because the values of the primary keys of the two tables do not have to be identical between linked records of the two tables the relationship is one-many. The table with the foreign key is on the many side of the relationship.

What if you wanted a one to one relationship between two tables? For example, a customer table and a Customer_Current_Address table. It seems to me that they both would have the same PRIMARY key to support the one to one relationship. Plus one of the tables would have a foreign key to preserve referential integrity. In other words, the rule that you can't insert a Customer_Current_Address record before inserting a customer record is expressed by having a foreign key field in the Customer_Current_Address table.

Hi, not very familiar with mysql. I have here a mysql statement:

table customer:

CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

table orders:

CREATE TABLE ORDERS
(Order_ID integer,
Order_Date date,
Customer_SID integer,
Amount double,
Primary Key (Order_ID),
Foreign Key (Customer_SID) references CUSTOMER(SID));

My question is, how are you going to identify if the referencing is a one-to-many or a one-to-one relationship??

With most implementations, the foreign key clause is dropped (no constraints) .The relationship is created within the SQL statement via JOIN (Inner, Left, Right etc) clause. Does it really matter if it's 1-1 or 1-many?

[drjohn]

one order is related to only one customer, but one customer can be related to many orders
therefore it is one (customer) to many (orders)

You just ASK yourself "will this happen".

[JerieLsky]

Hey there,

Now I am still learning MySQL, and I do not have much experience in this portion of SQL, not there yet. but here is a site you can go to, it may give you the help you are looking for.

http://dev.mysql.com/doc/refman/4.1/en/index.html

This is a MySQL manual.

Hope I could be of service to you.

;)

one order is related to only one customer, but one customer can be related to many orders
therefore it is one (customer) to many (orders)

You just ASK yourself "will this happen".

Yes sir, the example that I stated was a one-to-many relationship, but how are you going to verify if the connection made from both table is really a one-to-many relationship?

[d5e5]

Yes sir, the example that I stated was a one-to-many relationship, but how are you going to verify if the connection made from both table is really a one-to-many relationship?

There is nothing to verify. Your table definitions logically entail that the relationship is one customer to many orders, where 'many' means zero or more. The foreign key in the order table references the primary key of the customer table, which is unique by definition. Therefore there can exist no more than one customer record associated with any order record. No column in the customer table points to an order, so there is no constraint on how many orders can belong to this customer. How could this NOT be a one-to-many relationship?

[Inderjeet_2]

refer below scenario will help you to understand
one to one :
CREATE TABLE stock (
STOCK_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
STOCK_CODE varchar(10) NOT NULL,
STOCK_NAME varchar(20) NOT NULL,
PRIMARY KEY (STOCK_ID) USING BTREE,
UNIQUE KEY UNI_STOCK_NAME (STOCK_NAME),
UNIQUE KEY UNI_STOCK_CODE (STOCK_CODE) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8;

CREATE TABLE stock_detail (
STOCK_ID int(10) unsigned NOT NULL AUTO_INCREMENT,
COMP_NAME varchar(100) NOT NULL,
COMP_DESC varchar(255) NOT NULL,
REMARK varchar(255) NOT NULL,
LISTED_DATE date NOT NULL,
PRIMARY KEY (STOCK_ID) USING BTREE,
CONSTRAINT FK_STOCK_ID FOREIGN KEY (STOCK_ID) REFERENCES stock (STOCK_ID)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

See here stock_id is a primary key in both the tables.

and in one to many relationship primary key will be different in both the tables.
I hope answered.. This thread is however old but just posting answer to help others.