表一 test1create table test1(
test1ID int primary key auto_increment,
test1name varchar(50)
)表二 test2 想连表一test1的 id主键做外键,下面写好像有错? 应该怎么写?create table test2(
test2ID int,
test2name varchar(50),
test1ID int foreign key references test1(test1ID)
)
test1ID int primary key auto_increment,
test1name varchar(50)
)表二 test2 想连表一test1的 id主键做外键,下面写好像有错? 应该怎么写?create table test2(
test2ID int,
test2name varchar(50),
test1ID int foreign key references test1(test1ID)
)
CONSTRAINT 'fk_test1ID' FOREIGN KEY ('test1') REFERENCES 'test1' ('test1ID')
test2ID int,
test2name varchar(50),
test1ID int,
foreign key (test1ID) references test1(test1ID)
)
另外在MYSQL中建议不要在列中直接做外键,在表中专门说明。mysql> create table test1(
-> test1ID int primary key auto_increment,
-> test1name varchar(50)
-> );
Query OK, 0 rows affected (0.09 sec)mysql> create table test2(
-> test2ID int,
-> test2name varchar(50),
-> test1ID int references test1(test1ID)
-> );
Query OK, 0 rows affected (0.08 sec)mysql> show create table test2;
+-------+---------------------------------
| Table | Create Table
+-------+---------------------------------
| test2 | CREATE TABLE `test2` (
`test2ID` int(11) DEFAULT NULL,
`test2name` varchar(50) DEFAULT NULL,
`test1ID` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------
1 row in set (0.05 sec)
mysql> create table test2(
-> test2ID int,
-> test2name varchar(50),
-> test1ID int ,
-> foreign key (test1ID) references test1(test1ID)
-> );
Query OK, 0 rows affected (0.08 sec)mysql> show create table test2;
+-------+-------------------------------------------------
| Table | Create Table
+-------+-------------------------------------------------
| test2 | CREATE TABLE `test2` (
`test2ID` int(11) DEFAULT NULL,
`test2name` varchar(50) DEFAULT NULL,
`test1ID` int(11) DEFAULT NULL,
KEY `test1ID` (`test1ID`),
CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`test1ID`) REFERE
`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------
1 row in set (0.00 sec)mysql>