例如
CREATE TABLE `debtorsmaster` (
`debtorno` varchar(10) NOT NULL default '',
`name` varchar(40) NOT NULL default '',
`address1` varchar(40) NOT NULL default '',
`address2` varchar(40) NOT NULL default '',
`address3` varchar(40) NOT NULL default '',
`address4` varchar(50) NOT NULL default '',
`address5` varchar(20) NOT NULL default '',
`address6` varchar(15) NOT NULL default '',
`currcode` char(3) NOT NULL default '',
`salestype` char(2) NOT NULL default '',
`clientsince` datetime NOT NULL default '0000-00-00 00:00:00',
`holdreason` smallint(6) NOT NULL default '0',
`paymentterms` char(2) NOT NULL default 'f',
`discount` double NOT NULL default '0',
`pymtdiscount` double NOT NULL default '0',
`lastpaid` double NOT NULL default '0',
`lastpaiddate` datetime default NULL,
`creditlimit` double NOT NULL default '1000',
`invaddrbranch` tinyint(4) NOT NULL default '0',
`discountcode` char(2) NOT NULL default '',
`ediinvoices` tinyint(4) NOT NULL default '0',
`ediorders` tinyint(4) NOT NULL default '0',
`edireference` varchar(20) NOT NULL default '',
`editransport` varchar(5) NOT NULL default 'email',
`ediaddress` varchar(50) NOT NULL default '',
`ediserveruser` varchar(20) NOT NULL default '',
`ediserverpwd` varchar(20) NOT NULL default '',
`taxref` varchar(20) NOT NULL default '',
`customerpoline` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`debtorno`),
KEY `Currency` (`currcode`),
KEY `HoldReason` (`holdreason`),
KEY `Name` (`name`),
KEY `PaymentTerms` (`paymentterms`),
KEY `SalesType` (`salestype`),
KEY `EDIInvoices` (`ediinvoices`),
KEY `EDIOrders` (`ediorders`),
CONSTRAINT `debtorsmaster_ibfk_1` FOREIGN KEY (`holdreason`) REFERENCES `holdreasons` (`reasoncode`),
CONSTRAINT `debtorsmaster_ibfk_2` FOREIGN KEY (`currcode`) REFERENCES `currencies` (`currabrev`),
CONSTRAINT `debtorsmaster_ibfk_3` FOREIGN KEY (`paymentterms`) REFERENCES `paymentterms` (`termsindicator`),
CONSTRAINT `debtorsmaster_ibfk_4` FOREIGN KEY (`salestype`) REFERENCES `salestypes` (`typeabbrev`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;里面有4个外键,我想直接导入数据到这个表,但是外键太多,找子表插入又很麻烦,有没有什么方法
CREATE TABLE `debtorsmaster` (
`debtorno` varchar(10) NOT NULL default '',
`name` varchar(40) NOT NULL default '',
`address1` varchar(40) NOT NULL default '',
`address2` varchar(40) NOT NULL default '',
`address3` varchar(40) NOT NULL default '',
`address4` varchar(50) NOT NULL default '',
`address5` varchar(20) NOT NULL default '',
`address6` varchar(15) NOT NULL default '',
`currcode` char(3) NOT NULL default '',
`salestype` char(2) NOT NULL default '',
`clientsince` datetime NOT NULL default '0000-00-00 00:00:00',
`holdreason` smallint(6) NOT NULL default '0',
`paymentterms` char(2) NOT NULL default 'f',
`discount` double NOT NULL default '0',
`pymtdiscount` double NOT NULL default '0',
`lastpaid` double NOT NULL default '0',
`lastpaiddate` datetime default NULL,
`creditlimit` double NOT NULL default '1000',
`invaddrbranch` tinyint(4) NOT NULL default '0',
`discountcode` char(2) NOT NULL default '',
`ediinvoices` tinyint(4) NOT NULL default '0',
`ediorders` tinyint(4) NOT NULL default '0',
`edireference` varchar(20) NOT NULL default '',
`editransport` varchar(5) NOT NULL default 'email',
`ediaddress` varchar(50) NOT NULL default '',
`ediserveruser` varchar(20) NOT NULL default '',
`ediserverpwd` varchar(20) NOT NULL default '',
`taxref` varchar(20) NOT NULL default '',
`customerpoline` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`debtorno`),
KEY `Currency` (`currcode`),
KEY `HoldReason` (`holdreason`),
KEY `Name` (`name`),
KEY `PaymentTerms` (`paymentterms`),
KEY `SalesType` (`salestype`),
KEY `EDIInvoices` (`ediinvoices`),
KEY `EDIOrders` (`ediorders`),
CONSTRAINT `debtorsmaster_ibfk_1` FOREIGN KEY (`holdreason`) REFERENCES `holdreasons` (`reasoncode`),
CONSTRAINT `debtorsmaster_ibfk_2` FOREIGN KEY (`currcode`) REFERENCES `currencies` (`currabrev`),
CONSTRAINT `debtorsmaster_ibfk_3` FOREIGN KEY (`paymentterms`) REFERENCES `paymentterms` (`termsindicator`),
CONSTRAINT `debtorsmaster_ibfk_4` FOREIGN KEY (`salestype`) REFERENCES `salestypes` (`typeabbrev`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;里面有4个外键,我想直接导入数据到这个表,但是外键太多,找子表插入又很麻烦,有没有什么方法
update t_table a set (a.f1,a.f2,a.f3)=(select f1,f2,f3 from testz b where a.id=b.id)
为什么我用这种方法不成功
(select f1,f2,f3 from testz)b
set a.f1=b.f1 and a.f2=b.f2 and a.f3=b.f3
on a.id=b.id;
这样试一下
SQL 查询:ALTER TABLE `salesorders` DROP PRIMARY KEYMySQL 返回:文档
#1025 - Error on rename of '.\weberp\#sql-1b8_4b8' to '.\weberp\salesorders' (errno: 150)
mysql> alter table t change id id int not null;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> alter table t drop primary key;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(15) | YES | | NULL | |
| value | int(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)