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] |
|
|
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 |
|
|
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] |
|
|
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 |
|
|
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 |
|
|
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 |
|
|
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)goCREATE TABLE Accommodation2 -- I had an Accommodation table in my db already.(Chalet_no INTEGER NOT NULL)goCREATE TABLE Booking(Groupid INTEGER NOT NULL,Chalet_no INTEGER NOT NULL)goALTER TABLE TeamsADD CONSTRAINT Teams_pk PRIMARY KEY (Groupid)ALTER TABLE Accommodation2ADD 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 BookingDROP TABLE TeamsDROP TABLE Accommodation2CREATE TABLE Teams(GroupID INTEGER NOT NULL,CONSTRAINT pk_Teams PRIMARY KEY (GroupID))goCREATE TABLE Accommodation2(ChaletNo INTEGER,CONSTRAINT pk_Accommodation2 PRIMARY KEY (ChaletNo))goCREATE 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 |
|
|
yuanyelss
Starting Member
4 Posts |
Posted - 2011-02-19 : 00:03:37
|
unspammed |
|
|
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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
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 |
|
|
|