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
 Constraints

Author  Topic 

Drucey
Starting Member

5 Posts

Posted - 2011-02-17 : 08:00:43
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)

17689 Posts

Posted - 2011-02-17 : 08:12:31
use CHECK Constraints


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

Drucey
Starting Member

5 Posts

Posted - 2011-02-17 : 09:18:33
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)

17689 Posts

Posted - 2011-02-17 : 09:33:13
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-17 : 10:16:53
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
Go to Top of Page

Drucey
Starting Member

5 Posts

Posted - 2011-02-17 : 11:42:43
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

5 Posts

Posted - 2011-02-17 : 11:45:05
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

34 Posts

Posted - 2011-02-17 : 21:50:56
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

4 Posts

Posted - 2011-02-19 : 00:03:37
unspammed
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-02-19 : 17:35:30
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

5 Posts

Posted - 2011-02-24 : 08:56:13
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
   

- Advertisement -