我的查询:
CREATE TABLE `fisher`.`prodstock` (
`PID` VARCHAR(10) NOT NULL COMMENT 'Product Id',
`QUANTITY` DECIMAL(10,2) NOT NULL COMMENT 'Quantity in KG',
`CREATED` DATETIME NOT NULL DEFAULT NOW() COMMENT 'Invard Quantity Date',
PRIMARY KEY (`PID`),
CONSTRAINT `FK_PID`
FOREIGN KEY (`PID`)
REFERENCES `fisher`.`products` (`PID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
COMMENT = 'Stock of Product'
设置CURRENT_TIMESTAMP
为默认值
CREATE TABLE `fisher`.`prodstock` (
`PID` VARCHAR(10) NOT NULL COMMENT 'Product Id',
`QUANTITY` DECIMAL(10,2) NOT NULL COMMENT 'Quantity in KG',
`CREATED` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Invard Quantity Date',
PRIMARY KEY (`PID`),
CONSTRAINT `FK_PID`
FOREIGN KEY (`PID`)
REFERENCES `fisher`.`products` (`PID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
COMMENT = 'Stock of Product'
不能使用默认值now()
函数。如果你想保持DATETIME
数据类型,你必须创建一些触发器,并在表中插入记录时使用。默认值必须是常量,而不是表达式的结果。
触发的例子:
CREATE TRIGGER `yourTriggername` BEFORE INSERT ON `yourTablename`
FOR EACH ROW
SET NEW.datetimefield = NOW()
如果您可以将created
的数据类型更改为timestamp
,则可以使用
CREATE TABLE fisher.prodstock (
.....
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
....
)