这个 create table 语句有什么问题? [英] What is wrong with this create table statement?
问题描述
我目前正在为学校项目编写数据库.我在 xampp 上使用 MySQL 并尝试将此表添加到我的数据库中.我的 SQL 语法仍然不是 100%,这里有一个错误,我似乎无法弄清楚:
Im currently writing a database for a school project. Im using MySQL on xampp and trying to add this table to my database. Im still not 100% on my SQL syntax and theres an error here I cannot seem ti figure out:
CREATE TABLE photoDB(
U_id INT UNSIGNED NOT NULL FOREIGN KEY REFERENCES userDB(U_id),
P_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
C_id INT UNSIGNED NOT NULL FOREIGN KEY REFERENCES table_comments(C_id),
PhotoName VARCHAR(50),
Description TEXT NOT NULL,
File VARCHAR,
Views BIGINT UNSIGNED,
Rep DOUBLE (100000, 2),
UploadDate DATETIME,
EditDate DATETIME,
EditVersion INT UNSIGNED,
LatestEditVerion INT UNSIGNED
);
我尝试创建的所有表都存在同样的问题.
Im having the same issue with all of the tables Im trying to create.
错误信息如下:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY REFERENCES userDB(U_id), P_id INT UNSIGNED NOT NULL AUTO_INCREMENT ' at line 2
提前致谢
推荐答案
之前的答案都是正确的.您还有其他问题(例如,您不能拥有那么大的 DOUBLE;最大 = 255).
The previous answers are true. You've got other problems, too (e.g. you can't have a DOUBLE that big; max = 255).
你有问题,伙计.
这是一个简单的例子,也许你可以扩展.它有两个表,它们之间是多对多的关系.连接表有两个外键.它适用于 MySQL - 我刚刚创建了一个数据库并添加了这些表.
Here's a simple example that perhaps you can extend. It has two tables with a many-to-many relationship between them. The join table has two foreign keys. It works in MySQL - I just created a database and added these tables.
use stackoverflow;
create table if not exists stackoverflow.product
(
product_id int not null auto_increment,
name varchar(80) not null,
primary key(product_id)
);
create table if not exists stackoverflow.category
(
category_id int not null auto_increment,
name varchar(80) not null,
primary key(category_id)
);
create table if not exists stackoverflow.product_category
(
product_id int,
category_id int,
primary key(product_id, category_id),
constraint product_id_fkey
foreign key(product_id) references product(product_id)
on delete cascade
on update no action,
constraint category_id_fkey
foreign key(category_id) references category(category_id)
on delete cascade
on update no action
);
insert into stackoverflow.product(name) values('teddy bear');
insert into stackoverflow.category(name) values('toy');
insert into stackoverflow.product_category
select p.product_id, c.category_id from product as p, category as c
where p.name = 'teddy bear' and c.name = 'toy';
这篇关于这个 create table 语句有什么问题?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!