terrymanu commented on issue #35245:
URL:
https://github.com/apache/shardingsphere/issues/35245#issuecomment-3491784051
Problem Analysis
Thank you for reporting this issue. After thorough analysis of
ShardingSphere's code implementation, I have identified the root cause of this
problem.
You are experiencing an issue where: customer is configured as a Single
Table, order_item as a Sharding Table, and creating a foreign key constraint
results in a "Table 'customer' already exists" error.
Root Cause
The root cause of this issue is ShardingSphere's architectural limitation
when handling foreign key constraints between Single Tables and Sharding
Tables, not a simple table existence check problem.
Technical Analysis
1. Single Table Characteristics: The customer table is configured as a
Single Table, stored only in one specified data source
2. Sharding Table Characteristics: The order_item table is a Sharding
Table, distributed across multiple data sources according to sharding strategy
3. Foreign Key Constraint Processing: When creating a foreign key on a
sharding table, ShardingSphere's constraint processor
(ShardingConstraintReviser) attempts to rewrite the foreign key constraint to
adapt to the
sharding environment
Key Code Path
The issue occurs in the following code path:
// ShardingConstraintReviser.java:40-48
for (DataNode each : shardingTable.getActualDataNodes()) {
String referencedTableName = originalMetaData.getReferencedTableName();
Optional<String> logicIndexName =
getLogicIndex(originalMetaData.getName(), each.getTableName());
if (logicIndexName.isPresent()) {
return Optional.of(new ConstraintMetaData(
logicIndexName.get(),
rule.getAttributes().getAttribute(DataNodeRuleAttribute.class).findLogicTableByActualTable(referencedTableName).orElse(referencedTableName)));
}
}
When the constraint processor iterates through all data nodes of the
sharding table, it cannot properly handle references to Single Tables, leading
to table existence conflicts during foreign key constraint processing.
ShardingSphere Architectural Limitations
Why Cross-Type Table Foreign Key Constraints Are Not Supported
1. Data Integrity Guarantee Difficulty: In distributed environments,
maintaining data consistency between Single Tables and Sharding Tables is
extremely costly
2. Routing Complexity: Single Tables use direct routing, Sharding Tables
use sharding routing, and cross-type foreign key constraints require unified
routing processing mechanisms
3. Performance Impact: Cross-datasource foreign key constraint checks
severely impact query and write performance
4. Transaction Complexity: Foreign key constraints require
cross-datasource distributed transaction support
ShardingSphere Design Principles
Code analysis shows that ShardingSphere primarily considers operations
between tables of the same type when designed:
- SingleConstraintReviser: Handles constraints within Single Tables
- ShardingConstraintReviser: Handles constraints between Sharding Tables
- Lacks dedicated processors for handling foreign key constraints between
Single Tables and Sharding Tables
Solutions
Solution 1: Unify Table Types (Recommended)
Configure both customer and order_item as Sharding Tables with the same or
compatible sharding strategies:
ShardingRuleConfiguration shardingRuleConfig = new
ShardingRuleConfiguration();
// Configure customer table as single-shard table (not sharded but managed
as Sharding Table)
ShardingTableRuleConfiguration customerTable = new
ShardingTableRuleConfiguration(
"customer", "ds.customer");
shardingRuleConfig.getTables().add(customerTable);
// order_item table with normal sharding
ShardingTableRuleConfiguration orderItemTable = new
ShardingTableRuleConfiguration(
"order_item", "ds.order_item_${order_id % 4}");
shardingRuleConfig.getTables().add(orderItemTable);
// Configure sharding strategy
shardingRuleConfig.getShardingAlgorithms().put(
"order_item_sharding", new
StandardShardingStrategyConfiguration("order_id", "order_item_mod"));
Solution 2: Implement Foreign Key Logic at Business Layer
Implement foreign key constraint checks at the application layer:
@Service
public class OrderItemService {
@Autowired
private CustomerRepository customerRepository;
@Autowired
private OrderItemRepository orderItemRepository;
@Transactional
public void createOrderItem(OrderItem orderItem) {
// Business layer foreign key check
if (!customerRepository.existsById(orderItem.getCustomerId())) {
throw new BusinessException("Customer not found: " +
orderItem.getCustomerId());
}
orderItemRepository.save(orderItem);
}
}
Solution 3: Use Sharding Table Reference Rules
If forced association is needed, use ShardingSphere's binding table
configuration:
ShardingTableReferenceRuleConfiguration referenceRule =
new ShardingTableReferenceRuleConfiguration(
"customer_order_item",
"customer",
"order_item"
);
shardingRuleConfig.getBindingTableGroups().add(referenceRule);
Temporary Solutions
If you must maintain the current table structure configuration, the
following temporary solutions can be used:
Use IF NOT EXISTS Syntax
-- Use IF NOT EXISTS when creating order_item table
CREATE TABLE IF NOT EXISTS order_item (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customer(id)
);
Create Foreign Key Constraint Separately
-- Create in steps
-- 1. First create table (without foreign key)
CREATE TABLE order_item (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE
);
-- 2. Then add foreign key constraint separately
ALTER TABLE order_item ADD CONSTRAINT fk_order_item_customer
FOREIGN KEY (customer_id) REFERENCES customer(id);
Best Practice Recommendations
1. Avoid Cross-Type Table Foreign Key Constraints: This is an
architectural limitation of ShardingSphere, not officially recommended
2. Unify Table Types: For tables requiring foreign key associations, it's
recommended to configure them as the same type (both as Sharding Tables or both
as Single Tables)
3. Business Layer Implementation: Implement foreign key logic at the
application layer, using transactions to ensure data consistency
4. Regular Data Validation: Perform data integrity validation through
scheduled tasks
Summary
This issue reflects ShardingSphere's architectural limitations when
handling mixed table type scenarios. Although temporary solutions can bypass
the error, in the long term, it's recommended to redesign table structure to
use unified table types or implement foreign key logic at the application
layer.
This issue has been widely discussed in the ShardingSphere community, and
the official stance is not to recommend creating foreign key constraints
between Single Tables and Sharding Tables, as this pattern presents
fundamental technical challenges in distributed environments.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
