0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences

DECLARE @Person TABLE
(
    ID INT PRIMARY KEY,
    Name NVARCHAR(50) NOT NULL
);

DECLARE @PhoneNumber TABLE
(
    ID INT PRIMARY KEY,
    [Type] VARCHAR(10) NOT NULL CHECK([Type] IN ('mobile', 'home')),
    Number VARCHAR(15) NOT NULL CHECK( PATINDEX('%[^0-9]%', Number) = 0 )
);

DECLARE @PersonPhoneNumber TABLE
(
    ID INT IDENTITY(2,2) PRIMARY KEY,
    Person_ID INT NOT NULL, --FOREIGN KEY
    PhoneNumber_ID INT NOT NULL --FOREIGN KEY
);

INSERT  @Person (ID, Name)
SELECT  1, 'Ed'
UNION ALL
SELECT  2, 'Bob';

INSERT  @PhoneNumber (ID, [Type], Number)
SELECT  1, 'home', '1111'
UNION ALL
SELECT  2, 'mobile', '2222'
UNION ALL
SELECT  3, 'home', '3333'
UNION ALL
SELECT  4, 'mobile', '4444';

INSERT  @PersonPhoneNumber (Person_ID, PhoneNumber_ID)
SELECT  1, 1
UNION ALL
SELECT  1, 2
UNION ALL
SELECT  2, 3
UNION ALL
SELECT  2, 4;

fork query

edited aug 18 12

created apr 18 12 anonymous