SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How do I solve Incorrect syntax table constraint??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

danigardner92
Starting Member

1 Posts

Posted - 10/04/2012 :  22:59:29  Show Profile  Reply with Quote
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
2202 Posts

Posted - 10/05/2012 :  01:35:19  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000