faqts : Computers : Databases : Microsoft SQL Server

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

14 of 23 people (61%) answered Yes
Recently 5 of 10 people (50%) answered Yes

Entry

Database: Create: Microsoft SQLserver: Type: Key: Primary+Foreign: How create one to many tables?

Dec 22nd, 2003 13:48
Knud van Eeden,


----------------------------------------------------------------------
--- Knud van Eeden --- 22 December 2003 - 10:45 pm -------------------
Database: Create: Microsoft SQLserver: Type: Key: Primary+Foreign: How 
create one to many tables?
---
e.g.
One person can have many e-mail addresses.
---
Steps: Overview:
 1. Create a database
 2. Select this database
 3. Create tables
 4. Insert in tables
 5. View tables
---
Steps: Worked out:
 1. -Possibly create a new database
      CREATE DATABASE Test ON (
       NAME = 'Test',
       FILENAME = 'c:\program files\microsoft sql 
server\mssql\data\test.mdf',
       SIZE = 10,
       MAXSIZE = 50,
       FILEGROWTH = 5
      )
      LOG ON (
       NAME = 'sa',
       FILENAME = 'c:\program files\microsoft sql 
server\mssql\data\test.ldf',
       SIZE = 5MB,
       MAXSIZE = 25MB,
       FILEGROWTH = 5MB
      )
      GO
---
See also:
Database: Microsoft SQL server: SQL: Database: Operation: Create: How 
to create a database in SQL?
---
 2. -Select this database
      USE Test
---
See also:
Database: Microsoft SQL server: SQL: Database: Operation: Select: How 
to select an existing database in SQL?
---
 3. -Create your tables in this database
      Table 1:
      ---
      Table 'Person'
      ----------------------------------------------------
      NAME                     PERSONKEY (=PRIMARY KEY)
      ----------------------------------------------------
      john doe                 1
      vanessa beau             2
      ----------------------------------------------------
      ---
      Table 2:
      ---
      Table 'Email'
      -----------------------------------------------------
      EMAILADDRESS             PERSONKEY (=FOREIGN KEY)
      -----------------------------------------------------
      [email protected]        1
      [email protected]              1
      [email protected]        1
      [email protected]       2
      [email protected]   2
      [email protected]             2
      -----------------------------------------------------
 4. -Create your tables in this database using SQL
     1. Creating this table 1 in SQL
        ---
      CREATE TABLE Person (
       Name VARCHAR( 50 ) NOT NULL,
       PersonKey INT NOT NULL,
       PRIMARY KEY ( PersonKey )
      );
        ---
     2. Creating this table 2 in SQL
        ---
      CREATE TABLE Email (
       emailaddress VARCHAR( 50 ) NOT NULL,
       PersonKey INT NOT NULL,
       FOREIGN KEY ( PersonKey ) REFERENCES Person ( PersonKey )
      );
        ---
 5. -Insert some values in this tables
     1. Insert into table 1:
        ---
         INSERT INTO Person ( Name, PersonKey ) VALUES ( 'John Doe', 
1 );
         INSERT INTO Person ( Name, PersonKey ) VALUES ( 'Vanessa 
Beau', 2 );
        ---
     2. Insert into table 2:
        ---
         INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
         INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
         INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
         INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
         INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
         INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
        ---
  6. -View this tables
      1. View table 1
         ---
         SELECT * FROM Person;
         ---
      2. View table 2
         ---
         SELECT * FROM Email;
         ---
      3. View tables 1 and 2 connected via their primary and foreign 
key:
         ---
         SELECT * FROM Person, Email WHERE Person.Personkey = 
Email.Personkey;
---
See also:
Database: Microsoft SQL server: SQL: Database: Table: Operation: Key: 
Primary: Foreign: View: How to view tables connected via primary key 
and foreign key?
  7. -So alltogether this becomes:
--- cut here ---------------------------------------------------------
      --
      -- create your database
      --
      CREATE DATABASE Test ON (
       NAME = 'Test',
       FILENAME = 'c:\program files\microsoft sql 
server\mssql\data\test.mdf',
       SIZE = 10,
       MAXSIZE = 50,
       FILEGROWTH = 5
      )
      LOG ON (
       NAME = 'sa',
       FILENAME = 'c:\program files\microsoft sql 
server\mssql\data\test.ldf',
       SIZE = 5MB,
       MAXSIZE = 25MB,
       FILEGROWTH = 5MB
      )
      GO
      --
      -- use your database
      --
      USE Test
      --
      -- create table 1
      --
      CREATE TABLE Person (
       Name VARCHAR( 50 ) NOT NULL,
       PersonKey INT NOT NULL,
       PRIMARY KEY ( PersonKey )
      );
      --
      -- create table 2
      --
      CREATE TABLE Email (
       emailaddress VARCHAR( 50 ) NOT NULL,
       PersonKey INT NOT NULL,
       FOREIGN KEY ( PersonKey ) REFERENCES Person ( PersonKey )
      );
      --
      -- insert in table 1
      --
      INSERT INTO Person ( Name, PersonKey ) VALUES ( 'John Doe', 1 );
      INSERT INTO Person ( Name, PersonKey ) VALUES ( 'Vanessa Beau', 
2 );
      --
      -- insert in table 2
      --
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
      --
      -- view the tables
      --
      --
      -- view table 1
      --
      SELECT * FROM Person;
      --
      -- view table 2
      --
      SELECT * FROM Email;
      --
      -- View tables 1 and 2 connected via their primary and foreign 
key:
      --
      SELECT * FROM Person, Email WHERE Person.Personkey = 
Email.Personkey;
--- cut here ---------------------------------------------------------
---
---
Tested OK on
Microsoft SQL Server v2000 'Query Analyzer',
running on Microsoft Windows XP Professional.
---
---
Internet: see also:
---
Database: Microsoft SQL server: SQL: Operation: Overview:Can you give 
an overview of SQL operations?
http://www.faqts.com/knowledge_base/view.phtml/aid/27560/fid/147
----------------------------------------------------------------------