MySQL中的外键？ [英] Foreign keys in MySQL?

问题描述

过去几周我一直在慢慢学习SQL。我已经拿起关系数据库的所有关系代数和基础知识。我现在要做的是学习如何实现。

我遇到的一个绊脚石是MySQL中的外键。除了它们存在于 InnoDB 存储架构中，我似乎无法找到更多的东西。 MySQL有。

在MySQL中实现外键的简单示例是什么？

这是模式的一部分我写道，似乎没有工作，如果你宁愿指出我的缺陷，而不是给我一个工作的例子。

  CREATE TABLE`posts`（
`pID` bigint（20）NOT NULL auto_increment，
`content` text NOT NULL，
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP，
`uID bigint（20）NOT NULL，
`wikiptr` bigint（20）默认NULL，
`cID` bigint（20）NOT NULL，
 PRIMARY KEY（`pID`），
外键（`cID`）引用类别，
外键（`uID`）引用用户
）ENGINE = InnoDB; 
  

解决方案

假设您的类别和用户表已经存在并且包含cID和uID分别作为主键，这应该工作︰
$ b $ pre $ code CREATE TABLE`posts`（
`pID` bigint 20）NOT NULL auto_increment，
`content` text NOT NULL，
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP，
`uID` bigint（20）NOT NULL，
`wikiptr bigint（20）默认NULL，
`cID` bigint（20）NOT NULL，
PRIMARY KEY（`pID`），
外键（`cID`）引用类别（`cID ``），
外键（`uID`）引用用户（`uID`）
）ENGINE = InnoDB;

在引用子句。

I have been slowly learning SQL the last few weeks. I've picked up all of the relational algebra and the basics of how relational databases work. What I'm trying to do now is learn how it's implemented.

A stumbling block I've come across in this, is foreign keys in MySQL. I can't seem to find much about the other than that they exist in the InnoDB storage schema that MySQL has.

What is a simple example of foreign keys implemented in MySQL?

Here's part of a schema I wrote that doesn't seem to be working if you would rather point out my flaw than show me a working example.

CREATE TABLE `posts` (
`pID` bigint(20) NOT NULL auto_increment,
`content` text NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uID` bigint(20) NOT NULL,
`wikiptr` bigint(20) default NULL,
`cID` bigint(20) NOT NULL,
PRIMARY KEY  (`pID`),
Foreign Key(`cID`) references categories,
Foreign Key(`uID`) references users
) ENGINE=InnoDB;

解决方案

Assuming your categories and users table already exist and contain cID and uID respectively as primary keys, this should work:

CREATE TABLE `posts` (
`pID` bigint(20) NOT NULL auto_increment,
`content` text NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uID` bigint(20) NOT NULL,
`wikiptr` bigint(20) default NULL,
`cID` bigint(20) NOT NULL,
PRIMARY KEY  (`pID`),
Foreign Key(`cID`) references categories(`cID`),
Foreign Key(`uID`) references users(`uID`)
) ENGINE=InnoDB;

The column name is required in the references clause.

这篇关于MySQL中的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！