Friday, July 07, 2006

Referential Integrity in MySQL

When I work with database products like MS SQL Server, MySQL, PostgreSQL, I have to check the manual very frequently, because the DDL and DML for these products are not exactly the same. Even these statements are universal, it's also hard for me to memorize them all.
User manual of MySQL is not so good for us to immediately get a solution, because there are not many examples and they just provide long, long definitions.
These days I'm working on 2 tables in MySQL which need referential integrity. The statement is too long for me to recall, so I just put it down here for later copying&pasting. The following is a table creation statement which enforces referential integrity to another table:
create table channels(ChannelID integer primary key auto_increment not null, Title varchar(1000) not null, URL varchar(1000) not null, LastUpdated datetime not null, FolderID integer, index (FolderID), foreign key (FolderID) references folders(FolderID) on delete restrict on update restrict);

A source for referential integrity in MySQL: http://www.databasejournal.com/features/mysql/article.php/2248101 .

No comments: