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)
-----------------------------------------------------
john.doe@test.com 1
j.d@aol.com 1
johnny6@yahoo.com 1
vanessa@vendor.com 2
vanessa.beau@yahoo.com 2
v.b.@aol.com 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
( '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 );
---
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
( '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 ---------------------------------------------------------
---
---
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
----------------------------------------------------------------------
