April 16, 2008 at 9:17 am
I'm really just interested in what the following lines do:
ENGINE=InnoDB DEFAULT CHARSET=latin1;
--What is that engine and what does it do?
adminid tinyint(3) unsigned NOT NULL auto_increment,
--What does unsigned mean?
KEY FK_tbl_links_1 USING BTREE (linkcat),
-- I think that this assigns (linkcat) as a foreign key, but I'm not sure and I don't know what BTREE is
CONSTRAINT FK_tbl_links_1 FOREIGN KEY (linkcat) REFERENCES tbl_categories (catid) ON DELETE CASCADE ON UPDATE CASCADE
--I think that this uses (catid) from the tbl_categories table as a reference for (linkcat). What does --CASCADE do?
DROP DATABASE IF EXISTS dblinks;
CREATE DATABASE dblinks;
USE dblinks;
CREATE TABLE tbl_admin (
adminid tinyint(3) unsigned NOT NULL auto_increment,
adminusername varchar(15) NOT NULL,
adminpassword varchar(15) NOT NULL,
PRIMARY KEY (adminid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO tbl_admin (adminid, adminusername, adminpassword)
VALUES (1,'admin','password');
CREATE TABLE tbl_categories (
catid int(10) unsigned NOT NULL auto_increment,
catname varchar(30) NOT NULL,
PRIMARY KEY USING BTREE (catid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE tbl_links (
linkid int(10) unsigned NOT NULL auto_increment,
linktitle varchar(45) NOT NULL,
linkurl varchar(255) NOT NULL,
linkrecip varchar(255) NULL,
linkemail varchar(100) default NULL,
linkapproved enum('n','y') NOT NULL default 'n',
linkcat int(10) unsigned NOT NULL,
linkdate timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
linkdescr varchar(255) NOT NULL,
PRIMARY KEY (linkid),
KEY FK_tbl_links_1 USING BTREE (linkcat),
CONSTRAINT FK_tbl_links_1 FOREIGN KEY (linkcat) REFERENCES tbl_categories (catid) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
April 16, 2008 at 11:57 am
The syntax is a little different from SQL Server, but it looks like it's creating a database named dblinks then creating tables called tbl_admin, tbl_catagories, and tbl_links.
The tbl_links table has a foreign key constraint that links the linkcat column in tbl_links to the catid column in tbl_catagories. The cascading delete and update mean that when a row is deleted or updated in tbl_catagories, the corresponding rows in tbl_links will be deleted or updated.
Greg
April 16, 2008 at 12:49 pm
Thanks. Could you also tell me why linkcat is set as an int and explain what foreign keys and constraints do? Also is the '1' after 'FK_tbl_links_ ' the key ID?
I was thinking that linkcat is an int because catid is an int and instead of being referenced to catname, it's referenced to catid to save space, but how then do you get a form to display the catname?
April 16, 2008 at 2:40 pm
Apoztel (4/16/2008)
Thanks. Could you also tell me why linkcat is set as an int and explain what foreign keys and constraints do? Also is the '1' after 'FK_tbl_links_ ' the key ID?I was thinking that linkcat is an int because catid is an int and instead of being referenced to catname, it's referenced to catid to save space, but how then do you get a form to display the catname?
In a general sense, a CONSTRAINT is constraining the data that's allowed to be input into the column. In this situation, the code is creating a constraint called FK_tbl_links_1 on linkcat which is a foreign key to the catid field in tbl_categories. This basically means that the only values allowed in linkcat are ones that appear in the catid field in tbl_categories. linkcat is an int(10) because catid is an int(10), and looks like it stores the ID code of the category. This is a very common way of storing data (normalization). Hopefully, now, the previous poster's explanation on the UPDATE and DELETE actions makes more sense also.
The foreign key constraint is called FK_tbl_links_1 because it was probably auto-generated by some GUI, and it attached a "_1" in case there were more foreign keys to be made (*_2, *_3, etc.). It only means that it's the first one.
The form probably requests these two tables joined together on linkcat = catid, thereby attaching the catname to each record's linkcat value. If you're not familiar with data normalization and joining, you should probably read up on those concepts.
April 16, 2008 at 2:54 pm
Thanks. I really appreciate it.
April 16, 2008 at 3:04 pm
Sorry, I just noticed the comments at the top of your code.
ENGINE=InnoDB DEFAULT CHARSET=latin1;
ENGINE might refer to the underlying database program (or connectivity to it) that this code is run on.
adminid tinyint(3) unsigned NOT NULL auto_increment,
unsigned vs. signed is whether the field can accept negative numbers (a minus "sign") or not. In this case, not.
April 16, 2008 at 3:55 pm
this looks like a mysql script. InnoDB is one of the storage engines you can choose there. Another common one in mysql is MyIASM
---------------------------------------
elsasoft.org
April 16, 2008 at 3:58 pm
Your code is more than likely for MySQL, not for SQL Server.
You would probably get better help posting you question o a forum where that answer MySQL question, like http://www.dbforums.com/
April 18, 2008 at 6:23 am
thanks everyone for your help
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply