| Author |
 Topic  |
|
|
danigardner92
Starting Member
1 Posts |
 Posted - 10/04/2012 : 22:59:29
|
Create Table tblWoman(
FirstName Varchar(20) Not Null
Check (Len(FirstName)>=2),
LastName Varchar(30) Not Null
Check (Len(LastName)>=2),
MaidenName Varchar(30) Null
Check (MaidenName Is Null or Len(MaidenName)>=2),
WomanID Integer Primary Key Nonclustered
Check(WomanID >= 1001),
DOB DATE Not Null
Check(DOB <= GetDate())
)
Create Table tblMan(
FirstName Varchar(20) Not Null
Check (Len(FirstName)>=2),
LastName Varchar(30) Not Null
Check (Len(LastName)>=2),
ManID Integer Primary Key Nonclustered
Check(ManID >= 1001),
DOB DATE Not Null
Check(DOB <= GetDate()),
WifeId Integer Null
References tblWoman(WomanID),
DOM DATE Null
Check(DOM Is Null or DOM <= GetDate()),
Check ((WifeId Is Null And DOM Is Null) Or (WideId Is Not Null And DOM Is Not Null))
)
Create Table tblChild(
FirstName Varchar(20) Not Null
Check (Len(FirstName)>=2),
LastName Varchar(30) Not Null
Check (Len(LastName)>=2),
ChildID Integer Primary Key Nonclustered
Check(ChildID >= 1001),
Gender Char(1) Not Null
Check (Gender = 'F' or Gender = 'M'),
DOB DATE Not Null
Check(DOB <= GetDate()),
FatherId Integer Null
References tblMan(ManID),
MotherID Integer Null
References tblWoman(WomanID),
Check ((FatherId Is Null and MotherId Is Not Null) or (FatherId Is Not Null and MotherId Is Null) Or (FatherId Is Not Null and MotherId Is Not Null))
)
Create Table tblSiblingPair(
ChildID1 Integer Not Null,
References tblChild(ChildID),
ChildID2 Integer Not Null
References tblChild(ChildID),
Primary Key Clustered (ChildID1,ChildID2)
)
Create Clustered Index idxManName
On tblMan (
LastName,
FirstName
)
Create Clustered Index idxWomanName
On tblWoman (
LastName,
FirstName
)
Create Clustered Index idxChildName
On tblChild (
LastName,
FirstName
)
Create Index idxFather
On tblChild (
FatherID
)
Create Index idxMother
On tblChild (
MotherID
)
Create Index idxChildDOB
On tblChild (
DOB DESC
)
Create Unique Index idxMarriage
On tblMan (
WifeID
)
Where WifeID Is Not Null |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 10/05/2012 : 01:35:19
|
Create Table tblWoman(
FirstName Varchar(20) Not Null
Check (Len(FirstName)>=2),
LastName Varchar(30) Not Null
Check (Len(LastName)>=2),
MaidenName Varchar(30) Null
Check (MaidenName Is Null or Len(MaidenName)>=2),
WomanID Integer Primary Key Nonclustered
Check(WomanID >= 1001),
DOB DATE Not Null
Check(DOB <= GetDate())
)
Create Table tblMan(
FirstName Varchar(20) Not Null
Check (Len(FirstName)>=2),
LastName Varchar(30) Not Null
Check (Len(LastName)>=2),
ManID Integer Primary Key Nonclustered
Check(ManID >= 1001),
DOB DATE Not Null
Check(DOB <= GetDate()),
WifeId Integer Null
References tblWoman(WomanID),
DOM DATE Null
Check(DOM Is Null or DOM <= GetDate()),
Check ((WifeId Is Null And DOM Is Null) Or (WideId WifeId Is Not Null And DOM Is Not Null))
)
Create Table tblChild(
FirstName Varchar(20) Not Null
Check (Len(FirstName)>=2),
LastName Varchar(30) Not Null
Check (Len(LastName)>=2),
ChildID Integer Primary Key Nonclustered
Check(ChildID >= 1001),
Gender Char(1) Not Null
Check (Gender = 'F' or Gender = 'M'),
DOB DATE Not Null
Check(DOB <= GetDate()),
FatherId Integer Null
References tblMan(ManID),
MotherID Integer Null
References tblWoman(WomanID),
Check ((FatherId Is Null and MotherId Is Not Null) or (FatherId Is Not Null and MotherId Is Null) Or (FatherId Is Not Null and MotherId Is Not Null))
)
--remove that comma
Create Table tblSiblingPair(
ChildID1 Integer Not Null,
References tblChild(ChildID),
ChildID2 Integer Not Null
References tblChild(ChildID),
Primary Key Clustered (ChildID1,ChildID2)
)
Create Clustered Index idxManName
On tblMan (
LastName,
FirstName
)
Create Clustered Index idxWomanName
On tblWoman (
LastName,
FirstName
)
Create Clustered Index idxChildName
On tblChild (
LastName,
FirstName
)
Create Index idxFather
On tblChild (
FatherID
)
Create Index idxMother
On tblChild (
MotherID
)
Create Index idxChildDOB
On tblChild (
DOB DESC
)
Create Unique Index idxMarriage
On tblMan (
WifeID
)
Where WifeID Is Not Null
[/quote]
--
Chandu |
 |
|
| |
Topic |
|
|
|
How do I solve Incorrect syntax table constraint??
