Entry

Database:Create: Type: Key:Primary+Foreign: How create person email one to many database, using SQL?

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

----------------------------------------------------------------------
--- Knud van Eeden --- 22 December 2003 - 10:41 pm -------------------

Database:Create: Type: Key:Primary+Foreign: How create person email 
one to many database, using SQL?

---

Steps: Overview:

 1. -Open Microsoft SQL server Query Analyzer

 2. -To create a database, tables, fill these tables,
     and show these tables,
     type or paste the following text:

--- cut here ---------------------------------------------------------

      --
      -- create your database
      --
      CREATE DATABASE Emaillist ON (
       NAME = 'Emaillist',
       FILENAME = 'c:\program files\microsoft sql 
server\mssql\data\emaillist.mdf',
       SIZE = 10,
       MAXSIZE = 50,
       FILEGROWTH = 5
      )
      LOG ON (
       NAME = 'sa',
       FILENAME = 'c:\program files\microsoft sql 
server\mssql\data\emaillist.ldf',
       SIZE = 5MB,
       MAXSIZE = 25MB,
       FILEGROWTH = 5MB
      )
      GO
      --
      -- use your database
      --
      USE Emaillist
      --
      -- 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 
( 'john.doe@test.com', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( 'j.d@aol.com', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( 'johnny6@yahoo.com', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( 'vanessa@vendor.com', 2 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( 'vanessa.beau@yahoo.com', 2 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( 'v.b.@aol.com', 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 ---------------------------------------------------------

---
---

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

----------------------------------------------------------------------