define constraints in mysql?!

Comments

• Ara_cam

  October 2002

  From looking at the manual, it appears that the Constraint and the Check variables do not go together, so try this syntax:
  [code]
  CREATE TABLE numbers (no INT NOT NULL CHECK (no >= 0));
  [/code]
  The Constraint keyword is used for foreign key references.
  
  Hope this helps!
  
  
  : [code]
  : CREATE TABLE numbers (no INT NOT NULL CONSTRAINT con_pos CHECK no >= 0);
  : [/code]
  : When I try to execute this I get an syntax error message. Maybe it's some really stupid question, but could please somebody clear this up for me.
  
  
• Kyon

  October 2002

  Hi.
  
  Already tried that but w/o success - thanks anyway.
  
  Cheers,
  Kyon
  
  -- planning is the substitution of hazard by error --
  
  
• Ara_cam

  October 2002

  I did some further testing, and as of the latest version, the Check option doesn't actually do anything. It is ignored by MySQL.
  
  (Here is where I've created your table, and added the constraint to the database, then tested it):
  [code]
  mysql> create table numbers (no int not null);
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> alter table numbers
  -> add constraint con_pos
  -> check (no >=0 );
  Query OK, 0 rows affected (0.03 sec)
  Records: 0 Duplicates: 0 Warnings: 0
  
  mysql> insert into numbers (no) values (-1);
  Query OK, 1 row affected (0.00 sec)
  
  mysql> select * from numbers;
  +----+
  | no |
  +----+
  | -1 |
  +----+
  1 row in set (0.02 sec)
  [/code]
  
  From the MySQL online user manual http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#CREATE_TABLE :
  [code]
  The FOREIGN KEY, CHECK, and REFERENCES
  clauses don't actually do anything [/code]
  
  So, I guess to make the Check constraint work, you will have to use program logic, rather than database logic. Unfortunately, MySQL tries to remove as much integrity coding as possible. For instance, the Foreign Key options don't really do anything either, unlike most databases. They expect the program acting as a front-end to the database to handle all the constraints.
  
  I know this isn't the answer you were looking for, but at least you know where to go next.
  
• Kyon

  October 2002

  Hi.
  
  Thanks for the effort you made for checking this out. So I'll put all that checking in my sourcecode. It's just a little bit astonishing that such a simple thing as a check constraint has not been realized yet in MySQL. Especially since I've been working before with Oracle.
  
  But now I know what to do.
  
  Thanks again
  
  Cheers,
  Kyon
  
  -- planning is the substitution of hazard by error --