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
 Constraints
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Drucey
Starting Member

United Kingdom
5 Posts

Posted - 02/17/2011 :  08:00:43  Show Profile  Reply with Quote
Hi everyone, new to SQL and working on an assignment,

One little question - doesn't interfere with my studies:

I am creating tables one attribute is gender, in the CONSTRAINTS i would like a way to make sure it is either m or f without the use of another table just sql. What is the method of doing this (I also need a way to make a field either AM or PM but it would be done the same way)

Thanks for your time

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 02/17/2011 :  08:12:31  Show Profile  Reply with Quote
use CHECK Constraints


KH
Time is always against us

Go to Top of Page

Drucey
Starting Member

United Kingdom
5 Posts

Posted - 02/17/2011 :  09:18:33  Show Profile  Reply with Quote
Thanx for the reply, i will definitely look into that.

Sorry to keep being a pain but i have the script to create said tables and after hours i really seem stuck. I will post one example which frustrates me:

create table Booking
( FOREIGN KEY (Groupid) REFERENCES Teams(Groupid),
FOREIGN KEY (chalet_no) REFERENCES Accomodation(Chalet_no),
CONSTRAINT bookingid_pk PRIMARY KEY (Groupid, Chalet_no));

generates the error:

*
ERROR at line 2:
ORA-00904: : invalid identifier
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17584 Posts

Posted - 02/17/2011 :  09:33:13  Show Profile  Reply with Quote
quote:
ERROR at line 2:
ORA-00904: : invalid identifier


Looks like you are not using SQL Server. Maybe you should then we can help you with your script

For questions on Oracle, try dbforums.com. SQLTeam is on Microsoft SQL Server


KH
Time is always against us

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 02/17/2011 :  10:16:53  Show Profile  Reply with Quote
quote:
Originally posted by Drucey


create table Booking
( FOREIGN KEY (Groupid) REFERENCES Teams(Groupid),
FOREIGN KEY (chalet_no) REFERENCES Accomodation(Chalet_no),
CONSTRAINT bookingid_pk PRIMARY KEY (Groupid, Chalet_no));



Perhaps you are not even using SQL I never saw a create table statement in that fashion Dude google the "Oracle Create Table" you will get a good help

Edited by - MIK_2008 on 02/17/2011 10:17:58
Go to Top of Page

Drucey
Starting Member

United Kingdom
5 Posts

Posted - 02/17/2011 :  11:42:43  Show Profile  Reply with Quote
Ah sorry, so new to this. I definitely am using oracle though, it creates tables fine just flags up a few errors - Thats actually how we were taught to create tables. Sorry to waste your time though.

Thanks again

Go to Top of Page

Drucey
Starting Member

United Kingdom
5 Posts

Posted - 02/17/2011 :  11:45:05  Show Profile  Reply with Quote
And that one is a bit different, its not a normal table but a result of a many to many relationship being split, hence the foreign keys etc
Go to Top of Page

chris_n_osborne
Starting Member

USA
34 Posts

Posted - 02/17/2011 :  21:50:56  Show Profile  Reply with Quote
quote:
Originally posted by Drucey


create table Booking
( FOREIGN KEY (Groupid) REFERENCES Teams(Groupid),
FOREIGN KEY (chalet_no) REFERENCES Accomodation(Chalet_no),
CONSTRAINT bookingid_pk PRIMARY KEY (Groupid, Chalet_no));

I do not think this will work. You must define at least one column in a CREATE TABLE statement.

To add CONSTRAINTS to an existing table, you use the ALTER TABLE statement:

CREATE TABLE Teams
(Groupid  INTEGER NOT NULL
)
go

CREATE TABLE Accommodation2 -- I had an Accommodation table in my db already.
(Chalet_no   INTEGER NOT NULL
)
go

CREATE TABLE Booking
(Groupid    INTEGER NOT NULL
,Chalet_no  INTEGER NOT NULL
)
go

ALTER TABLE Teams
ADD CONSTRAINT Teams_pk PRIMARY KEY (Groupid)
ALTER TABLE Accommodation2
ADD CONSTRAINT Accommodation2_fk PRIMARY KEY (Chalet_no)
ALTER TABLE Booking 
ADD CONSTRAINT bookingid_pk PRIMARY KEY (Groupid, Chalet_no)
ALTER TABLE Booking 
ADD CONSTRAINT Groupid_fk FOREIGN KEY (Groupid) REFERENCES Teams (Groupid)
ALTER TABLE Booking 
ADD CONSTRAINT Chalet_no_fk FOREIGN KEY (Chalet_no) REFERENCES Accommodation2 (Chalet_no)
go


-- Example of declarative referential integrity
-- directly in each CREATE TABLE statement:

DROP TABLE Booking
DROP TABLE Teams
DROP TABLE Accommodation2

CREATE TABLE Teams
(GroupID  INTEGER NOT NULL
,CONSTRAINT pk_Teams PRIMARY KEY (GroupID)
)
go

CREATE TABLE Accommodation2
(ChaletNo    INTEGER
,CONSTRAINT pk_Accommodation2 PRIMARY KEY (ChaletNo)
)
go

CREATE TABLE Booking
(GroupID    INTEGER NOT NULL
,ChaletNo   INTEGER NOT NULL
,CONSTRAINT pk_Booking PRIMARY KEY (GroupID, ChaletNo)
,CONSTRAINT fk_Booking_Teams_GroupID
            FOREIGN KEY (GroupID)
            REFERENCES Teams (GroupID)
,CONSTRAINT fk_Booking_Accommodation2_ChaletNo
            FOREIGN KEY (ChaletNo)
            REFERENCES Accommodation2 (ChaletNo)
)
go
Go to Top of Page

yuanyelss
Starting Member

China
4 Posts

Posted - 02/19/2011 :  00:03:37  Show Profile  Visit yuanyelss's Homepage  Reply with Quote
unspammed

Edited by - yuanyelss on 02/19/2011 00:10:58
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 02/19/2011 :  17:35:30  Show Profile  Visit jcelko's Homepage  Reply with Quote
quote:
Originally posted by Drucey

Hi everyone, new to SQL and working on an assignment, I am creating a table with an attribute of gender ..



You do know that we will hunt down your professors and tell them you are presenting the work of others as your own for a class? Most colleges will expel you, as they should, you little cheat.

You don't do research, which means you are a bad student. There is an ISO Standard for sex_code (0 = unknown, 1 = male, 2 = female, 9 = lawful person (corporations organization, etc). You don't know that columns are not fields. You don't know the ISO-8601 temporal standards.







--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

Drucey
Starting Member

United Kingdom
5 Posts

Posted - 02/24/2011 :  08:56:13  Show Profile  Reply with Quote
I'm not actually copying and presenting any work as my own, simply asking people in the know, which is no different to asking a friend in the profession ect. I ask, learn how its done and then i do it myself which is perfectly legal. As for research forums and such are an encouraged form. As long as i get information, LEARN it and apply it myself i am not sure what is wrong here? So feel free to tell my lecturers and explain how i asked a couple things. Thanks to everyone for the input
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.09 seconds. Powered By: Snitz Forums 2000