How do I solve Incorrect syntax table constraint?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How do I solve Incorrect syntax table constraint??

Author

Topic

danigardner92
Starting Member

1 Post

Posted - 2012-10-04 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-05 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources