2 zhl910114 zhl910114 于 2014.12.04 15:55 提问

mysql创建表错误,下面的代码哪儿不对?

CREATE TABLE ACCOUNT(
ID INT(4) CONSTRAINT ACCOUNT_ID_PK PRIMARY KEY,
RECOMMENDER_ID INT(4) CONSTRAINT ACCOUNT_RECOMMENDER_ID_FK REFERENCES ACCOUNT(ID),
LOGIN_NAME VARCHAR(30) NOT NULL CONSTRAINT ACCOUNT_LOGIN_NAME_UK UNIQUE,
LOGIN_PASSWD VARCHAR(30) NOT NULL,
STATUS CHAR(1) CONSTRAINT ACCOUNT_STATUS_CK CHECK (STATUS IN (0,1,2)),
CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PAUSE_DATE DATE,
CLOSE_DATE DATE,
REAL_NAME VARCHAR(20) NOT NULL,
IDCARD_NO CHAR(18) CONSTRAINT ACCOUNT_INCARD_NO UNIQUE NOT NULL,
BIRTHDATE DATE,
GENDER CHAR(1) CONSTRAINT ACCOUNT_GENDER_CK CHECK (GENDER IN (0,1)),
OCCUPATION VARCHAR(50),
TELEPHONE VARCHAR(15) NOT NULL,
EMAIL VARCHAR(50),
MAILADDRESS VARCHAR(50),
ZIPCODE CHAR(6),
QQ VARCHAR(15),
LAST_LOGIN_TIME DATE,
LAST_LOGIN_IP VARCHAR(15)
);

3个回答

S_JIMO
S_JIMO   2014.12.04 16:47
已采纳

字段写上面,像这些主键,外键。。待该表的字段写完之后写到最下面,不要写到字段后面

m949181240
m949181240   2014.12.04 16:12

ID INT(4) CONSTRAINT ACCOUNT_ID_PK PRIMARY KEY,
RECOMMENDER_ID INT(4) CONSTRAINT ACCOUNT_RECOMMENDER_ID_FK REFERENCES ACCOUNT(ID),

这种简写格式是不能命名约束的。如果要命名约束,要单独起一行,如:
ID INT(4) ,
RECOMMENDER_ID INT(4) ,
constraint ACCOUNT_ID_PK PRIMARY KEY(ID),
constraint ACCOUNT_RECOMMENDER_ID_FK foreign key(RECOMMENDER_ID) REFERENCES ACCOUNT(ID)

m949181240
m949181240 啰嗦一句要指定主键只要这样:ID INT(4) PRIMARY KEY
接近 3 年之前 回复
testcs_dn
testcs_dn   Rxr 2014.12.04 18:26

[Err] 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 'CONSTRAINT ACCOUNT_ID_PK PRIMARY KEY,
RECOMMENDER_ID INT(4) CONSTRAINT ACCOUNT_' at line 2

如果不需要指定约束名称,这样就可以了:
CREATE TABLE ACCOUNT(
ID INT(4) PRIMARY KEY,
RECOMMENDER_ID INT(4) REFERENCES ID,
LOGIN_NAME VARCHAR(30) NOT NULL UNIQUE,
LOGIN_PASSWD VARCHAR(30) NOT NULL,
STATUS CHAR(1) CHECK (STATUS IN (0,1,2)),
CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PAUSE_DATE DATE,
CLOSE_DATE DATE,
REAL_NAME VARCHAR(20) NOT NULL,
IDCARD_NO CHAR(18) UNIQUE NOT NULL,
BIRTHDATE DATE,
GENDER CHAR(1) CHECK (GENDER IN (0,1)),
OCCUPATION VARCHAR(50),
TELEPHONE VARCHAR(15) NOT NULL,
EMAIL VARCHAR(50),
MAILADDRESS VARCHAR(50),
ZIPCODE CHAR(6),
QQ VARCHAR(15),
LAST_LOGIN_TIME DATE,
LAST_LOGIN_IP VARCHAR(15)
);

Navicat for mysql 10,mysql 5.0下测试通过

Csdn user default icon
上传中...
上传图片
插入图片