I just checked the definition of CREATE TABLE in the MySQL manual <http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html> and I didn't see anything that suggests that primary keys based on VARCHAR columns need to be treated differently than other column types in MySQL. However, when I tried to create a table based on a VARCHAR(500) column, I got a message that said:
ERROR 1074: Too big column length for column 'account_number' (max = 255). Use BLOB instead Therefore, if your column is more than 255 characters long, you will have to use BLOB instead of VARCHAR. If your column is 255 or fewer characters long, change your column definition to CHAR from VARCHAR. Also, I believe you will have to include the phrase 'Type=INNODB' in your table definition; I think only the INNODB engine actually enforces FOREIGN KEY constraints. Here is an example of creating two tables, a parent and a child, that use the INNODB engine: drop table if exists dept; create table dept( deptno char(3) not null, deptname varchar(36) not null, mgrno char(6), primary key(deptno) ) Type=InnoDB; drop table if exists emp; create table emp( empno char(6) not null, firstnme char(12) not null, midinit char(1), lastname char(15) not null, workdept char(3) not null, salary dec(9,2) not null, primary key(empno), index(workdept), foreign key(workdept) references dept(deptno) on delete restrict ) Type=InnoDB; Table 'dept' is the parent table and contains one row for each department of an imaginary company. Table 'emp' is the child table and contains one row for each employee of the imaginary company. Each row in the 'emp' table has a department number in its 'workdept' column; 'workdept' is the foreign key and points to the 'deptno' column of the 'dept' table. Rhino ----- Original Message ----- From: "Dayakar" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, November 21, 2004 12:39 AM Subject: Help needed in creating primary key ,foreign key on a varchar datatype colum Hello, I am converting my database from oracle to mysql4.1 and i want the same structure as it is oracle like primary key and foreign key references etc.. In oracle i have created my primary key and foreign key references on a varchar datatype column, so can any one help me in doing the same in mysql. I have tried doing the same by seeing the examples given in mysql manual but not successfull. If any one can help me by giving a example then it would be great help for me. regards dayakar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
