Breaking down a bit of Code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Breaking down a bit of Code

Author

Topic

Brharrii
Starting Member

4 Posts

Posted - 2012-09-12 : 14:02:56

I'm trying to learn about Many to many relationships. I found a website that explains them by giving you code to run in SQL that will build the tables for you. I had intended to build the tables and then look at them to see how they were setup, but the code must have been written for another version of SQL (I'm using access 2010) as it gives me errors every time I try to run it.

This is the code:

-- -----------------------------------------------------
-- Table `Country`
-- -----------------------------------------------------
CREATE TABLE `Country` (
  `countryId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `countryName` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`countryId`) );
 
-- -----------------------------------------------------
-- Table `Language`
-- -----------------------------------------------------
CREATE TABLE `Language` (
  `languageId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `languageName` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`languageId`) );
 
-- -----------------------------------------------------
-- Table `Country2Language`
-- -----------------------------------------------------
CREATE TABLE `Country2Language` (
  `Country_countryId` INT UNSIGNED NOT NULL ,
  `Language_languageId` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`Country_countryId`, `Language_languageId`) ,
  INDEX `fk_Country_has_Language_Language1` (`Language_languageId` ASC) ,
  INDEX `fk_Country_has_Language_Country` (`Country_countryId` ASC) ,
  CONSTRAINT `fk_Country_has_Language_Country`
    FOREIGN KEY (`Country_countryId` )
    REFERENCES `Country` (`countryId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Country_has_Language_Language1`
    FOREIGN KEY (`Language_languageId` )
    REFERENCES `Language` (`languageId` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

I'm hoping I can list some of the commands I'm not understanding and get some clarification on their meanings:

1. "INT UNSIGNED NOT NULL AUTO_INCREMENT "
2. "INDEX `fk_Country_has_Language_Language1` (`Language_languageId` ASC)" - I've never done anything with Indexes before, Does anyone have a source that they like that teaches about them?
3. "CONSTRAINT `fk_Country_has_Language_Country`"
4. "REFERENCES `Country` (`countryId` )"
5. "ON DELETE NO ACTION" and "ON UPDATE NO ACTION"

Thank you for your help

Bruce

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 14:05:51

first check the site from which you downloaded the sql to understand which DBMS system its giving the code for. Its not T-SQL also which is Microsoft SQL Server's implementation of SQL language.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Brharrii
Starting Member

4 Posts

Posted - 2012-09-12 : 14:09:23

This is what I'm working from:

http://sqlrelationship.com/many-to-many-relationship/

it looks like it is designed for MYSQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 15:31:02

ok...then that explains it.

either try it out in mysql or download t-sql code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Brharrii
Starting Member

4 Posts

Posted - 2012-09-12 : 16:12:12

thanks for responding,

I wish I could download MYSQL, our IT Department has our computers locked up pretty tight. Unfortunately that is not an option for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 16:56:45

in t-sql try like


-- -----------------------------------------------------
-- Table `Country`
-- -----------------------------------------------------
CREATE TABLE Country 
(
  countryId INT IDENTITY(1,1) NOT NULL,
  countryName VARCHAR(45) NOT NULL ,
  PRIMARY KEY (countryId) );
 
-- -----------------------------------------------------
-- Table `Language`
-- -----------------------------------------------------
CREATE TABLE [Language] (
  languageId INT IDENTITY(1,1) NOT NULL,
  languageName VARCHAR(45) NOT NULL ,
  PRIMARY KEY (languageId) );
 
-- -----------------------------------------------------
-- Table `Country2Language`
-- -----------------------------------------------------
CREATE TABLE Country2Language (
  Country_countryId INT  NOT NULL ,
  Language_languageId INT  NOT NULL ,
  PRIMARY KEY CLUSTERED (Country_countryId, Language_languageId) 
  )
  
  ALTER TABLE Country2Language ADD CONSTRAINT fk_Country_has_Language_Country
    FOREIGN KEY (Country_countryId )
    REFERENCES Country (countryId )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

  ALTER TABLE Country2Language ADD CONSTRAINT  fk_Country_has_Language_Language1
    FOREIGN KEY (Language_languageId )
    REFERENCES Language (languageId )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Brharrii
Starting Member

4 Posts

Posted - 2012-09-12 : 18:52:11

Wow, I appreciate the time you must have spent going through and re-writing that. I'm sorry to say that I only have access to Microsoft Access (I tried it in access and it said there was a syntax error). I'm really not as concerned with making the query work though, so much as I am in understanding the commands it contains so that I can create the table manually (as I had posted in the initial question).

thanks again for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 22:24:58

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources