Member Avatar for 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??

  • 7 Contributors
  • 9 Replies
  • 8K Views
  • 7 Years Discussion Span
  • Latest Post Latest Post by Inderjeet_2

Dani AI

Generated

In MySQL, the cardinality comes from constraints, not from the JOIN you write later. To read it from a schema (or enforce it), inspect whether the foreign key column is unique and whether it allows NULLs. In your example, Orders.Customer_SID references Customer(SID) but is not unique, so the schema permits many orders per customer (0..N if nullable, 1..N if NOT NULL). To see this on a live system, run:

SHOW CREATE TABLE Orders;

-- If Customer_SID is not UNIQUE/PK  => one-to-many
-- If Customer_SID is UNIQUE or PK    => one-to-one (0..1 if NULL allowed, 1..1 if NOT NULL)

To enforce one-to-one using your two tables without sharing the same primary key, make the foreign key unique:

ALTER TABLE Orders
  MODIFY Customer_SID INT NOT NULL,
  ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (Customer_SID) REFERENCES Customer(SID),
  ADD UNIQUE (Customer_SID);

To confirm how the current data behaves (independent of what the schema allows):

-- Customers with multiple orders (evidence of 1..N in practice)
SELECT Customer_SID, COUNT(*) AS order_count
FROM Orders
GROUP BY Customer_SID
HAVING COUNT(*) > 1;

Notes:

  • Use InnoDB to have MySQL actually enforce foreign keys. MyISAM ignores them.
  • JOINs do not define relationships; they only navigate the ones declared by keys and constraints.

Recommended Answers

All 9 Replies

Member Avatar for 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.

;)

Member Avatar for 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.

Member Avatar for colweb

There is a similar question here: with a lot of reply's. Maybe it will bring you closer to an answer.

Member Avatar for 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??

Member Avatar for Member #120589

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?

Edited by Member #120589 because: n/a
Member Avatar for 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".

Member Avatar for 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?

Member Avatar for 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?

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.